Text field containing date to query within date range

[Guest]

I am running a query on a database I cannot modify.
A Text Field contains a string with a date format of "mm/dd/yy"
I need to display all records within a date range. The minimum and maximum
dates of the date range are also strings.
I've tried BETWEEN with no success.

Any and all suggestions are very much appreciated.

 

[Marshall Barton]

I am running a query on a database I cannot modify.
A Text Field contains a string with a date format of "mm/dd/yy"
I need to display all records within a date range. The minimum and maximum
dates of the date range are also strings.
I've tried BETWEEN with no success.

Convert the strings to date values:

CDate(datefield) Between [start date AND [end date]

 

[Guest]

Thanks for replying Marshall,

I tried what you recommended and I get the following error message ...
"Invalid use of Null"

I am running a query on a database I cannot modify.
A Text Field contains a string with a date format of "mm/dd/yy"
I need to display all records within a date range. The minimum and maximum
dates of the date range are also strings.
I've tried BETWEEN with no success.

Convert the strings to date values:

CDate(datefield) Between [start date AND [end date]

 

[Marshall Barton]

So, some records have a Null value in the "date" field?

Try this:

CDate(Nz(datefield,"1/1/3333") Between [start date] AND [end
date]

 

[Guest]

Thanks again Marshall,

I forgot to mention that I am accessing this DB from an ASP .Net webpage.
It did not allow me to use the Nz( ), so I found the following to work:

"SELECT * FROM Project WHERE
IIf(IsNull(Project.Date),0,CDate([Project.Date])) BETWEEN #" +
ddlBeginDate.SelectedItem.Text + "# AND #" + ddlEndDate.SelectedItem.Text +
"#"

Again,
Thanks for pointing me in the correct direction.

So, some records have a Null value in the "date" field?

Try this:

CDate(Nz(datefield,"1/1/3333") Between [start date] AND [end
date]

I tried what you recommended and I get the following error message ...
"Invalid use of Null"