Wild cards in DateTime critrea query

[action]

I have a long date feild I query against and use a wild card in the time part of a long datetime field. Can I do this and how would it be done?

Cheers

 

[Mile-O]

Wildcard is for text; if you want to disregard the time part then use the DateValue() function.

 

[namliam]

Or format the datetime field using format(,"") to a string
You can the use the wildcard to your harts content...

Regards

The Mailman

 

[action]

DateValue more questions

Thanks Mile-o-phile,
I have read up on datevalue and it worked great for a between function but if I wanted to select today's records how can I get this working. I have tried the real basic options but no joy.

My between statement that works is:

Between DateValue([Start]) And DateValue([End])

The query will only be run to view todays data, Date() & Now() I can't get working.

Any further pointers.

Apperciate the help.

Cheers

 

[namliam]

Where datevalue(YourField) = date()

Should return todays entries....

Regards

 

[action]

cheers

Thanks, I'll try it in the morning as It's time to go home here in NZ..

 

[action]

Frustrated!

Here is the SQL which works great:

SELECT Calender.AwayTeamID, [QRY Match Players with debt with Team].Balance, [QRY Match Players with debt with Team].Name, Calender.DateTime, DateValue([Calender].[DateTime]) AS Newdate
FROM [QRY Match Players with debt with Team] INNER JOIN Calender ON [QRY Match Players with debt with Team].ID = Calender.AwayTeamID
WHERE ((DateValue([Calender].[DateTime]))=#10/16/2003#));

When I replace the end date with =date() It won't run.

Note the field which the "today" date criteria is in has been stripped of the time part and I think this is the problem.

Can anybody reslove this?

 

[action]

This didn't work! It had another critrea which I have pulled out thats why the extra parentheses was there.

this is the sql as it stands now:

SELECT Calender.AwayTeamID, [QRY Match Players with debt with Team].[Last Name], [QRY Match Players with debt with Team].[First Name], [QRY Match Players with debt with Team].Balance, [QRY Match Players with debt with Team].Name, Calender.DateTime, [QRY Match Players with debt with Team].Company, DateValue([Calender].[DateTime]) AS Newdate
FROM [QRY Match Players with debt with Team] INNER JOIN Calender ON [QRY Match Players with debt with Team].ID = Calender.AwayTeamID
WHERE DateValue([Calender].[DateTime]) = Date();

This does not work. Any more suggestions?

 

[EMP]

Just a simple test for DateValue([Calender].[DateTime]) = Date() on your system.

The attached database contains five records from 10/14/2003 to 10/18/2003 and a query:-

SELECT Calender.*, DateValue([Calender].[DateTime]) AS Newdate
FROM Calender
WHERE DateValue([Calender].[DateTime]) = Date();


When you run the query, will it return the correct record? If it will, then the problem is something else.