Searching before and after specific date

[alstont]

I'm trying to write a MSAccess form statement that will evaluate a SQL field in a table to tell me if the field is prior to a certain date. The SQL field is a nvarchar field but is used as a date field with the structure of mm/dd/yy. I'm using an unbound textbox to make my evaluation with the following:

=IIf(FormatDateTime([closedate],0)<=#12/31/1999#,"SERIES 200","SERIES 100")

The statement is not being evaluated because everything is 'SERIES 100' regardless of the closedate

So - I thought the limitation was with the form and I placed a similar sting in a query with the following:

Series:
IIf(Format([closedate],"m/d/yyyy")<#12/31/1999#,"SERIES 200",
IIf(Format([closedate],"m/d/yyyy")>#1/1/2000#,"SERIES 100"))

and had the same results. I tried a bit more complicated string

IIf((Format([closedate],"m/d/yyyy") Between Format("1/1/1959","m/d/yyyy") And Format("12/31/1999","m/d/yyyy")), "SERIES 200", "SERIES 100")

with no better results. Am I correct in thinking you are able to filter between dates and return results or is the conversion from a text field to a date field throwing my code off? Any suggestions would be appreciated. Thanks!!

 

[TheAceMan1]

How are ya alstont . . . . .

Try this"

[blue]=IIf([purple][b]CDate([closedate])[/b][/purple]<=#12/31/1999#,"SERIES 200","SERIES 100")[/blue]

See Ya! . . . . . .