[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Missing operator in query expression

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
jdfuller
Asked:
jdfuller
  • 7
  • 2
1 Solution
 
mbizupCommented:
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
 
jdfullerAuthor Commented:
Now getting "Too few parameters" error.  I had that before the first submission and it went away when I quoted as submitted.
0
 
jdfullerAuthor Commented:
Expected 1.  Blogs indicate the field names might be wrong but I have checked them.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jdfullerAuthor Commented:
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
 
jdfullerAuthor Commented:
Removed "Exit Do"  Don't need.

Now all I get is no current record when entering first item.  Maybe need to save first?
0
 
jdfullerAuthor Commented:
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
 
mbizupCommented:
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
 
IrogSintaCommented:
Are you actually renumbering 32,000 records or just a small set of that?
0
 
jdfullerAuthor Commented:
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
 
jdfullerAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now