Help to query date format 9/20/2005 1

[beetlebailey]

I need advice on how to query an Access database table column named "Date" containing the short date format, example: 9/20/2005. I would like to query based on the day (20) and return the results something like this:

SELECT TTRN from ([tbl ttrn index])where day = 20

Thanks in advance.

 

[Remou]

Hi

Select [tbl ttrn index].TTRN From [tbl ttrn index] WHERE (((Format([Date],"dd"))=20))

It is easiest to use the Query Builder. I guess you are stuck with Date, but it is a very awkward name for a field.

This is a good forum for queries:
Microsoft: Access Queries and JET SQL Forum
forum701

 

[PHV]

SELECT SomeField FROM [tbl ttrn index] WHERE Day([Date field])=20

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[beetlebailey]

Neither of the two suggestions are working. Any other ideas ? I am using Excel VBA and a SQL query to tap into Access. The connection and query are woking fine, the query is making it there just coming back empty every time. The problem is breaking the Date field in Access which contains the data into just a single day value.

Thanks, Mark

 

[xlbo]

is the data in Access held as Date/Time ?



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[beetlebailey]

Access Table = tbl TTRN Index
Access Data Type = Date/Time, Format = Short Date

Thanks for all the help.

 

[xlbo]

proposed syntax works fine - what have you tried ?

Please post the VBA & SQL you are using

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question