“Like” expression against a text field that contains Roman Numerals

[Ezeasy]

Could someone help me on using the “Like” expression against a text field that contains Roman Numerals (I, II, III, & IV)? When I use the “Like” expression for example (I) it selects all records and that’s because it does match the other numbers. But I would like to run this query if this field is blank select all or select the records based on the passed parameter to the criteria in the query. I’m also checking a date field for select date range to pull records from.
Any help would be appreciated. Thanks

 

[VicM]

Ez

Try using Like "spaceIIspace", etc. Unless of course the numerals are concatenated with another string of text. But I would expect that would not be the case.

HTH,

Vic

 

[SgtJarrow]

If you are looking for exactly I, or exactly II, and so on....you don't need the like statement....just use:

"I"

or

"IX"

or

"XXVI"

to find the appropriate number. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[Ezeasy]

VicM - I tried it like your example before and had no-luck.

Mstrmage1768 – I know to use the exact match, but if I don’t pass a parm I would like to see all the records in that range.

 

[SgtJarrow]

So you have (at least) two criteria....a date range and a roman numeral. If you supply both, you want the query to return all values that meet those two criteria. But if only supply the date range, you want ALL records in that range. Sound about right???

Check the FAQs here on Tek-Tips. I can remember seeing at least one FAQ that explains how to return all records if no criteria is given in a paramater...should fix you up. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[VicM]

EZ

Where in the string do the roman numerals appear? At the beginning, middle, end, or anywhere?

Vic

 

[Ezeasy]

Vic,
The string appears at the beginning.

Thanks, Ez

 

[VicM]

Ez

If it's true in all cases that the Roman numeral string always starts the field, then remove the first space in the line of code I suggested above. As long as there will never be a space in front of the Roman numeral, that code should work. (Conversely, there must always be at least ONE space after the numeral.)

The problem with using just the Roman string without the Like is that you will in all probability get nothing returned. That method of coding is asking for records containing only that string.

HTH,

Vic

P.S. If you are unsure whether the user will put a space or spaces in front of the Roman numeral, you can run the data through a validation and strip out the spaces using the Trim function.