Date/Time Conversion from remote form through query / [FORMS]! 1

[Stevehewitt]

Hi Everyone,

The database tables I am running the query against is third party, so I'm stuck with having the date stored as: YYYY-MM-DD HH:MM:SS

When reporting, we don't report on the time - just the date; so I have the following as the criteria in my query:

Like Format([Enter the date],'yyyy-mm-dd') & ' *'

However, I want to be able to change the query from a standard parameter query to using the [FORMS] function to pull the date from a field that is entered into a form.
Obviously the issue is that they will just be entering in a date and no time; so I need to keep using 'LIKE' in the query.

Any ideas how to incorporate a [FORMS]![frmDateReport]![txtDate] parameter into the query above?

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[mp9]

If I understand this right, the problem is that on the form just a date is entered, e.g. 01/07/2005

But that you want this to pick up everything from 2005-07-01 00:00:00 to2005-07-01 23:59:59 inclusive?

In which case, how about something along these (untested) lines:

Between Format([FORMS]![frmDateReport]![txtDate],'yyyy-mm-dd') and Format([FORMS]![frmDateReport]![txtDate] + 1,'yyyy-mm-dd')

This should work as a date without a time defaults to 00:00:00, so in effect you're asking for everything from 00:00:00 on the day you specified to 00:00:00 the next morning.

Hope this is of use.

http://www22.brinkster.com/accessory/

 

[PHV]

You may try this:
PARAMETERS [Forms]![frmDateReport]![txtDate] DateTime;
SELECT ...
FROM ...
WHERE ...
Like Format([Forms]![frmDateReport]![txtDate],'yyyy-mm-dd') & ' *'

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

 

[Stevehewitt]

Thanks mp9, but it wasn't quite what I was after.

PHV - once again I owe my thanks! Work a charm.

Thanks again,



Steve.

"They have the internet on computers now!" - Homer Simpson