Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query in Microsoft Access 2007

Posted on 2014-03-12
15
Medium Priority
?
758 Views
Last Modified: 2014-03-17
I am trying to write a SQL query in Access 2007 to pull data from linked tables from an outside data source, based on an end users selection in a field on a form.  This data is then used to populate other fields on the same form.  

I've used this design in several databases I wrote in Access 2010 without any issues.  However, in this 2007 database, I am getting a "Run-Time error 3075 (Missing Operator)" failing on the identified line in the code.  

Can someone verify whether this is unsupported syntax in v2007, or can you point out the problem I am not seeing?  Thank you!

Dim rst
    
strSQL = "SELECT ms.MST_SHIP_NUM, o.ORD_NUM, ms.SHIP_DT, ad.NAME, ms.CAR_ID, ms.EST_CUBE, vss.VSL_ID, vss.SEAL_NUM " & _
         "FROM CPCMEP.MST_SHIP ms " & _
         "LEFT JOIN CPCMEP.ORD_ACT oa ON ms.MST_SHIP_NUM = oa.MST_SHIP_NUM " & _
         "LEFT JOIN CPCMEP.ORD o ON oa.ORD_NUM = o.ORD_NUM " & _
         "LEFT JOIN CPCMEP.CUS cu ON o.CUS_ID = cu.CUS_ID " & _
         "LEFT JOIN CPCMEP.ADDR ad ON cu.MARK_FOR_ADDR_ID = ad.ADDR_ID " & _
         "LEFT JOIN CPCMEP.VSL vs ON ms.MST_SHIP_NUM = vs.MST_SHIP_NUM " & _
         "LEFT JOIN CPCMEP.VSL_SEAL vss ON ms.MST_SHIP_NUM = vss.MST_SHIP_NUM " & _
         "WHERE ((ms.MST_SHIP_NUM) Like " & Forms![frm_Outbound_Load_Integrity]![MASTER_SHIP_NUM] & ");"
                 
Set rst = CurrentDb.OpenRecordset(strSQL)  'This is the line that is failing
        
    '   If there are records for the MS#...
        If rst.RecordCount > 0 Then
        
            Me.CARRIER = rst!ms.CAR_ID
            Me.SEAL_NUM = rst!vss.SEAL_NUM
            Me.CUSTOMER_NAME = rst!ad.Name
            Me.SCHED_SHIP_TIME = TimeValue(rst!ms.SHIP_DT)
            Me.SHIPMENT_TOTAL_CUBE = rst!ms.EST_CUBE
            Exit Sub
        
        End If

Open in new window

0
Comment
Question by:jmcclosk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39924003
I'm thinking it has to do with the Like operator.  The condition to match must be wrapped in quotes because it is a string comparison.

Try
"WHERE ((ms.MST_SHIP_NUM) Like " & Chr(34) & Forms![frm_Outbound_Load_Integrity]![MASTER_SHIP_NUM] & Chr(34) & ");"

OM Gang
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39924004
is it possible it is failing on the LIKE line, and resolution of shipnum? (type?)
0
 

Author Comment

by:jmcclosk
ID: 39924027
I tried your suggestion.  Still get the same error on the same line.  If it helps any, I got a screenshot of the actual error:Error Message
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

by:omgang
ID: 39924039
Please construct the same query in the QBD, i.e. construct the query in the query designer,  When you have that query executing successfully copy the SQL and paste it here or use it to compare to the query you are constructing in the procedure.

Also, you should follow up on COACHMAN99's comment and verify the value of Forms![frm_Outbound_Load_Integrity]![MASTER_SHIP_NUM].  If it is null it will cause and error in your SQL statement, e.g. you may need to handle nulls such as

Nz(Forms![frm_Outbound_Load_Integrity]![MASTER_SHIP_NUM], 0).....   or something.
OM Gang
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39924061
or even remove the where condition entirely - just to confirm
0
 

Author Comment

by:jmcclosk
ID: 39924077
I already have the logic in the code to make sure there is a value in the Master_Ship_Num field on the form before it will even attempt to execute the query.  All data on the form must be populated before it will do anything.  Sorry I did not clarify to COACHMAN99 that this was the case.

I did originally write this using query designer.  The problem is that execution of the original query can take in excess of 10 - 15 minutes.  It works.  It just takes a very long time.  So, I am trying to refine the query using the SQL statement above to make it run faster.  Here is the SQL view of the original query in designer:

SELECT CPCMEP_ORD.ORD_NUM, CPCMEP_ADDR.NAME, CPCMEP_VSL_SEAL.VSL_ID, CPCMEP_VSL_SEAL.SEAL_NUM, 
FROM (((CPCMEP_MST_SHIP LEFT JOIN CPCMEP_ORD_ACT ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_ORD_ACT.MST_SHIP_NUM) LEFT JOIN ((CPCMEP_ORD LEFT JOIN CPCMEP_CUS ON CPCMEP_ORD.CUS_ID = CPCMEP_CUS.CUS_ID) LEFT JOIN CPCMEP_ADDR ON CPCMEP_CUS.MARK_FOR_ADDR_ID = CPCMEP_ADDR.ADDR_ID) ON CPCMEP_ORD_ACT.ORD_NUM = CPCMEP_ORD.ORD_NUM) LEFT JOIN CPCMEP_VSL ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_VSL.MST_SHIP_NUM) LEFT JOIN CPCMEP_VSL_SEAL ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_VSL_SEAL.MST_SHIP_NUM
WHERE (((CPCMEP_MST_SHIP.MST_SHIP_NUM)=[Forms]![frm_Outbound_Load_Integrity]![Master_Ship_Num]));

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 39924100
The WHERE clause in the SQL from your last post uses and = operator instead of a Like operator.  Does that query execute successfully?

A note:  if the precompiled query, the one created in the query designer, takes a long time to execute it won't be faster constructing the query on the fly via VBA code.  The query you create in code will have to be compiled first before it is executed.  I'm pretty sure it will take longer to execute.

OM Gang
0
 

Author Comment

by:jmcclosk
ID: 39924109
COACHMAN99, I first tried to alter the Where statement to look up a specific MS# I knew was in my DB:

"WHERE ((ms.MST_SHIP_NUM) Like 0000969483);"

Then, I also tried a second attempt at removing the Where statement completely, as you suggested.

Both tests resulted in the exact same error on the exact same line as the original.  Could it be an issue with "rst" or "strSQL"?

I just realized that I did not include in the original information that I have strSQL called as a string variable:

Dim strSQL As String

Sorry I did not include that originally.  I am really at a loss here.  This syntax works great in v2010.
0
 

Author Comment

by:jmcclosk
ID: 39924127
OM Gang, I don't mean to disagree with you, but I have had opposite experiences.  The Query Designer tends to nest the joins causing multiple layers and added complexity to the request, causing huge time loss when pulling data from outside sources.  When I've pulled the SQL out and cleaned it up, the response time tends to increase dramatically.

In terms of the "=", yes I ran it with that originally.  I must have changed it to "Like" at some point afterwards when troubleshooting.  Regardless of which I use, I get the same error each time.
0
 
LVL 28

Accepted Solution

by:
omgang earned 1400 total points
ID: 39924162
Interesting about query execution times compared between precompiled queries and VBA executed SQL statements.  I have at least one old PAQ here on EE from a number of years ago on this topic where significant execution speeds were realized by converting code-generated SQL statements to precompiled (saved) queries.  Perhaps the difference is with your 'cleaned it up'?

Regardless, try this in your code procedure.  It's the exact SQL (almost -- there's a comma at the end of the first line (here --- CPCMEP_VSL_SEAL.SEAL_NUM,) that shouldn't be there) you posted above set for use in VBA.

strSQL = "SELECT CPCMEP_ORD.ORD_NUM, CPCMEP_ADDR.NAME, CPCMEP_VSL_SEAL.VSL_ID, CPCMEP_VSL_SEAL.SEAL_NUM " & _
"FROM (((CPCMEP_MST_SHIP LEFT JOIN CPCMEP_ORD_ACT ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_ORD_ACT.MST_SHIP_NUM) LEFT JOIN ((CPCMEP_ORD LEFT JOIN CPCMEP_CUS ON CPCMEP_ORD.CUS_ID = CPCMEP_CUS.CUS_ID) LEFT JOIN CPCMEP_ADDR ON CPCMEP_CUS.MARK_FOR_ADDR_ID = CPCMEP_ADDR.ADDR_ID) ON CPCMEP_ORD_ACT.ORD_NUM = CPCMEP_ORD.ORD_NUM) LEFT JOIN CPCMEP_VSL ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_VSL.MST_SHIP_NUM) LEFT JOIN CPCMEP_VSL_SEAL ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_VSL_SEAL.MST_SHIP_NUM " & _
"WHERE (((CPCMEP_MST_SHIP.MST_SHIP_NUM)=" & [Forms]![frm_Outbound_Load_Integrity]![Master_Ship_Num] & "));"
0
 
LVL 28

Expert Comment

by:omgang
ID: 39924169
Regarding you rst declaration and use:  you have rst declared as a Variant meaning it can be anything.  You instantiate it as a DAO Recordset object.  That's fine for a variable declared as a Variant.  Also, the error is specific to a problem with the SQL statement itself.

OM Gang
0
 

Assisted Solution

by:jmcclosk
jmcclosk earned 0 total points
ID: 39924412
I don't doubt what you say.  When v2010 rolled out, the company I was working for at the time jumped right from v2003 to the newest, completely skipping over v2007.  The old queries I had in my v2003 DBs weren't cutting it anymore and breaking them out to SQL queries in the code solved the issue.  

But, right now I am open to anything at this point just to be able to get this data populated.    I did what you asked and it looks like we're making some progress.  I was still getting an error, but a different one.  So, I change the "=" to a "Like" and added Chr(34) on either side of the form input.  It worked.

The problem I am having now is rendering the data to the fields.  What am I doing wrong to call the data out of the query?  Here is my code.  I've identified the line that is getting the error:

Dim rst

strSQL = "SELECT CPCMEP_MST_SHIP.MST_SHIP_NUM, CPCMEP_MST_SHIP.CAR_ID, CPCMEP_MST_SHIP.SHIP_DT, CPCMEP_ORD.ORD_NUM, CPCMEP_ADDR.NAME, CPCMEP_VSL_SEAL.VSL_ID, CPCMEP_VSL_SEAL.SEAL_NUM " & _
"FROM (((CPCMEP_MST_SHIP LEFT JOIN CPCMEP_ORD_ACT ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_ORD_ACT.MST_SHIP_NUM) LEFT JOIN ((CPCMEP_ORD LEFT JOIN CPCMEP_CUS ON CPCMEP_ORD.CUS_ID = CPCMEP_CUS.CUS_ID) LEFT JOIN CPCMEP_ADDR ON CPCMEP_CUS.MARK_FOR_ADDR_ID = CPCMEP_ADDR.ADDR_ID) ON CPCMEP_ORD_ACT.ORD_NUM = CPCMEP_ORD.ORD_NUM) LEFT JOIN CPCMEP_VSL ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_VSL.MST_SHIP_NUM) LEFT JOIN CPCMEP_VSL_SEAL ON CPCMEP_MST_SHIP.MST_SHIP_NUM = CPCMEP_VSL_SEAL.MST_SHIP_NUM " & _
"WHERE (((CPCMEP_MST_SHIP.MST_SHIP_NUM) Like " & Chr(34) & [Forms]![frm_Outbound_Load_Integrity]![MASTER_SHIP_NUM] & Chr(34) & "));"


Set rst = CurrentDb.OpenRecordset(strSQL)
        
    '   If there are records for the MS#...
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While (Not rst.EOF)
            
        Me.CARRIER = rst!CPCMEP_MST_SHIP.CAR_ID  'This is the line that is receiving the error
        Me.SEAL_NUM = rst!CPCMEP_VSL_SEAL.SEAL_NUM
        Me.CUSTOMER_NAME = rst!CPCMEP_ADDR.Name
        Me.SCHED_SHIP_TIME = TimeValue(rst!CPCMEP_MST_SHIP.SHIP_DT)
        Me.SHIPMENT_TOTAL_CUBE = rst!CPCMEP_MST_SHIP.EST_CUBE
        Exit Sub
        rst.MoveNext
    Loop
End If

Open in new window


Here's the error:
Error Msg
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 1400 total points
ID: 39924437
You can call by index, e.g..  Keep in mind that the index starts at 0, so the first column in the recordset is 0 (MST__SHIP_NUM), the next is 1, etc.
Me.CARRIER = rst(1)
Me.SEAL_NUM = rst(6)

To get the column names from the recordset you can TEMPORARILY add something like
Debug.Print rst(0).Name
Debug.Print rst(1).Name
etc.

By the way, if you change the variable declaration to
Dim rst As DAO.Recordset
you will then have intellisense available to help when writing the code.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39924493
Great!  Glad it's working for you and glad I could help.
OM Gang
0
 

Author Closing Comment

by:jmcclosk
ID: 39933774
It works now!  Thank you for helping me through this!  I couldn't have done it without the assistance!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question