Solved

Missing operator in query expression

Posted on 2013-10-25
10
482 Views
Last Modified: 2013-10-25
What is wrong with this statement?  Error is "Missing operator"

Form values from access are picked by the variables which are varEID (a number, integer) and strRvCtl ( a character).

Set rst = dbs.OpenRecordset("SELECT * FROM tbl_est_detail WHERE [EST_ID] = " & varEID & " And [REV_CTL] = " & strRvCtl, dbOpenDynaset, dbSeeChanges)
0
Comment
Question by:jdfuller
  • 7
  • 2
10 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
If rev_ctl is text, delimit it with quotes:

Set rst = dbs.OpenRecordset("SELECT * FROM tbl_est_detail WHERE [EST_ID] = " & varEID & " And [REV_CTL] = '" & strRvCtl & "'", dbOpenDynaset, dbSeeChanges)

Open in new window

0
 

Author Comment

by:jdfuller
Comment Utility
Now getting "Too few parameters" error.  I had that before the first submission and it went away when I quoted as submitted.
0
 

Author Comment

by:jdfuller
Comment Utility
Expected 1.  Blogs indicate the field names might be wrong but I have checked them.
0
 

Author Comment

by:jdfuller
Comment Utility
Doh!! I had the subform field designation wrong.  Solved the missing parameter now I have "No current record." error.

I am adding a line to an Estimate and the sub form has the details on it.  When adding or deleting a line I run a renumber routine that has to filter through 32,000 records to find just the one that need to be cycled through to change the line numbers.

Private Sub Renum()

    '////// For Item Numbering code at end of event.  ///////
    Dim dbs As dao.Database
    Dim rst As dao.Recordset
    Dim varEID As Integer
    Dim strRvCtl As String
    Dim intItemNum As Integer
   
    intItemNum = 0
   
    'Capture filter variables from form.
    varEID = [Forms]![frm_estimate]![EST_ID]
    strRvCtl = [Forms]![frm_estimate]![REV_CTL]

    Set dbs = CurrentDb
 
    Set rst = dbs.OpenRecordset("SELECT * FROM tbl_est_detail WHERE [EST_ID] = " & varEID & " And [REV_CTL_LINE] = '" & strRvCtl & "'", dbOpenDynaset, dbSeeChanges)

   
            'Renumber all items or number them if not numbered.  All items renumbered after each selection of part.
           
            rst.MoveFirst
       
            Do While Not rst.EOF
                       
                    intItemNum = intItemNum + 1
                    rst.Edit
                    rst!ITEM_NUM = intItemNum
                    rst.Update
                    rst.MoveNext
            Loop
            Exit Do
            rst.MoveNext
             
    rst.Close
    Set rst = Nothing
   
    Forms![frm_estimate]![EstDetSubfrm].Requery
   
End Sub
0
 

Author Comment

by:jdfuller
Comment Utility
Removed "Exit Do"  Don't need.

Now all I get is no current record when entering first item.  Maybe need to save first?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:jdfuller
Comment Utility
Had an extra Move next.  Working now.

OK.  After hours of getting in to SQL, how do I speed this code up? It takes way to long to renum these few records.  It is JUST for looks and reference when talking to the customer.

Below is the working code...

Private Sub Renum()

    '////// For Item Numbering code at end of event.  ///////
    Dim dbs As dao.Database
    Dim rst As dao.Recordset
    Dim varEID As Integer
    Dim strRvCtl As String
    Dim intItemNum As Integer
   
    intItemNum = 0
   
    'Capture filter variables from form.
    varEID = [Forms]![frm_estimate]![EST_ID]
    strRvCtl = [Forms]![frm_estimate]![REV_CTL]

    Set dbs = CurrentDb
 
    Set rst = dbs.OpenRecordset("SELECT * FROM tbl_est_detail WHERE [EST_ID] = " & varEID & " And [REV_CTL_LINE] = '" & strRvCtl & "'", dbOpenDynaset, dbSeeChanges)

   
            'Renumber all items or number them if not numbered.  All items renumbered after each selection of part.
           
            rst.MoveFirst
       
            Do While Not rst.EOF
                       
                    intItemNum = intItemNum + 1
                    rst.Edit
                    rst!ITEM_NUM = intItemNum
                    rst.Update
                    rst.MoveNext
            Loop
             
    rst.Close
    Set rst = Nothing
   
    Forms![frm_estimate]![EstDetSubfrm].Requery
   
End Sub
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Sorry for the delay - dinner.

This is a completely different question than what you started with - which should be in a separate thread per EE's guidelines.

That said, I don't think there is much you can do to speed up that code.  However, I don't recommend actually updating the table.  You can display record counts in queries.

Do you have an autonumber field, date field, or anything else to indicate sequence?

If so, you can query the data like this (I'm assuming that Est_ID is a sequential number):

SELECT e.*, (SELECT Count(*) FROM tbl_est_detail eDupe  WHERE eDupe.Est_ID <= e.Est_ID) as ItemNumber
FROM tbl_est_detail e;

Open in new window


... and use that as the recordsource of your form.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Are you actually renumbering 32,000 records or just a small set of that?
0
 

Author Comment

by:jdfuller
Comment Utility
Sorry - my dinner!  No just a small set; several or one line item but hardly more than 5 or 6.

To overcome the delay, I have made the module an event triggered by the user choice to renumber records.  Mostly its for reference, nothing else.  I also opened up the field so they can put digits in it themselves.  Its just not important but looks good.  Sacrificing coolness for speed.  Not a huge issue.  My research shows a bunch of folks want to know how to do it but referencing the records is a challenge.  I'm thinking a stored procedure is better for speed.

Thanks for the help!

JDF
0
 

Author Closing Comment

by:jdfuller
Comment Utility
The Q got off topic slightly but the narrowing down of the problem had to be in the same thread to make sense.

Thanks!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

6 Experts available now in Live!

Get 1:1 Help Now