Serial number range lookup

[Cdaworld]

Here is what i am trying to do. I have serveral serial number ranges and if you gun fall with a range it will give the date (Month or year) it was made ie.

Range Date Made
1- 200 Dec 1959
201 - 300 Jan 1960
301 - 500 Feb 1960
1001 - 2000 1961

The user just need to input SN and it will return a report of the year

user input 150
results in Made in Dec 1959

Thanks for your help..

Carl

 

[vbaInet]

This would have been best suited in a function, but is the Range always going to be in this format? That is, number(s) space hyphen space number(s)?

If it is then here:

DLookup("[[COLOR=Red]Date Made[/COLOR]]", "[COLOR=Red]TableName[/COLOR]", Val([[COLOR=Red]txtBoxInput[/COLOR]]) & " BETWEEN Val([[COLOR=Red]Range[/COLOR]]) AND Val(Mid([[COLOR=Red]Range[/COLOR]], Instrrev([[COLOR=Red]Range[/COLOR]], ""-"") + 2))")

Enter the appropriate field names for the bits in red. And txtBoxInput is the name of the textbox where the user enters the SN.