String Search in query 1

[swtrader]

I am trying to use the following in a query:

StringSearch: IIf(InStr(1,[DESCRIPTION],"Payroll"),1)

I obviously don't know how to use the InStr function.

I can find a "P" and I can find complete words, such as "POS" -- if the word is at the beginning of the string....but I can't find whole words.

Thanks.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[AlexCuse]

I don't understand what you are trying to do here? Why not just use

where Description like '*Payroll*'

Anyway what you want is this:

iif(instr([Description], 'Payroll') > 0, 1, 0)

Your problem was in how you were using iif as well.

IIF syntax: iif(expression, value if true, value if false)

Instr syntax: Instr(expression, searchString)

Instr returns the starting position of your searchString, and if it is not found returns 0. So your boolean expression for the iif should be

instr([Description], 'Payroll) [b]> 0[/b]

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[swtrader]

Hmm....I've never used "Like" in a query. That works. Thanks.

Maybe if I explain a bit what I am trying to do, you can point me in the right direction.

Delimited text file comes into my dept. It has a bunch of info -- Date, Description, Amount, etc. This text file is appended to an Access table with those field names and a few others, including "Type".

The Description can be lengthy and jumbled but if it includes, say, "Payroll", then it's a payroll (Type). (It might read: "Dept 323, month 34, payroll was paid for all full and part time employees" OR "Day 12, payroll is outstanding" OR anything else.

If it includes, say, "Legal Expense", then it's a legal expense (Type).

What I was hoping to do is to build a table of search items (Payroll, Legal Expense in one column/field to use to search the string and another column/field to denote the actual type of the item -- Payroll, Legal Expense). There could be dozens or as many as 50-100 of these "pairings".

Ultimately, I want to update the table to show the correct Type after having determined the correct type after having searched the text string.

I think I am making this far more difficult than it needs to be.

 

[AlexCuse]

I think I am making this far more difficult than it needs to be.

Unfortunately, I don't believe you are. Yours is not an easy task. You could probably set up the lookup table and join to it on the like operator, but this is not going to be a great performer, and it also leaves you open to allkinds of problems (what if three of these search words are in one entry?). I think your best bet is to perform these checks in your import process, and get the 'type' column to read exactly how you want it then.

What is your comfort level with VBA?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[swtrader]

Can only copy and modify VBA.

I'll look at trying to complete the task pre-Append.

thanks.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[swtrader]

Alex -- I've been putting around Access for several years. Your obvious (but, to me, unknown) query criteria *xxx* has been of tremendous help. I was able to construct exactly what I needed using a combination of this search, an Update query and an Append query. It's clunky and lacks pro-level finesse but it works very well. I couldn't have done it without your piece of info.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[AlexCuse]

Glad you got it to work! The like operator is a good one to know, and now it will be obvious to you

Ignorance of certain subjects is a great part of wisdom