Reading Dates from a Formatted Field (1 Viewer)

JCorreia

Registered User.
Local time
Today, 12:44
Joined
May 9, 2013
Messages
17
In order to perform conflict searches in my Access 2007 database, you enter a bunch of information and then receive a report of cases with similar information in order to avoid any conflicts of interest.
One of the fields is "Date of Accident". Currently I have its format set as yyyy/mm/dd. However, my expression
Code:
Date Searched: [Forms]![frmConflictSearch]![Date of Accident]
returns nothing (if I look at the query in Datasheet the column is blank).
I found a fix for getting it to appear by removing the format on Date of Accident, however, my query doesn't retrieve cases with matching dates (the dates are formatted to dddd, mmmm dd, yyyy as a Date/Time Data Type).
Is there any way I can get the query to both return the date searched, AND retrieve matching cases?
Thanks!
 

dpelizzari

Registered User.
Local time
Today, 12:44
Joined
Jun 10, 2010
Messages
26
JCorreia, you may need to look further into the format of the field in the table, there are options for showing the date in mulitple formats (long, short, time only, etc... if you have the user inputting just the date (1/1/2013), but the data format in the table is mm/dd/yyyy:00:00:00, it won't return any values. If changing the format isn't an option, you can change the query to look for values >= Date Searched and <=Date Searched +1 day. I guess another option would be, if the time stamp is there as well, to add the time stamp to the field in the query.
 

JCorreia

Registered User.
Local time
Today, 12:44
Joined
May 9, 2013
Messages
17
:/ Waiting for the administrator to let me in to the site to watch the video.
dpelizzari, I think the problem lies with how I read the value to display it in a separate field (the report), not how I compare it to the values in the table.
That being said, if there was a way to reliably compare a text field with a date field, that would probably solve the problem too...
 

JCorreia

Registered User.
Local time
Today, 12:44
Joined
May 9, 2013
Messages
17
Thanks Uncle Gizmo, that actually did give me some inspiration!
I ended up creating an hidden text box and made the After Update event of the Date of Accident make the value of the box the string value of the date.
That way, I have the text box the user types in as a Date format, which can be compared, and the hidden text box to output to the expression in the query.
Thanks for your help!
 

Users who are viewing this thread

Top Bottom