|
| |
|
|
MikeP
Posts: 20
From: Cyprus (CET+1)
Status: offline
 |
Data type mismatch in FP/ASP/Excel Query - 10/16/2003 12:30:14
This is about dates and mismatch errors. A common problem it seems but no posting I've found has solved it for me.
I use the DRW (Custom mode) to generate the query. It reads:
SELECT * FROM "Theoprod" WHERE Dates BETWEEN '::Start_Date::' AND '::End_Date::'
I enter the dates in the search form in format: 2003-10-15, which is the same format as used in my Excel database's 'Dates' column.
The error generated reads:
Database Results Error
Description: [Microsoft][ODBC Excel Driver] Data type
mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers
I've tried various 'Format Cell' options in the database, all to no avail.
I've seen posts advocating #" & date & "# but the DRW returns a sytax error from the Excel ODBC immediately if I try that.
Any ideas please?
(I'm using FP 2000, Excel 2002, IIS on WinXP.)
|
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Data type mismatch in FP/ASP/Excel Query - 10/16/2003 12:51:31
Try this:
SELECT * FROM "Theoprod" WHERE Dates BETWEEN #::Start_Date::# AND #::End_Date::#
For some reason, I sometimes have trouble with the BETWEEN statement. If the above does not work, try:
SELECT * FROM "Theoprod" WHERE Dates >= ::Start_Date:: AND Dates <= ::End_Date::'
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
MikeP
Posts: 20
From: Cyprus (CET+1)
Status: offline
|
RE: Data type mismatch in FP/ASP/Excel Query - 10/16/2003 13:59:30
Thanks BB,
First suggestion gives syntax error in the DRW verify query stage.
Second suggestion gets rid of the mismatch error, but only works if I enter date serial numbers on the search form instead of real dates. (If I enter real dates I get 'No records returned').
Well, it's a step in the right direction , but I'll bet that not many people out there will know that today is 37910 (-:
So, does anyone have suggestions for real date entry, please?
| |
|
|
|
BeTheBall
Posts: 6493
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Data type mismatch in FP/ASP/Excel Query - 10/16/2003 15:18:26
Mike, you may have to put your code on a diet to get this to work. Read more about the diet here:
http://www.outfront.net/spooky/adv_drw.htm
I just threw together a page on my site using this SQL and it works as it should:
fp_sQry="SELECT * FROM Adherence WHERE ReviewDay BETWEEN #::Start::# AND #::End::#"
However, if I try to paste the SQL into the Custom Query portion of the wizard, it doesn't verify, as you said. That is why I had to "diet" the code so I could then edit the SQL as needed.
Once you get the SQL as above, if you have continued problems I suspect the cause will be the date format, not the SQL.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
MikeP
Posts: 20
From: Cyprus (CET+1)
Status: offline
|
RE: Data type mismatch in FP/ASP/Excel Query - 10/16/2003 15:48:10
Okay, looks like I've been Microshafted. There's a bug in the DRW - it cannot handle date queries.
The workaround is at:
http://support.microsoft.com/?kbid=296653
But even that did not solve my problems because FP tries to repair the edited code when you resave, and corrupts it. I had to repair FP's 'repair' in Notepad.
What a nighmare. We give the guy 50 billion bucks for this?
Ah well, tks again.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 