unable to select date

[cancer2006]

Using Access 97. Need to select data based on the following condition.

Select * From tempTable
Where (tempTable.date >= '01/01/2007') or (tempTable.date is null)

data type of tempTable.date is Text.

When I use the following code
Select * From tempTable
Where tempTable.date >= '01/01/2007'

It gives me all the records even if the date is < 01/01/2007 with out NULL records.

I will appreciate your efforts. Thanks.

 

[Hans8823]

If the date type of tempTable.date is text this is the result I would expect. Every date, except for jan 1th in years before 2007 are smaller then '01/01/2007'. If you would stick to date type text then you should store the dates in yyyy/mm/dd format, the better option is change the date type to date and then select statement should be:

Select * From tempTable Where (tempTable.date >= #01/01/2007#)

 

[dhookom]

Hans8823 is correct. Another alternative is to convert the text to a date value with:

Select * From tempTable
Where DateValue(tempTable.date) >= '01/01/2007'

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I'd use the following:
Select * From tempTable
Where CVDate(tempTable.date) >= #01/01/2007# or (tempTable.date is null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886