Data type mismatch in FP/ASP/Excel Query (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

MikeP -> 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 -> 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::'



MikeP -> 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 -> 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.



MikeP -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.171875