Date calculations

[JannetteW]

Hi there,

I am trying to write a sql statement that will retrieve all records that have a date greater than today or is blank, can you help with this please. So far I have date field > date().

Thanks

[beat1078]

First you need to make sure that your date field matches up with the function.
Date() returns mm/dd/yyyy

Then you want to try dteField >date() or isnull(dteField )
That will produce all records that are greater than today and that are blank.

If your date dosn't match up to the format you need to be creative with some code to manipulate the date to match up like:

SometxtField=ri ght$(date$,4)+l eft$(date$,2)+m id$(date$,4,2)
That will produce a date of yyyymmdd without the slashes.

--Beat

[PEB]

Hi,

U can try also

Field> Now() or is null

:)

[JannetteW]

Thanks very much , that has worked.

First you need to make sure that your date field matches up with the function.
Date() returns mm/dd/yyyy

Then you want to try dteField >date() or isnull(dteField )
That will produce all records that are greater than today and that are blank.

If your date dosn't match up to the format you need to be creative with some code to manipulate the date to match up like:

SometxtField=ri ght$(date$,4)+l eft$(date$,2)+m id$(date$,4,2)
That will produce a date of yyyymmdd without the slashes.

--Beat