Hello,



This is the error i am getting when user puts container number and hits 

list it gets info from access tables.  i have ran same code in access and 

it worked, but when i put it in asp page i get this below???



I am new to this so any info can help me.



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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) 

in query expression 'dbo_Invoice_Items.invoice_numberFROM dbo_Ports INNER 

JOIN (dbo_Client_Ports INNER JOIN ((dbo_Invoice_Table INNER JOIN 

dbo_Invoice_Items ON dbo_Invoice_Table.invoice_number = 

dbo_Invoice_Items.invoice_number)INNER JOIN (dbo_Vessels INNER JOIN 

(((dbo_Bol_Conta'. 



Here is the code:

 if Len(Trim(Request("Cnttxt"))) > 0 and Len(Request("bnList.x")) > 0 then 

            Sqlst="SELECT dbo_Vessels.vessel_name, dbo_Voyages.voyage_id, 

dbo_Ports.port_name, dbo_Bills_of_Lading.bill_of_lading_id, 

dbo_Invoice_Items.invoice_number" & _

            "FROM dbo_Ports INNER JOIN (dbo_Client_Ports INNER JOIN 

((dbo_Invoice_Table INNER JOIN dbo_Invoice_Items ON 

dbo_Invoice_Table.invoice_number = dbo_Invoice_Items.invoice_number)" & _

            "INNER JOIN (dbo_Vessels INNER JOIN (((dbo_Bol_Containers 

INNER JOIN dbo_Bills_of_Lading ON dbo_Bol_Containers.bill_of_lading_id = 

dbo_Bills_of_Lading.bill_of_lading_id) INNER " & _

            "JOIN dbo_Voyage_Ports ON dbo_Bills_of_Lading.voyage_id = 

dbo_Voyage_Ports.voyage_id) INNER JOIN dbo_Voyages ON 

dbo_Voyage_Ports.voyage_id = dbo_Voyages.voyage_id) " & _

           " ON dbo_Vessels.vessel_id = dbo_Voyages.vessel_id) ON 

dbo_Invoice_Items.ctn_id = dbo_Bol_Containers.ctn_id) ON 

dbo_Client_Ports.company_id = dbo_Vessels.company_id) " & _

           " ON dbo_Ports.port_id = dbo_Client_Ports.port_id"

  











              if Len(Trim(Request("InvTxt"))) > 0 or  Request("Selopt") 

= "Invoice" then

                 Sqlst = Sqlst & " where Invoice_Items.invoice_number =" & 

Request("InvTxt")

               end if

              if Len(Trim(Request("Boltxt"))) > 0 and Request("Selopt") 

= "Bol" then

                 Sqlst = Sqlst & " where Bills_of_Lading.bill_of_lading_id 

=" & Request("Bol")   

              end if

               if  Len(Trim(Request("Vesstxt"))) > 0 and Request("Selopt") 

= "Vessel" and Len(Trim("Voytxt")) > 0 then

                   Sqlst = Sqlst & " where Vessels.vessel_name =" & Request

("Vesstxt") & " and Voyage.voyage_id  =" & Request("Voytxt") 

                end if

             

       



      





RecordCol.Open Sqlst,Mycon,adOpenStatic, adLockReadOnly

Maxrecords = 9





Thanks

Try adding single quotes before and after the literal text generated by the

Request() function within the SQL statement.



The statement below then becomes:



Sqlst = Sqlst & " WHERE Invoice_Items.invoice_number ='" &

Request("InvTxt") & "'"



It is hard to see on screen, but the opening single quote goes between the

equal sign and the first closing double quote; the "'" pattern at the end of

the lineis actually a single quote enclosed in double quotes.



You need to do this for text --  enclose between # and # for dates and don't

enclose for numeric fields in the database.



Does this make any sense?



-- Zee





----- Original Message -----

From: Sharon Buggle <s.buggle@w...>

To: Access ASP <access_asp@p...>

Sent: Sunday, September 16, 2001 10:16 PM

Subject: [access_asp] Microsoft OLE DB Provider for ODBC Drivers error

'80040e14'







> Sqlst = Sqlst & " where Invoice_Items.invoice_number =" &

Request("InvTxt")