like statement for date search

  • Thread starter Thread starter one1george
  • Start date Start date
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
 
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
 
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
 
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
 
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...
 
People (as opposed to programmers!) tend to think of dates as "text
things" rather than "number things".

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


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
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.
 
Real programmers do it in Machine Language!

Seriously, I think that if you come to Access VBA with experience in
another programming language - even regular Basic - you are likely to
have a more secure grasp of the distinction between what a variable
(or Field) actually contains and what its contents represent. If you
have, you won't make this particular error. People whose only exposure
to programming is writing VBA to extend Access, even if they have
become quite good at that, may not have the same "feel" for the basics
of programming.

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


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top