Help With Like Condition Using Wildcards

[mr.donaldson]

Thanks to anyone taking the time to read this post. I am fairly new to Access and SQL so I am doing my best to work through roadblocks on my own before seeking help. However, I am at a stand still.

I have a table called Coded_Data with one particular field called Codes. This field list a number and a respective code to identify what type of code it is. For instance 123456789-AD...123456789-ADS. I am wanting to create to two columns. One listing all work orders coded with AD and the other withADS. When I use the Like condition with % it returns both AD and ADS in same column. I understand why because the "%AD" returns anything that starts with AD so this is why I get both codes. I only want to get the the AD codes in one column and ADS in the other. I have duplicated the table twice since I am trying to retrieve data from the same field and table but display in 2 separate columns. The duplicated table has a different alias. The problem is in the where clause. I just don't know how to solve this. I tried using = but that doesn't work either. My query is below. Sorry for being lengthy. Thanks for any help and suggestions.


where (((coded_data.codes) LIKE "%AD")) or ((coded_data_1.codes) LIKE "%ADS");

 

[Simon_MT]

You could try something along the lines of

CodesLen: Len([coded_data.codes])
CodesPos: Instr([coded_data.codes],"-")
CodesTest: CodesLen-CodesPos

And then do whatever having established the length of the suffix.

Simon

 

[mr.donaldson]

I will try what you suggested. Im very new to all this so I am trying to figure out how to put that in my query. Thank you for the help.

 

[Brianwarnock]

Like "%AD" will select based on data ending AD not ADS and Like "%ADS" will select data ending ADS not AD , but you cannot do this in the Where clause for what you want as that applies to records not columns.
Try creating new columns

ADcodes: iif(code Like"%AD",codes,"")
ADScodes: iif(code Like"%ADS",codes,"")

No criteria

Brian