User Input Date/Time Criteria

[Sinfathisar]

Hi there,

I have a form with its underlying query built from a table. Users access this form by entering a date when prompted. I am having trouble fine tuning the criteria to prompt for the date because underlying field in the table is date/time (general date) - formatted like this: i.e. 2/14/2007 1:14:00 AM.

The field must be a date/time because the client wants to record this information, and the date is initially input (synced) into the access database from a handheld device (using Syware Visual CE).

I want the user to be able to enter a date (either **/**, **/**/**, **/**/****) and return all records from the entire DAY (12am to 11:59pm). This is easy enough if the date is a short date, but no so for date/time.

I was able to use the like statement to sort of get this to work, but it does not work if the date is entered in the format **/**/** (i.e. 04/14/09) because the like statement only allows differences to the ends of what the user inputs, not in the middle, so the missing 20 from 2009 confuses it.

This is the like statement:
Like "*" & [Enter the Date] & "*"

I also tried using the integer date to crop the decimal from microsoft's date, but access just got confused by this and did not return any records.

This is the int statement:
Int[Enter the Date]

I also tried variations on the int date, but none of them are working. I do have a sub report that uses a between int date to pull up records and that works fine, but it is pulling the criteria from information on the main report, not from user input criteria.

I was hoping someone might have some idea as to how I can make this work without making a note on the switchboard that you have to type the date a certain way.

Thanks in advance

 

[honda882000]

Have you tried formatting the date from the table (using Format function) in your query's condition? For example:

WHERE Format(date_time_field,"mm/dd/yyyy") = Format([Form_Field],"mm/dd/yyyy")

You might not need the Format around your form field, depending on its format.

 

[raskew]

Anytime you involve the format() function with a date, date/time or time value, you return a string, not a true date.

Better, if using your output for anything other than strictly display, to use the datevalue(), timevalue() or cdate() functions, each of which will return a true datetime as stored by Access.

Examples: (Note: ? cdbl included in each case to show that y is truly in date/time data format)
1) Date/time string to true datetime:
x = "2/14/2007 1:14:00 AM"
y = cdate(x)
? y
2/14/2007 1:14:00 AM
? cdbl
39127.0513888889

2) Date/time string to true date only.
x = "2/14/2007 1:14:00 AM"
y = datevalue(x)
? y
2/14/2007
? cdbl
39127

3. True datetime to true time only.
x = #2/14/2007 1:14:00 AM#
y = timevalue(x)
? y
1:14:00 AM
? cdbl
5.13888888888889E-02


HTH - Bob

 

[Sinfathisar]

Thanks for the input, but I don't actually want to convert the data format in the table. It already is formatted as a (general) date, and the Visual CE program records the full date/time (information that my client wants recorded). That means that without using Like [Enter Date:] & "*" as my criteria, the user would need to know the date and exact time to bring up the correct record. This will work, but it is limiting because then Access doesn't recognize the year if the user only types the last two digits.

I was hoping to find something that would let me get around this so the user would have greater flexibility for bringing up records, but I am starting to think its not possible without actually reformatting the data.

For now it looks like there will be a note on the switchboard: user must enter date in "this format"

 

[raskew]

Using a table populated with date/time records,
I was able to correctly return records when,
prompted to [Enter Date], I used mm/dd/yy,
e.g 6/30/06. You might test this out with your
data.

PARAMETERS [Enter Date] DateTime;
SELECT Fld1
FROM tblDateTest
WHERE (((Fld1) Like [Enter Date] & "*"))
ORDER BY Fld1;

Please keep us advised.

Bob

 

[Sinfathisar]

Thank you!! Problem solved!

I did try using parameters once before, but I guess I never tried it in conjunction with the Like wildcard statement.

Thanks a million Bob.

 

[raskew]

Hey -

Glad it worked for you.

Best wishes, Bob