Search for current date in a text field

[mrlewis52]

I have a SQL database that I have an external data link to an access database.
I need to search records based on the current date.

The problem is that the field that needs to be searched is in a text format
"yyyy-mm-dd". I have used the following statement successfully to pull out
current year:

Left([ArrestDate],4)=DatePart("yyyy",Date()).

This does pull off any records with the 2010 year. However, I need to sort by
the entire date.

How can I do this?

I have tried the following hoping it would put a string together but I get an
"Invalid Procedure Call" error.

 

[vanderghast]

*IF* your field is a date_time field, you can filter by a criteria like:

= DateSerial(2010, 1, 1,) AND < DateSerial(2011, 1, 1)

and if there is an index on that field, the index should be used.

Note that there is a difference between HOW IT IS STORED and HOW IT IS
DISPLAYED. It is not because you SEE a format yyyy-mm-dd that it is a
stored as a string (even if it MAY be a string too). That is how it is
important to know if the field is a date_time field or if it is a string.
Since you got an error using LEFT(fieldName, 4), I assume the field IS NOT
a string value, but a date_time value, which is ok.

You can use

= DateSerial(YEAR(NOW), 1, 1,) AND < DateSerial(1+YEAR(NOW), 1, 1)

if the year to consider is to be the actual one, and plan to use the
application for some years to come.


Vanderghast, Access MVP

I have a SQL database that I have an external data link to an access
database.
I need to search records based on the current date.

The problem is that the field that needs to be searched is in a text
format
"yyyy-mm-dd". I have used the following statement successfully to pull out
current year:

Left([ArrestDate],4)=DatePart("yyyy",Date()).

This does pull off any records with the 2010 year. However, I need to sort
by
the entire date.

How can I do this?

I have tried the following hoping it would put a string together but I get
an
"Invalid Procedure Call" error.

 

[Daryl S]

Mrlewis52 -

You can turn the string value into a date value using the DateValue
function, but you should pass in the string date in the same order as your
system date - usually month/day/year. In your query that pulls the records
you want, you can also convert the arrest date to a true date field, or if
you only need to sort the records you are returning, you can sort based on
the converted true date. This is the expression that will give you a true
date based on the yyyy-mm-dd format:

DateValue(Mid([ArrestDate],6,2) & "/" & Right([ArrestDate],2) & "/" &
Left([ArrestDate],4))

 

[Jerry Whittle]

The CDate function will convert a string like that into a date.

Debug.Print Cdate("2010-04-25")
4/25/2010

CDate([ArrestDate])

However CDate will fail if it's presented with an invalid date.
"2010-13-13" won't work. You might want to check the data field with the
IsDate function to see if there are any problem records.

 

[John W. Vinson]

I have a SQL database that I have an external data link to an access database.
I need to search records based on the current date.

The problem is that the field that needs to be searched is in a text format
"yyyy-mm-dd". I have used the following statement successfully to pull out
current year:

Left([ArrestDate],4)=DatePart("yyyy",Date()).

This does pull off any records with the 2010 year. However, I need to sort by
the entire date.

How can I do this?

I have tried the following hoping it would put a string together but I get an
"Invalid Procedure Call" error.

I'm in agreement with Vanderghast: is this *really* a Text type field, or a
Date/Time field just formatted and displayed as text?

If it is text, you should be able to use a criterion

CDate([ArrestDate]) = Date()

to recast the text string into a Date/Time and compare it with today's date.
For more flexiblity, and to take advantage of any indexes on ArrestDate, you
could use a small unbound form frmCrit with two textboxes txtStart and txtEnd
(these could even have =Date() as their default value to save typing if
today's data is the most common search); you'ld use a criterion like

[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")

 

[mrlewis52]

I think this might just work.

The CDate function will convert a string like that into a date.

Debug.Print Cdate("2010-04-25")
4/25/2010

CDate([ArrestDate])

However CDate will fail if it's presented with an invalid date.
"2010-13-13" won't work. You might want to check the data field with the
IsDate function to see if there are any problem records.

I have a SQL database that I have an external data link to an access database.
I need to search records based on the current date.

[quoted text clipped - 14 lines]

.

 

[mrlewis52]

When I added your suggestion for the criteria, I am getting an "Invalid use
of Null" error. I entered the line on the Criteria line for the [ArrestDate]
field.

CDate([ArrestDate]) = Date()

What am I missing? Does this error indicate there is an invalid date or
something in the data?

I have a SQL database that I have an external data link to an access database.
I need to search records based on the current date.

[quoted text clipped - 12 lines]

I have tried the following hoping it would put a string together but I get an
"Invalid Procedure Call" error.

I'm in agreement with Vanderghast: is this *really* a Text type field, or a
Date/Time field just formatted and displayed as text?

If it is text, you should be able to use a criterion

CDate([ArrestDate]) = Date()

to recast the text string into a Date/Time and compare it with today's date.
For more flexiblity, and to take advantage of any indexes on ArrestDate, you
could use a small unbound form frmCrit with two textboxes txtStart and txtEnd
(these could even have =Date() as their default value to save typing if
today's data is the most common search); you'ld use a criterion like

[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")

 

[mrlewis52]

There are records that have no dates in them which results in an #Error in
the CDate field.

When I added your suggestion for the criteria, I am getting an "Invalid use
of Null" error. I entered the line on the Criteria line for the [ArrestDate]
field.

CDate([ArrestDate]) = Date()

What am I missing? Does this error indicate there is an invalid date or
something in the data?
[quoted text clipped - 17 lines]

[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")

 

[mrlewis52]

The blank ArrestDate fields were the issue. Took those out and it works great!
Thanks for your help (and Vanderghast).

There are records that have no dates in them which results in an #Error in
the CDate field.

When I added your suggestion for the criteria, I am getting an "Invalid use
of Null" error. I entered the line on the Criteria line for the [ArrestDate]

[quoted text clipped - 10 lines]

[ArrestDate] BETWEEN Format([Forms]![frmCrit]![txtStart], "yyyy-mm-dd") AND
Format([Forms]![frmCrit]![txtEnd], "yyyy-mm-dd")

 

[mrlewis52]

I used Mr. Vinson and your suggestion with the CDate and it worked great.
Thanks very much.

The CDate function will convert a string like that into a date.

Debug.Print Cdate("2010-04-25")
4/25/2010

CDate([ArrestDate])

However CDate will fail if it's presented with an invalid date.
"2010-13-13" won't work. You might want to check the data field with the
IsDate function to see if there are any problem records.

I have a SQL database that I have an external data link to an access database.
I need to search records based on the current date.

[quoted text clipped - 14 lines]

.