473,883 Members | 2,819 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

syntax error, missing operator

15 New Member
I hope it is not bad form to join and immediately ask a question (especially one that appears to be common). I have looked at my code so long today, that I cannot figure out what MS Access/VBA wants. I am apparently missing an operator. Any help is greatly appreciated.

The snippet flagged when I debug is:
Expand|Select|Wrap|Line Numbers
  1.     rs1.FindFirst ("[ReportType]= & Report_Type" & _
  2.         "FROM Report_Status" & _
  3.         " WHERE Report_Status.[PI] = '" & PI_Number & "' " & _
  4.         "ORDER BY Report_Status.[PI]; ")
Oct 4 '11 #1
28 3590
931 Recognized Expert Contributor
The typical usage for FindFirst assumes that you previously populated the recordset (using say CurrentDB.Execu te or OpenRecordset). Once the recordset is populated, you use FindFirst to grab the first record in the recordset that meets the criteria that you specify in the argument.

The argument that you pass to FindFirst appears to have such criteria ("[ReportType] = & Report_Type"), but then you follow it with a fragment of a SQL string (a SQL statement without a SELECT clause isn't even possible, but that's a little beside the point here).

Can you 1) post more code so that we can see what you're doing prior to the FindFirst call, and 2) explain what you're trying to accomplish with the SQL fragment?

Oct 4 '11 #2
12,516 Recognized Expert Moderator MVP
Here's what that string looks like once it's translated.

Given PI_Number = 'abc'
Expand|Select|Wrap|Line Numbers
  1. [ReportType]= & Report_TypeFROM Report_Status WHERE Report_Status.[PI] = 'abc' ORDER BY Report_Status.[PI];
Oct 4 '11 #3
15 New Member
Rabbit, thanks - I am new to VBA so I was still muddling through what that would come out as.

zepphead80, Funny thing is that when I saved & closed my code, then closed the form, then reopened the form and clicked the button that's supposed to run this, it (sortof) worked. Gave the opposite response than I wanted...

So I have this database with Project ID Numbers (PI_Number) and I want to allow users to use a form to enter data. Problem is my users are the typical person who doesn't understand to (manually) search for previous entries in the database before creating a new entry.

1 problem with not allowing duplicate PI numbers is that we have duplicates based on what report type they are viewing. So no two PI + Report Type can duplicate. I want to allow user to enter a PI Number and Report Type and have code to run when s/he hits "save" (not the default - but a button on the form) that looks for the PI NUmber + Report Type combination in the database. If the combo already exists, give them a pop up message that states "exists - going to record" and they click ok and go. If the combo doesn't exist, the user is none the wiser, and a new entry is created.

To me this sounds very convoluted...

Anyhow... my code follows for the find record if no record give a mesage box (testing before just allowing a save).

Expand|Select|Wrap|Line Numbers
  2. Private Sub Save_Record_Click()
  3.     If (PI_Number & vbNullString) = vbNullString Then Exit Sub
  5.     Dim rs1 As DAO.Recordset
  6.     Set rs = Me.RecordsetClone
  7.     Set rs1 = Me.RecordsetClone
  9.     rs1.FindFirst ("[ReportType]= & Report_Type" & _
  10.         "FROM Report_Status" & _
  11.         " WHERE Report_Status.[PI] = '" & PI_Number & "' " & _
  12.         "ORDER BY Report_Status.[PI]; ")
  14.     If rs1.NoMatch Then
  15.         MsgBox "Sorry, no such record '" & Me.PI_Number & "' " & Me.Report_Type & "' was found.", _
  16.                vbOKOnly + vbInformation
  17.     Else
  18.         Me.Recordset.Bookmark = rs.Bookmark
  19.     End If
  20.     rs.Close
  22. End Sub
Honestly, I have no idea if I'm doing any of it correctly, but I sometimes get the error message (yea!) but sometimes the error message is, well, erroneous (boo!)
Oct 4 '11 #4
15 New Member
Could the error possibly be fixed by inserting "Select"? If so, I have *no idea* where to insert the word.
Oct 4 '11 #5
931 Recognized Expert Contributor
This does not seem convoluted to me at all. What you are implementing is something called a compound primary key...it relies upon more than one column in order to establish uniqueness. A compound key is not ideal, but it does have its place in table design. It's possible that you might be able to go further in normalizing your tables to eliminate this situation, but that's a little beside the point of what you're asking.

I would try and write the FindFirst call as this:

Expand|Select|Wrap|Line Numbers
  1. rs1.FindFirst ("[ReportType]= " & Report_Type & " AND [PI] = '" & PI_Number & "'")

I am assuming here that PI_Number is a text string, and so enclosing it in ' '. Note that concatenation is important here; for example, if you include Report_Type inside the string then, as Rabbit points out, VBA interprets the whole thing as [ReportType] = Report_Type. In order for VBA to recognize Report_Type as a control on your form, it needs to be outside the string.

If rs.NoMatch is true, then you can proceed with the save. Otherwise you need to raise a message and exit gracefully.

Oct 4 '11 #6
15 New Member
Thank you. I could not for the life of me figure out how to properly concatenate that - hence why I was (poorly) using the SQL.

I am still getting my message box when I know rs1.NoMatch should be false. That is, say I have a report in the database that is entitled '0000000' (seven zeros, as text) and has a report type of 'CR'. When I type that into my form, I should not get the message box. But I do...

When I switch the clauses (making it so that I get a message should the report already exist & saving the entry from the form if record does not exist) then I get a run time error "No Current Record".

Expand|Select|Wrap|Line Numbers
  2.     If rs1.NoMatch Then
  3.         Me.Recordset.Bookmark = rs1.Bookmark
  4.     Else
  5.         MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", _
  6.                vbOKOnly + vbInformation
  7.     End If
Any thoughts?
Oct 4 '11 #7
15 New Member
Sorry to continuously "double post" (triple post?) but I just noticed it is not correctly going through the if Statment. Should I create a new topic for this?
Oct 4 '11 #8
931 Recognized Expert Contributor
Usually, how you save a record when working with DAO recordsets is to do something like this:

Expand|Select|Wrap|Line Numbers
  1. rs.AddNew
  3. rs.[field1] = control1_value
  4. rs.[field2] = control2_value
  6. ...
  8. rs.Update

What you're trying to do as the code currently stands is take the current record and populate the form with it. This is the opposite of your goal.
Oct 4 '11 #9
15 New Member
Thank you again! I will try this and see what I can break from there. :)

However, the NoMatch condition is giving me true everytime. So I get "Hello" even when the if statement *should* be false. So if I correct the "add new" portion, I believe I'll be back at square one of adding a record which already exists... And that is also the opposite of what I'm trying to accomplish.

So the new problem is the if statement I guess.
Oct 4 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules" for when and how to use single quotes and double quotes in ASP? thanks! ---------------------- SQL = SQL & "WHERE '" & REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE("GenKTitles.
by: dcarson | last post by:
I've read about this error in several other discussions, but still can't seem to pinpoint the problem with my code. Everything seemed to be working fine for some time, but it now tends to bomb out on me for some reason. The Summary field is pulled from a Rich Text Editor that allows HTML formatting and appears to be the culprit, but I just can't seem to figure out why. Any guidance is greatly appreciated. I'm connecting to an Access...
by: Alan Murrell | last post by:
Hello, One of our web hosting clients is getting the following error when someone tried to log in form their login page: --- ODBC ERROR --- Microsoft OLE DB Provider for ODBC Drivers error '80040e14' Syntax error (missing operator) in query expression 'userid= AND password='''.
by: khan | last post by:
getting, error, 3075 Syntax Error (Missing Operator) in query expression '8WHERE .=1' Dim TotalQty As Integer TotalQty = DLookup("", "", "=" & Forms!!)
by: chug | last post by:
I'm not a programmer but it's my job to solve this. Can someone help me with this error message? Microsoft OLE DB Provider for ODBC Drivers error '80040e14' Syntax error (missing operator) in query expression 'auctionid ='. /Mediator/AgentSetup.asp, line 71
by: BigGuy316 | last post by:
Here is the error message I am getting along with the code: Error Type: Microsoft JET Database Engine (0x80040E14) Syntax error (missing operator) in query expression 'IDFrm = 38 AND IDSpec ='. Dim rsResults Dim rsResults_numRows Set rsResults = Server.CreateObject("ADODB.Recordset")
by: kabradley | last post by:
Hey guys, I'm using an embedded sql statement tied to a command button to append records in a certain table. The code for the sql statement is as follows. 'Define sql string strSQL = "INSERT INTO LPID (LPName, LPUnitPrice, VendorFamily, LPCatagoryID, LPStructure)" & _ "SELECT LPID.LPName, LPID.LPUnitPrice, LPID.VendorFamily, LPID.LPCatagoryID, LPID.LPStructure" & _ "FROM LPID" & _ "WHERE (( = !!!LPName)" & _ "( = !!!LPUnitPrice)" & _ "(...
by: access baby | last post by:
I hava a date parameter filter query but it shows error Syntax error missing operator in query experssion can some one please help where am i going wrong in expression SELECT copyorderdtl * from copyorderdtl where(((cr_created_date)is Null) and ((cr_statusupdt_date)=)) OR (((cr_created_date) is not null and (cr_statusupdt_Date)=));
by: aaron6098 | last post by:
I am trying to finish my final project for my programing class. i keep on getting Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) Syntax error (missing operator) in query expression 'OrderID ='. /students/11/scripts/OrdersLines.asp, line 12 I place the ' ;' and then i recieve Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) Data type mismatch in criteria expression....
by: munkee | last post by:
I am trying to dynamically update the rowsource of a chart within access. To achieve this I moved the original rowsource sql of the chart which is: SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum("","tblParetoRaising",">=" & & "")/DSum("","tblParetoRaising") AS CPct FROM tblParetoRaising; And replaced it with the rowsource being a query called "qryPareto" I update the qryPareto using QueryDefs which...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.