like statement for date search

O

one1george

Hello,

I would like to use the like statement to look up a date.
i know that like '*' is used for a text search.
How would i use it for a date/time search..example
like #*# ?

I have tryed a number of different variations but with no
luck

thanks
 
L

LeAnne

Hi,

Not sure what you're trying to accomplish here. To use LIKE plus the
wildcard, you could use pattern matching on your date field:

SELECT *
FROM MyTable
WHERE [DateField] LIKE "3/*/2001";

Or to find a range:

SELECT *
FROM MyTable
WHERE [DateField] LIKE "[1-3]/*/2001";

Another way to specify a range of dates is to use the BETWEEN...OR
operator:

SELECT *
FROM MyTable
WHERE [DateField] BETWEEN #1/1/2001# AND #3/31/2001#;

hth,

LeAnne
 
G

Guest

Here is an example of the code i am trying to work with
strEvTraining = " Like '*' "
this works for text but not date/time

i have tryed " like #*# " but this doesn't work
any ideas.

thanks
robin
 
J

John Vinson

Hello,

I would like to use the like statement to look up a date.
i know that like '*' is used for a text search.
How would i use it for a date/time search..example
like #*# ?

I have tryed a number of different variations but with no
luck

thanks
Click to expand...

A date/time value is NOT a text string; it's stored as a Double Float
number, a count of days since midnight, December 30, 1899. It will be
formatted based on your computer's regional date format settings
(which may vary from computer to computer or from time to time,
depending on who's changed that setting!).

What are you trying to ACCOMPLISH in the real-world situation?
Searching for dates containing "3" would find all dates in 1993 and
2003, all dates in March in any year, and all dates where the day is
the 3rd, 13th, 23rd, 30th or 31st - hardly a useful set of data!

If you want dates in a particular month, you can put a calculated
field

Month([datefield])

in your query, and use a criterion of 1 to 12; if you want a
particular date range, you can use a criterion such as

BETWEEN [Enter start date:] AND [Enter end date:]

But using wildcards in a date field is not going to help you much!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
D

Duane Hookom

AMEN! I can't believe the number of posts that use string functions against
date fields.

--
Duane Hookom
MS Access MVP


John Vinson said:
Hello,

I would like to use the like statement to look up a date.
i know that like '*' is used for a text search.
How would i use it for a date/time search..example
like #*# ?

I have tryed a number of different variations but with no
luck

thanks
Click to expand...

A date/time value is NOT a text string; it's stored as a Double Float
number, a count of days since midnight, December 30, 1899. It will be
formatted based on your computer's regional date format settings
(which may vary from computer to computer or from time to time,
depending on who's changed that setting!).

What are you trying to ACCOMPLISH in the real-world situation?
Searching for dates containing "3" would find all dates in 1993 and
2003, all dates in March in any year, and all dates where the day is
the 3rd, 13th, 23rd, 30th or 31st - hardly a useful set of data!

If you want dates in a particular month, you can put a calculated
field

Month([datefield])

in your query, and use a criterion of 1 to 12; if you want a
particular date range, you can use a criterion such as

BETWEEN [Enter start date:] AND [Enter end date:]

But using wildcards in a date field is not going to help you much!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
D

Duane Hookom

This depends on how you define "programmers". I have seen some regular
contributors to these news groups that I find generally quite competent yet
still use string functions on date values.
 
Top