Solved

Missing operator in query expression

Posted on 2013-10-25
10
490 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
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
 

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

21 Experts available now in Live!

Get 1:1 Help Now