Return exact match in text containing hyphen

  • Thread starter Thread starter shelter
  • Start date Start date
S

shelter

I have looked at all the threads addressing the Replace function, but
none seem to address my problem.

I am running Access 2003. I have a table with a text field which
contains numbers formatted as yy-12345 (I cannot change the field type
as this is part of a proprietary application). I created a query that
allows a user to select a range of these numbers, ie; 06-100 through
06-150. My problem is that this returns all records containing these
numbers, ie; 06-1001, 06-1500, etc. I think the hyphen may be seen by
Access as a wildcard somehow. How can I make the query return the
appropriate range (no wildcards)? Thank you for your time.
 
You could try a calculated column to break the data down into parts. As
long as you didn't need to cross years that would be fairly simple.
Crossing years will make life more difficult.

Field: TheYear: Left(YourField,2)
Criteria: Left(TheInput,2)

Field: TheNumbers: Val(Mid(YourField,4))
Criteria: Between(Val(Mid(TheInput,4)) and Val(Mid(SecondInput,4))
 
Thank you for your response. I am curious why I need TheYear field. I
have tried running the query without it and get an error message (This
expression is typed incorrectly, or is too complex to be evaluated...).
Once I put TheYear field back in, the query runs fine. Why can't I run
this query with TheNumbers field only? Thank you.
 
Back
Top