Date query

[dhoover via AccessMonster.com]

I need to run a query that picks up a particular date no matter how it's
formatted. For example if a date is 02/02/2010 or 2/2/2010 or 02/2/2010, ect,
I need to be able to pick them all up.

 

[raskew via AccessMonster.com]

Hi -

Given your example, I'm assuming your field is text, not date/time. If
that's the case, lookup the DateValue() function.

HTH - Bob

 

[KARL DEWEY]

It sounds like you have a text field and not a DateTime datatype. You need
to change your field.

But if the format is recognizable to Access then use the CVDate function --
CVDate([YourField])
and then apply your date criteria.

 

[dhoover via AccessMonster.com]

It is a text field that sometimes contains a date and others times contains
text. I've tried CVDate and DateValue, neither are pulling all instances of
a date. For example if I type in 01/5/2010 it will not caputure a date
entered as 1/5/10

It sounds like you have a text field and not a DateTime datatype. You need
to change your field.

But if the format is recognizable to Access then use the CVDate function --
CVDate([YourField])
and then apply your date criteria.

I need to run a query that picks up a particular date no matter how it's
formatted. For example if a date is 02/02/2010 or 2/2/2010 or 02/2/2010, ect,
I need to be able to pick them all up.

 

[dhoover via AccessMonster.com]

It is a text field that sometimes contains a date and others times contains
text. I've tried CVDate and DateValue, neither are pulling all instances of
a date. For example if I type in 01/5/2010 it will not caputure a date
entered as 1/5/10

It sounds like you have a text field and not a DateTime datatype. You need
to change your field.

But if the format is recognizable to Access then use the CVDate function --
CVDate([YourField])
and then apply your date criteria.

I need to run a query that picks up a particular date no matter how it's
formatted. For example if a date is 02/02/2010 or 2/2/2010 or 02/2/2010, ect,
I need to be able to pick them all up.

 

[John Spencer]

With that structure you could use a calculated field that looks like the
following and search against it.

IIF(IsDate([YourField]),Format(CDate([YourField]),"mm/dd/yyyy"),[YourField])

That will give you a string that is consistently formatted for your dates and
when the field is NOT a date you will get the value returned. Although in
some cases you might get unexpected results. As this will transform partial
date strings. August 20 will be converted to the string 08/20/2010.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

It is a text field that sometimes contains a date and others times contains
text. I've tried CVDate and DateValue, neither are pulling all instances of
a date. For example if I type in 01/5/2010 it will not caputure a date
entered as 1/5/10

It sounds like you have a text field and not a DateTime datatype. You need
to change your field.

But if the format is recognizable to Access then use the CVDate function --
CVDate([YourField])
and then apply your date criteria.

I need to run a query that picks up a particular date no matter how it's
formatted. For example if a date is 02/02/2010 or 2/2/2010 or 02/2/2010, ect,
I need to be able to pick them all up.

 

[dhoover via AccessMonster.com]

I ended up running a 2nd query to format the dates properly and then ran my
original query from that using datevalue(), it was a round about way or doing
it but it worked. thanks for everyone's help!

With that structure you could use a calculated field that looks like the
following and search against it.

IIF(IsDate([YourField]),Format(CDate([YourField]),"mm/dd/yyyy"),[YourField])

That will give you a string that is consistently formatted for your dates and
when the field is NOT a date you will get the value returned. Although in
some cases you might get unexpected results. As this will transform partial
date strings. August 20 will be converted to the string 08/20/2010.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

It is a text field that sometimes contains a date and others times contains
text. I've tried CVDate and DateValue, neither are pulling all instances of

[quoted text clipped - 11 lines]