decath
|
| Posted: 03/13/2003, 3:38 AM |
|
I'm using ASP 3.0 with templates and MSAccess 2002(XP).
MSaccess requires dates like #2002/02/19#. In CCS I want to display records from a record set that were entered after a certain date (entered by the user in a textbox). When my search query is converted to SQL the date is given as '2002/02/19' which produses an error:
Error: Data type mismatch in criteria expression. (Microsoft JET Database Engine)
SELECT date_submitted FROM tblOrders WHERE date_submitted > '2002/02/19'
I've searched for similar problems in these messages - nothing is very clear given I have very little experience in programming.
Can anyone explain a step by step procedure for getting around this apparent bug?
Regards
decath
|
|
|
|
jp1946
|
| Posted: 03/18/2003, 12:49 AM |
|
Sean addresses this problem succesfully in his topic "CCS B5 Date & Boolean Help with Access" (6/2/2002). The solution given involves editing the common.asp file and I think this should be made unecessary for something as commonly used as date searches with MS Access.
Having edited common.asp I no longer get the "data type mismatch" error, but my searches invariably find no records! I expect it's something to do with the fact that I am using European date formats (dd/mm/yy) and have not got exactly the right combination of date formats set up in Access and CodeCharge Studio (v1.0.7). The trouble is that I have already spent many hours trying different combinations without success. We really need a definitive article on this topic.
I have tried the following:
1. Set regional setting in Windows (2000) Contol Panel to display dd/mm/yy
2. Set Access (2000) to date fields to short date format (dd/mm/yy)
3. Left DBformat blank in CCS date field Properties
3. Set field and parameter source format in CCS Query Builder to dd/mm/yy
What am I missing?
|
|
|
|
daniel
|
| Posted: 03/19/2003, 9:03 AM |
|
I had the same problem like decath.
In the connection window, below connection name, change database type to MS ACCESS. In the same window, click on server tab, set the database also to MS ACCESS, then choose the date-format below.
I'm using windows2000 too, it's working, but I want to filter the database like:
start_date>4/1/2003, or start_date<4/1/2003, or start_date<=4/1/2003
It filters now only:
start_date=4/1/2003
Any ideas how to change the filter options?
|
|
|
|
bsiler
|
| Posted: 03/19/2003, 12:55 PM |
|
From Codecharge FAQ Section
http://support.codecharge.com/files/CodeChargeFAQ.html?...1#_Toc517263823
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 use it to specify Start and Endates as follows:
sWhere=replace(sWhere, "'" & GetParam("start_date") & "'", "#" & GetParam("start_date") & "#")
sWhere=replace(sWhere, "'" & GetParam("end_date") & "'", "#" & GetParam("end_date") & "#")
Hope this helps
|
|
|
|
|
|
