?
Solved

Missing operator in query expression

Posted on 2013-10-25
10
Medium Priority
?
534 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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