CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge -> General/Other

 Date Search

Print topic Send  topic

Author Message
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

:-D 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 !

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.