If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date query
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#2
|
|||
|
|||
Date query
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 dhoover wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
Date query
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. -- Build a little, test a little. "dhoover via AccessMonster.com" wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 . |
#4
|
|||
|
|||
Date query
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 KARL DEWEY wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#5
|
|||
|
|||
Date query
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 KARL DEWEY wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#6
|
|||
|
|||
Date query
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 dhoover via AccessMonster.com wrote: 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 KARL DEWEY wrote: 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. |
#7
|
|||
|
|||
Date query
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! John Spencer wrote: 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] 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
Thread Tools | |
Display Modes | |
|
|