sql error in statement (resolved - I GOT IT!)

[fenster]

hi guys..

im trying to pull all records from my DB (acesss 2000) where

customername is = string
orderstatus is = string
datecomplete is => string
datecomplete is <= string

maybe the join is wrong because i need to join table job and transaction by jobid (primary key)


heres my sql statement

    ssql = "SELECT"
    ssql = ssql & " Customer.Name,"
    ssql = ssql & " job.orderdate,"
    ssql = ssql & " Job.PatientName,"
    ssql = ssql & " Job.[Item Description],"
    ssql = ssql & " Job.jobcode,"
    ssql = ssql & " Job.Order_Status,"
    ssql = ssql & " Job.TrayNumber,"
    ssql = ssql & " Job.JobNumber,"
    ssql = ssql & " Job.JobID,"
    ssql = ssql & " Job.custid,"
    ssql = ssql & " Job.jobcode"
    ssql = ssql & " FROM"
    ssql = ssql & " Customer"
    ssql = ssql & " INNER JOIN"
    ssql = ssql & " Job ON Customer.CustID = Job.CustID"
    ssql = ssql & " WHERE (((Customer.Name) = '" & CUSTTOFIND & "'))"
    ssql = ssql & " AND "
    ssql = ssql & " (((Job.Order_Status) = '" & sType & "'))"
    ssql = ssql & " AND "
    ssql = ssql & " (((transaction.datecomplete) => '" & startdate & "'))"
    ssql = ssql & " AND "
    ssql = ssql & " (((transaction.datecomplete) <= '" & enddate & "'))"
    ssql = ssql & " ORDER BY Job.OrderDate;"

heres the debug print:

SELECT Customer.Name, job.orderdate, Job.PatientName, Job.[Item Description], Job.jobcode, Job.Order_Status, Job.TrayNumber, Job.JobNumber, Job.JobID, Job.custid, Job.jobcode FROM Customer INNER JOIN Job ON Customer.CustID = Job.CustID WHERE (((Customer.Name) = 'Hughes & McHugh (Airdrie)')) AND  (((Job.Order_Status) = 'Invoice')) AND  (((transaction.datecomplete) => '14/08/03')) AND  (((transaction.datecomplete) <= '14/08/03')) ORDER BY Job.OrderDate;

im getting a missing operator in expression....


please help if u can

thanks

davie.






the main culprit was => , >= was accepted...


thanks guys...

[plenderj]

Probably a parenthesis problem if you ask me... hang on...

[plenderj]

Try this :

strSQLString = "SELECT"
    strSQLString = strSQLString & " Customer.Name,"
    strSQLString = strSQLString & " job.orderdate,"
    strSQLString = strSQLString & " Job.PatientName,"
    strSQLString = strSQLString & " Job.[Item Description],"
    strSQLString = strSQLString & " Job.jobcode,"
    strSQLString = strSQLString & " Job.Order_Status,"
    strSQLString = strSQLString & " Job.TrayNumber,"
    strSQLString = strSQLString & " Job.JobNumber,"
    strSQLString = strSQLString & " Job.JobID,"
    strSQLString = strSQLString & " Job.custid,"
    strSQLString = strSQLString & " Job.jobcode"
    
    strSQLString = strSQLString & " FROM"
    strSQLString = strSQLString & " Customer"
    strSQLString = strSQLString & " INNER JOIN"
    strSQLString = strSQLString & " Job ON Customer.CustID = Job.CustID"
    
    strSQLString = strSQLString & " WHERE (Customer.Name = '" & CUSTTOFIND & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (Job.Order_Status = '" & sType & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (transaction.datecomplete => '" & startdate & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (transaction.datecomplete) <= '" & enddate & "')"
    
    strSQLString = strSQLString & " ORDER BY Job.OrderDate;"

[fenster]

hi plenderj thanks for the quick reply - tried the code u posted :


2147217900 syntax error (missing operator) in query expression

could it be that the join for transaction isnt in the sql?

[plenderj]

Your join looks okay. Try reducing the amount of stuff in the query until it finally works and that might pinpoint the problem.
For example simplify the where clauses...

[fenster]

if i rem out lines :

  'strSQLString = strSQLString & " AND "
    'strSQLString = strSQLString & " (transaction.datecomplete => '" & startdate & "')"
    'strSQLString = strSQLString & " AND "
    'strSQLString = strSQLString & " (transaction.datecomplete) <= '" & enddate & "')"

then it works ok...

the culprit is above..

im getting the startdate and enddate from textboxes

Dim startdate As String
Dim enddate As String

startdate = txtStart
enddate = txtEnd

ive been messing with it all morning but with no luck...

[plenderj]

Ah okay it could be colons or spaces in the dates or something like that.
Can you paste to here what the start and end dates are ?

[fenster]

14/08/03 startdate
15/08/03 enddate

i have the field in the db as date/time (transaction.datecomplete)

and the date time format as general date

[pikeman]

Hi,
I think the problem is because you use "transaction" but there is no such table neither after "FROM" nor after "INNER JOIN" clause. The text description of the error (missing operator in expression) might be misleading, it doesn't mean that there is necessarily a missing operator. Anyway, what is the relation of "transaction" to "Job" or "Customer" ? Just add one more "INNER JOIN" (or whatever type of join you need) for table "transaction". On the other side, when I have this kind of problem the error message is something like "The column prefix 'transaction' does not match with a table name or alias name used in the query." ?!?

... and I think you don't need these parenthesis in "WHERE" clause...

Hope this helps

[fenster]

Hi Pikeman,

heres the relationships:

cusomer / job are related by cust.id -

cust.id is the primary key of customer table.

job / transaction are related by job.id -

job.id is the primary key for job table.

what do u mean by adding to the other side?

could u post something to help me??

thanks

[plenderj]

Sorry I had left in a bracket above in the where statements :

strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (transaction.datecomplete => '" & startdate & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (transaction.datecomplete <= '" & enddate & "')"

[fenster]

plenderj:

still get the syntax error in those lines , heres the debug.print:

SELECT Customer.Name, job.orderdate, Job.PatientName, Job.[Item Description], Job.jobcode, Job.Order_Status, Job.TrayNumber, Job.JobNumber, Job.JobID, Job.custid, Job.jobcode FROM Customer INNER JOIN Job ON Customer.CustID = Job.CustID WHERE (Customer.Name = 'Hughes & McHugh (Airdrie)') AND  (Job.Order_Status = 'Invoice') AND  (transaction.datecomplete => '14/08/03') AND  (transaction.datecomplete <= '14/08/03') ORDER BY Job.OrderDate

[fenster]

is this correct :

still trying!!!

    strSQLString = "SELECT"
    strSQLString = strSQLString & " Customer.Name,"
    strSQLString = strSQLString & " job.orderdate,"
    strSQLString = strSQLString & " Job.PatientName,"
    strSQLString = strSQLString & " Job.[Item Description],"
    strSQLString = strSQLString & " Job.jobcode,"
    strSQLString = strSQLString & " Job.Order_Status,"
    strSQLString = strSQLString & " Job.TrayNumber,"
    strSQLString = strSQLString & " Job.JobNumber,"
    strSQLString = strSQLString & " Job.JobID,"
    strSQLString = strSQLString & " Job.custid,"
    strSQLString = strSQLString & " transaction.datecomplete"
    strSQLString = strSQLString & " FROM"
    strSQLString = strSQLString & " Customer"
    strSQLString = strSQLString & " INNER JOIN"
    strSQLString = strSQLString & " Job ON Customer.CustID = Job.CustID"
    strSQLString = strSQLString & " INNER JOIN"
    strSQLString = strSQLString & " Transaction ON Job.jobID = Transaction.jobID"
    strSQLString = strSQLString & " WHERE (Customer.Name = '" & CUSTTOFIND & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (Job.Order_Status = '" & sType & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (transaction.datecomplete => '" & startdate & "')"
    strSQLString = strSQLString & " AND "
    strSQLString = strSQLString & " (transaction.datecomplete <= '" & enddate & "')"

still a missing operator!

[fenster]

where have u gone guys?

we were getting sooooo close

i bracketed [transaction] think its reserved but still missing operator..

come on - im all out of ideas here..

[fenster]

im convinced now that i have to use the # operator in dates?

do u agree?

[plenderj]

Sorry I was wanderign the building. I tried code similar to that myself here and it worked just fine. Granted that was under MySQL, and the dates were back to front, but it worked...

[John9]

Try this :

Dim startdate As String
Dim enddate As String
 
startdate = format$(txtStart, "dd/mm/yyyy")
enddate = format$(txtEnd, "dd/mm/yyyy")
 
 
strSQLString = strSQLString & " AND "
strSQLString = strSQLString & "transaction.datecomplete between startdate and enddate"

=====================
Hope that help!

[Bruce Fox]

Originally posted by fenster
14/08/03 startdate
15/08/03 enddate

i have the field in the db as date/time (transaction.datecomplete)

and the date time format as general date

My 2 bobs worth... (I had a similar problem). Access uses mm/dd/yy format. Your clause has the day and month back to front.



Bruce.

[Bruce Fox]

Ooops, didn't realise it was resolved

What was the problem?