Juan
|
| Posted: 05/13/2004, 5:26 AM |
|
I have a MS Access 2000 DB and ASP and use CodeCharge
I have a date field that I want to search by.
The date is in this format: mm/dd/yyyy
When I enter the date in the search box and click search, it fails.
The form properties are as follows:
Field: InputDate
Variable Name: s_InputDate
Type: Param
Data Type: Date
Operation: = (I also tried Like '%...%')
Required: Not checked
Transfer: Checked
Thanks for any help with this one,
Juan
|
|
|
 |
Juan
|
| Posted: 05/13/2004, 7:01 PM |
|
 I found the solution, here it is:
Dates in Access
Summary
Symptoms: I am trying to execute a search on a Date value stored in an Access database but I keep on getting an error message on the SQL query. What am I doing wrong?
Resolution: MS Access requires date values to be enclosed between a pair of hash marks e.g. #06/04/01#. This means that if you are performing a search against a date value in Access, the default SQL which wraps date values between single quotation marks ('06/04/01') will generate an error. To accommodate this nuance, you have to convert regular dates like 06/04/01 to MS Access compatible date format. You can achieve this by addiing the following code to the 'Open' event of the grid form which is going to display the results of the search.
sWhere=replace(sWhere, "'" & GetParam("start_date") & "'", "#" & GetParam("start_date") & "#")
The replace() function above takes the where clause of the SQL statement and replaces the single quotation marks with hash marks.
I hope this helps someone else!
Juan
|
|
|
|
Gepheto
|
| Posted: 08/16/2004, 12:42 AM |
|
Your problem is the Data Type, on the search field the data type should be TEXT not date, and the use the operator Like% not the Like %%. because date is also considered a text. try this !
|
|
|
|
|
