Strange Query Error

[Mighty]

Guys,

I had a query running successfully on a Pervasive database system but have imported the table to Access and need to get the following query working:

SELECT RM20101.DOCNUMBR, RM20101.DOCDATE, RM20101.TRXDSCRN, RM20101.CURTRXAM, RM20101.GLPOSTDT, RM20101.AGNGBUKT, RM20101.CSPORNBR, MC00100.XCHGRATE, RM40401.DOCABREV FROM (RM20101 INNER JOIN RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL) INNER JOIN MC00100 ON RM20101.DOCDATE Between MC00100.EXCHDATE And MC00100.EXPNDATE WHERE RM20101.CUSTNMBR = 'ORBUS' AND MC00100.EXGTBLID='USD TBL' ORDER BY RM20101.DOCNUMBR

When I run this query (from ASP), I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Between operator without And in query expression 'RM20101.DOCDATE Between MC00100.EXCHDATE'.

However, there is clearly a "Between" and an "And" in the query. Maybe I need brackets. Can anyone help me out??

Mise Le Meas,

Mighty

 

[Databaseguy]

ASP is picky. You've already figured out to change the quotes to apostrophes.


Looks like you've got to put your between AFTER the where clause in your statement.

Between MC00100.EXCHDATE And MC00100.EXPNDATE WHERE RM20101.CUSTNMBR = 'ORBUS' AND MC00100.EXGTBLID='USD TBL' ORDER BY RM20101.DOCNUMBR



Hint, i use Access to write the query & cut and paste the SLQ. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Mighty]

But I am using the Between...And for the join.
How can I put this after the where clause?? Mise Le Meas,

Mighty

 

[tlbroadbent]

Between is not proper JOIN syntax. It is acceptable syntax in a WHERE clause. Try the following syntax.

SELECT
a.DOCNUMBR, a.DOCDATE, a.TRXDSCRN, a.CURTRXAM,
a.GLPOSTDT, a.AGNGBUKT, a.CSPORNBR,
c.XCHGRATE, b.DOCABREV
FROM RM20101 As a, RM40401 As b, MC00100 As c
WHERE a.RMDTYPAL = b.RMDTYPAL
AND a.DOCDATE BETWEEN c.EXCHDATE AND c.EXPNDATE
AND a.CUSTNMBR = 'ORBUS'
AND c.EXGTBLID='USD TBL'
ORDER BY a.DOCNUMBR


NOTE: the use of aliases for the table names to shorten the query and improve readability. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Mighty]

Terry,

Thanks for the tips but it is still not working. Below is the SQL command and the stupid error that Access is giving me:

SELECT a.DOCNUMBR, a.DOCDATE, a.TRXDSCRN, a.CURTRXAM, a.GLPOSTDT, a.AGNGBUKT, a.CSPORNBR, c.XCHGRATE, b.DOCABREV FROM RM20101 As a, RM40401 As b, MC00100 As c WHERE a.RMDTYPAL = b.RMDTYPAL AND a.DOCDATE BETWEEN c.EXCHDATE AND c.EXPNDATE AND a.CUSTNMBR = 'CUST12' AND c.EXGTBLID='USD TBL' ORDER BY a.DOCNUMBR


Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/clientarea/stmtPrint.asp, line 90


Any ideas??? Mise Le Meas,

Mighty

 

[Mighty]

Guys,

Got it working - my own fault. I was selecting a field that wasn't in the table. Duh!! THe following SQL command worked (Between...And in the join)

SELECT RM20101.DOCNUMBR, RM20101.DOCDATE, RM20101.TRXDSCRN, RM20101.CURTRXAM, RM20101.GLPOSTDT, RM20101.CSPORNBR, MC00100.XCHGRATE, RM40401.DOCABREV FROM (RM20101 INNER JOIN RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL) INNER JOIN MC00100 ON (RM20101.DOCDATE Between MC00100.EXCHDATE And MC00100.EXPNDATE) WHERE RM20101.CUSTNMBR = 'CUST12' AND MC00100.EXGTBLID='USD TBL' ORDER BY RM20101.DOCNUMBR

Mise Le Meas,

Mighty

 

[tlbroadbent]

I'm glad you got it to work. Which version of Access are you running? The between clause in the JOIN doesn't work for me. I'm using Access 2000. I get a an error, "Join expression not supported".

One of the problems you can encounter is that MS products don't adhere to ANSI standards but MS is making an effort to standardize the products. In the meantime, you can write a query using invalid syntax, the MS product accepts the syntax and processes the query. However, at the next relase of the product, the query may stop working.

Even worse, Access may accept the syntax but process the query incorrectly. You get an erroneous result but may not know it is incorrect.

Just my 2 cents of caution. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Mighty]

Terry,

Thanks for the tip.
I am only using Access 97.
It is most likely that we will not be upgrading to Access 200 but instead to SQL Server so I will probably have to alter alot of my queries anyway.

Thanks again for the help. Mise Le Meas,

Mighty