Text Field Lookup

[Parra]

I have a large table, 4 columns are text fields. Next to each column is a number field with a net amount. What I want to do is create a query that looks at each text field and if the word "*total*" is found, return the value in the number field next to it. (There can be several words in the field)

Description 1
Total 1
Description 2
Total 2
Description 3
Total 3
Description 4
Total 4

So I need a calculated field that will look up the word "*total*" in each field named Description.

Any suggestions????

Thanks

 

[kkknie]

Not knowing exactly what you are up to, I'll give what help I can:

The function InStr finds the first occurance of one string in another. For example:

If your TextField has the value: Product Total
Any your function was: InStr(TextField,"Total")
The return of the query would be 9 (9th character starts)

Better yet, you should handle cases by using this:
Instr(LCase(TextField),"total")

In either case, if you set your criteria to be > 0 for the return of the
InStr function, you should get only those records with the word total
in them.

If you want to clarify more, I may be able to help more...

K

 

[Dugantrain]

Using:

Like *total*

in the criteria field of each description will give you the necessary results. The two asterisks on either side of total will capture any field that has total anywhere in that field. Like Total* would capture all fields that have Total at the start of the field and Like *Total would capture all that have total at the very end.

You didn't specify whether or not you want it to retrieve every record that has Total in any one of the fields regardless of whether or not the other three text fields in that record have Total in them. If this is the case, then write 'like Total*' on a separate "Or" line of all four fields. I hope this is clear, if not, respond back.

 

[Parra]

OK I just checked back and saw that I had some responses, thanks. I am going to try them, but maybe this will give you an idea of what I'm trying to do:
IIf([description 1]="TOTAL:",[total 1],IIf([description 2]="TOTAL:",[total 2],IIf([description 3]="TOTAL:",[total 3],IIf([description 4]="TOTAL:",[total 4],0))))

This calculated field gave me what I wanted, but it assumes that the data in the field is "Total:", but I also want to extract it if there is more text, something like "last years total:"

But it does not seem to allow me to use wild cards *. I'm going to try your suggetions.

 

[Dugantrain]

It's behaving like you didn't put the asterisks in. Is that not the case?

 

[Parra]

Hi Dugantrain, It will be to long to explain but I did try what you recommeded, at first and it returned all the records in the database. It worked fine, but not for what I am trying to do.

Unfortuantly I eliminated that as an option. Any other suggestions?

Thanks

 

[Dugantrain]

Since your SQL syntax seems to work (almost, anyway) why don't you try to put the wildcard operator into the SQL string:
IIF([description1] like "*total*"), [total 1] etc.

 

[kkknie]

I think you can get your formula to work by replacing

[description 1]="TOTAL:"

with

InStr(UCase([description 1]),"TOTAL:")<>0

This just checks for any occurence of the string "TOTAL:"

K

 

[Parra]

Thanks KKKnie, I can use this in an IIF function.

Parra

 

[Parra]

I'm getting there:
IIf(InStr(UCase([description 1]),"TOT")<>0,[total 1],0)
Thanks Kkknie.