Date Parameters cause ODBC Call Failure

[cabbey77]

Hello,

I have a query, whereby the end users have been inputting the date parameters directly into design view. I am wanting to convert to a parameter query, but when I do, any entry into the pop ups results in an ODBC call failed error. I have also tried a form for input with the same results.

Anyone have any ideas?

Thanks,

C

 

[dhookom]

ODBC suggests the data might be SQL Server or other tables. Can you provide the SQL of the query and information about the table(s)?

Duane
Hook'D on Access
MS Access MVP

 

[cabbey77]

SELECT DISTINCTROW TOP 5 PRODDTA_F42119WMT.SDKCOO, PRODDTA_F42119WMT.SDDOCO, PRODDTA_F42119WMT.SDMCU, PRODDTA_F42119WMT.SDDCTO, PRODDTA_F42119WMT.SDOORN, PRODDTA_F42119WMT.SDOCTO, PRODDTA_F42119WMT.SDRORN, PRODDTA_F42119WMT.SDRCTO, PRODDTA_F42119WMT.SDRLLN, PRODDTA_F42119WMT.SDAN8 AS [Ship To], PRODDTA_F42119WMT.SDSHAN, PRODDTA_F42119WMT.SDPA8 AS [Bill To], PRODDTA_F42119WMT.SDDRQJ, PRODDTA_F42119WMT.SDTRDJ, PRODDTA_F42119WMT.SDPDDJ, PRODDTA_F42119WMT.SDIVD, PRODDTA_F42119WMT.SDADDJ, PRODDTA_F42119WMT.SDLITM, PRODDTA_F42119WMT.SDLOCN, PRODDTA_F42119WMT.SDLOTN, PRODDTA_F42119WMT.SDLNTY, PRODDTA_F42119WMT.SDNXTR, PRODDTA_F42119WMT.SDLTTR, PRODDTA_F42119WMT.SDEMCU, PRODDTA_F42119WMT.SDSRP1, PRODDTA_F42119WMT.SDSRP2, PRODDTA_F42119WMT.SDSRP3, PRODDTA_F42119WMT.SDSRP4, PRODDTA_F42119WMT.SDSRP5, PRODDTA_F42119WMT.SDPRP1, PRODDTA_F42119WMT.SDPRP2, PRODDTA_F42119WMT.SDPRP3, PRODDTA_F42119WMT.SDPRP4, PRODDTA_F42119WMT.SDPRP5, PRODDTA_F42119WMT.SDSOQS, PRODDTA_F42119WMT.SDSOBK, PRODDTA_F42119WMT.SDQTYT, PRODDTA_F42119WMT.SDUPRC, PRODDTA_F42119WMT.SDLPRC, PRODDTA_F42119WMT.SDUNCS, PRODDTA_F42119WMT.SDODOC, PRODDTA_F42119WMT.SDODCT, PRODDTA_F42119WMT.SDLOB, [Standard Cost].IMGLPT, [Standard Cost].COUNCS
FROM PRODDTA_F42119WMT LEFT JOIN [Standard Cost] ON PRODDTA_F42119WMT.SDLITM = [Standard Cost].IMLITM
WHERE (((PRODDTA_F42119WMT.SDKCOO) Like "*10*" Or (PRODDTA_F42119WMT.SDKCOO) Like "*14*") AND ((PRODDTA_F42119WMT.SDMCU) Not Like "*WN*" And (PRODDTA_F42119WMT.SDMCU) Not Like "*EN*") AND ((PRODDTA_F42119WMT.SDDCTO) Like "*SO*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*SE*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*S1*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*CO*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*S7*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*SR*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*YO*" Or (PRODDTA_F42119WMT.SDDCTO) Like "*UO*") AND ((PRODDTA_F42119WMT.SDAN8) Not Like 58625 And (PRODDTA_F42119WMT.SDAN8) Not Like 55965) AND ((PRODDTA_F42119WMT.SDIVD) Between #9/30/2013# And #10/31/2013#) AND ((PRODDTA_F42119WMT.SDLNTY) Not Like "F ") AND ((PRODDTA_F42119WMT.SDSRP1) Not Like "*97*" And (PRODDTA_F42119WMT.SDSRP1) Not Like "*91*" And (PRODDTA_F42119WMT.SDSRP1) Not Like "*98*" And (PRODDTA_F42119WMT.SDSRP1) Is Not Null) AND ((PRODDTA_F42119WMT.SDSOQS)<>0))
WITH OWNERACCESS OPTION;


The tables are linked Tables from JDE.

 

[Andrzejek]

[tt] Between [blue]#[/blue]9/30/2013[blue]#[/blue] And [blue]#[/blue]10/31/2013[blue]#[/blue] [/tt]
is the Access' way of dealing with Dates.

If you connect to Oracle, you need to do this:
[tt]Between TO_DATE('9/30/2013') And TO_DATE('10/31/2013') [/tt]


Have fun.

---- Andy

 

[lameid]

Andy's comment looks right if you are using an SQL Pass-Through (SPT) query but if you have just linked the tables and are running an Access query (unnecessarily slow), you might have better luck specifying the data type of your parameters. But seriously though, just go for the SPT query.

 

[dhookom]

I expect [Standard Cost] might be and Access table so a SQL pass-through won't work.

I would suggest replacing

((PRODDTA_F42119WMT.SDAN8) Not Like 58625 And (PRODDTA_F42119WMT.SDAN8) Not Like 55965)

With

((PRODDTA_F42119WMT.SDAN8) Not In ( 58625,55965))

There is no sense using "Like" without wildcards.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

There's also nothing wrong with using an SPT query and then joining your Access table to that querydef... This is not necessarily better but could be as the Access Engine does a lot of goofy things that are ok when working with native data/files but with RDBMS data not so much.

 

[dhookom]

True, part of the solution for the final query could be from a SPT but the final query would need to be an Access query if there is an Access table involved.

Duane
Hook'D on Access
MS Access MVP