Solved

SQL Query in Microsoft Access 2007

Posted on 2014-03-12
15
722 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
  • 7
  • 6
  • 2
15 Comments
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Comment Utility
is it possible it is failing on the LIKE line, and resolution of shipnum? (type?)
0
 

Author Comment

by:jmcclosk
Comment Utility
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
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Comment Utility
or even remove the where condition entirely - just to confirm
0
 

Author Comment

by:jmcclosk
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jmcclosk
Comment Utility
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
Comment Utility
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 350 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 350 total points
Comment Utility
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
Comment Utility
Great!  Glad it's working for you and glad I could help.
OM Gang
0
 

Author Closing Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now