Solved

Missing operator in query expression

Posted on 2013-10-25
10
524 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
[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
  • 2
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39601870
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
ID: 39601882
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
ID: 39601885
Expected 1.  Blogs indicate the field names might be wrong but I have checked them.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:jdfuller
ID: 39601897
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
ID: 39601902
Removed "Exit Do"  Don't need.

Now all I get is no current record when entering first item.  Maybe need to save first?
0
 

Author Comment

by:jdfuller
ID: 39601917
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
ID: 39602028
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
ID: 39602033
Are you actually renumbering 32,000 records or just a small set of that?
0
 

Author Comment

by:jdfuller
ID: 39602124
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
ID: 39602125
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

696 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