Query critera using DLookUp and "Like" (1 Viewer)

chuckcoleman

Registered User.
Local time
Today, 17:06
Joined
Aug 20, 2010
Messages
357
I've run into a snag. I have a query that is based on one table, [BillingTable]. That table has a field, [ReferenceNoOriginal]. The data in that field might look like, TAX482-69511449. I have another table called, [TireCodeTable]. It has six rows with a field name of [TireCode]. The [TireCode] field has values like, TAX, PLT, STT, TAZ. There is no relationship between the two tables. I want to essentially use the LIKE function with DLookUp to produce records that start with the TireCode's in the [TireCodeTable]. I've done a lot of research but I can't find the right coding.

I will appreciate any thoughts.

Thanks,

Chuck
 

plog

Banishment Pending
Local time
Today, 17:06
Joined
May 11, 2011
Messages
11,612
Idealy you store discrete pieces of data discretely. That is, every datum that has its own meaning goes in its own field. Since the TAX part of TAX482-69511449 has its own meaning, it should go in its own field. Most likely the 482 portion would go in its own field as well as a 3rd field for the last portion.

So, can you do that? Then you simply JOIN your table with the other table on that field and the query produces the results for you.

If that's not possible, you virtually do that. You create a query on BillingTable, bring down all the fields of the table, then create another calculated field using the Mid function (https://www.techonthenet.com/access/functions/string/mid.php) to extract out the portion in the other table. Save that query. Then build another query on it and the TireCodeTable JOINing them as described above.
 

chuckcoleman

Registered User.
Local time
Today, 17:06
Joined
Aug 20, 2010
Messages
357
Plog,

Thank you. I used your second suggestion and it worked perfectly.

I appreciate your reply!

Take care,

Chuck
 

chuckcoleman

Registered User.
Local time
Today, 17:06
Joined
Aug 20, 2010
Messages
357
Plog,

One additional thought. The risk with your second solution, (I used the Left function three characters), is that if the format of the data ever changed and the codes in my Tire Code Table weren't at the beginning/left of the string, I wouldn't be able to detect them. The LIKE function provides for that by using wildcard's, i.e. Like "*SAT*". In order to do that I guess I would have to use a combination of InStr and Mid? Do you agree with that?

Chuck
 

plog

Banishment Pending
Local time
Today, 17:06
Joined
May 11, 2011
Messages
11,612
No, if you were to use LIKE, there would be no need to manipulate any strings. Also, the SQL gets a lot more trickier. I'm sure someone knows the trick, but you can't just put asterisks around a field in your query and hope to match it to another field:

WHERE [Table1Field] LIKE "*" & [Table2Field] & "*"

The above is not going to work. The best solution is solution #1--store your data properly--put the SAT portion in its own field in a table.
 

Users who are viewing this thread

Top Bottom