Text field containing date to query within date range

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Kenz21 said:
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.
Click to expand...



Convert the strings to date values:

CDate(datefield) Between [start date AND [end date]
 
Thanks for replying Marshall,

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

Marshall Barton said:
Kenz21 said:
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.
Click to expand...



Convert the strings to date values:

CDate(datefield) Between [start date AND [end date]
Click to expand...
 
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]
 
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.

Marshall Barton said:
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"
Click to expand...
Click to expand...
 
Back
Top