473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Buggy code (Missing Operator in Query Expression)

70 64KB
I have a search form which is working well. It does what I need it to do, until after several searches for no apparent reason, it stops working. It gives me a run-time error 3075 and says "Missing operator in query expression." I've tried a lot of things to see if the error will stop but nothing works. The only thing that has helped is to close out the form, re-open it, and then all is good... for another 15 minutes.

Here is my code. I'd really appreciate any help I can get.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim strWhere As String
  3. Dim lngLen As Long
  4.      If Not IsNull(Me.cboSearchLastName) Then
  5.           strWhere = strWhere & "[LastName] ='" & Me.cboSearchLastName & "' AND "
  6.              End If
  7.              If Not IsNull(Me.cboSearchFirstName) Then
  8.           strWhere = strWhere & "[FirstName] ='" & Me.cboSearchFirstName & "' AND "
  9.              End If
  10.              If Not IsNull(Me.cboSearchOrganization) Then
  11.           strWhere = strWhere & "[OrganizationFK] =" & Me.cboSearchOrganization & " AND "
  12.              End If
  13.              If Not IsNull(Me.cboSearchShopName) Then
  14.           strWhere = strWhere & "[ShopNameFK] =" & Me.cboSearchShopName & " AND "
  15.              End If
  16.              If Not IsNull(Me.cboSearchOfficeSym) Then
  17.           strWhere = strWhere & "[OfficeSymFK] =" & Me.cboSearchOfficeSym & " AND "
  18.              End If
  19.              If Not IsNull(Me.cboSearchBuildingName) Then
  20.           strWhere = strWhere & "[BuildingFK] =" & Me.cboSearchBuildingName & " AND "
  21.              End If
  22.              If Not IsNull(Me.cboSearchRoomName) Then
  23.           strWhere = strWhere & "[RoomsPK] =" & Me.cboSearchRoomName & " AND "
  24.              End If
  25.         Call MsgBox(strWhere, vbOKOnly, "Debug")
  26.     lngLen = Len(strWhere) - 5
  27.     If lngLen <= 0 Then
  28.         MsgBox "No criteria", vbInformation, "Nothing to do."
  29.     Else
  30.         strWhere = Left$(strWhere, lngLen)
  31.         Call MsgBox(strWhere, vbOKOnly, "Debug")
  32.       '  MsgBox "No Records Found."
  33.       Dim Msg As VbMsgBoxResult
  34.  
  35. If DCount("*", "qryRecordSet", strWhere) = 0 Then
  36.  Msg = MsgBox("No corresponding records to your search criteria." & vbCrLf & vbCrLf & _
  37.     "Okay", vbCritical + vbYesNo)
  38.   If Msg = vbYes Then
  39.  Me.FilterOn = False
  40.  Me.cboSearchBuildingName = ""
  41. Me.cboSearchRoomName = ""
  42. Me.cboSearchOrganization = ""
  43. Me.cboSearchShopName = ""
  44. Me.cboSearchOfficeSym = ""
  45. Me.cboSearchLastName = ""
  46. Me.cboSearchFirstName = ""
  47.   Else
  48. Me.Filter = strWhere
  49. Me.FilterOn = True
  50.   End If
  51.  End If
Oct 8 '15 #1
2 937
Seth Schrock
2,965 Expert 2GB
In line 34, place
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strWhere
This will output your string to the immediate window (Ctrl + G to view it). When it doesn't work, look at that and post it here so that we can see what went wrong.
Oct 10 '15 #2
ittechguy
70 64KB
Thanks for your help. I solved the problem. It was an issue with how I was using DCount to check to see if no records were found.
Oct 12 '15 #3

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

Similar topics

0
by: alexz | last post by:
valuA = (request.form("toadd")) If valuA = "" then SQL = "UPDATE CourseReg SET attended='Active' WHERE ID IN("&request.form("toadd")&")" Set RS = MyConn.Execute(SQL) End If MyConn.Close Set...
29
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"...
2
by: Shannan Casteel via AccessMonster.com | last post by:
I have a form for users to enter part numbers. The form has 5 text boxes for each part. Part No1 has a text box for the user to enter his part no., along with a description box, price box, a...
7
by: Chucky | last post by:
I have been going crazy slowly all week. When I step through my code I get Run Time Error 3075, Syntax error, Missing Operator in Query Expression. I think that I have narrowed the error down to...
1
by: Justin R | last post by:
Hey I am really stuck and can't figure out what is wrong here is the code line and surrounding code, if anyone can help i would appreciate it. Thanks This first line is the line that has a problem...
4
by: thebarefootnation | last post by:
Hi I have the following error message "Syntax Error (missing operator) in query expression" occurring when I am trying to update combo box within a form. My code is: Dim strSQL As String
7
by: Cyd44 | last post by:
Hope someone can help with a query expression. I am trying to open a form from a variable set in another form and have the following query set on button click:- If Not Me.NewRecord Then...
2
by: Dilip Krishnan | last post by:
Syntax error (missing operator) in query expression 'Serial Number = 'L3FW341''. The Serial Number field is declared as text in Access Db. I am not sure which operator is missing in my command! ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.