Runtime error 3075

Status
Not open for further replies.

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
First let me say that I am new to Access. I currently using Access 2010. In my table I have 10 Fields, the field names are below:

EmployeeEntry = AutoNumber
EmployeeNumber=Text
FirstName=text
LastName=text
Title=text
EmailAddress=text
workphone=text
extension=text
HireDate=date/time
Notes=Memo
VBA Code:
Private Sub cmdAdd_Click()
   'adds data to table
   'refresh data in table
   CurrentDb.Execute "INSERT INTO MaintenanceEmployees(EmployeeNumber,FirstName,LastName,TItle,EmailAddress,WorkPhone,Extension,HireDate)" & _
      "VALUES('" & Me.EmployeeNumber & ",'" & Me.FirstName & "','" & Me.LastName & "','" & Me.TItle & "','" & Me.EmailAddress & "','" & _
      Me.WorkPhone & "','" & Me.Extension & "','" & Me.HireDate & "')"
   
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
First let me say that I am new to Access. I currently using Access 2010. The error message states "Syntax error (missing operator) in query expression" Then it shows my entry followed by ","," for entries that don't apply to that person.

In my table I have 10 Fields, the field names are below:

EmployeeEntry = AutoNumber
EmployeeNumber=Text
FirstName=text
LastName=text
Title=text
EmailAddress=text
workphone=text
extension=text
HireDate=date/time
Notes=Memo
VBA Code:
Private Sub cmdAdd_Click()
   'adds data to table
   'refresh data in table
   CurrentDb.Execute "INSERT INTO MaintenanceEmployees(EmployeeNumber,FirstName,LastName,TItle,EmailAddress,WorkPhone,Extension,HireDate)" & _
      "VALUES('" & Me.EmployeeNumber & ",'" & Me.FirstName & "','" & Me.LastName & "','" & Me.TItle & "','" & Me.EmailAddress & "','" & _
      Me.WorkPhone & "','" & Me.Extension & "','" & Me.HireDate & "')"
  
End Sub
 
Upvote 0
When I Google "vba 3075", I get this as the first hit, which seems to describe and solve your exact problem...


Specifically, you are missing an apostrophe. Try this:

VBA Code:
"VALUES('" & Me.EmployeeNumber & "','" & Me.FirstName & "','" & Me.LastName & "','" & Me.TItle & "','" & Me.EmailAddress & "','" & _
 
Upvote 0
Solution
That worked. I can't believe I missed that. Any advise on how to prevent this in the future. Thank You
 
Upvote 0
That worked. I can't believe I missed that. Any advise on how to prevent this in the future. Thank You
When you run into an issue like this, step through the code and view the SQL string in the immediate window. That can often be enough to spot the issue. If you're still stuck at that point, you can copy-paste the SQL to a query in the query editor and try to run it. The error messages there aren't always more helpful but the query editor will sometimes put the cursor where the problem is so you at least know where to look while you try to figure out what to do. :)
 
Upvote 0
Try using this syntax to fix your runtime error
Private Sub Command8_Click()
Dim SQL As String

SQL = "SELECT tblVisit.PatientID, tblPatients.Firstname, tblPatients.Lastname" _
& "DateDiff('yyyy',[tblPatients]![Birthday],Now()) AS Age, tblVisit.[Date Visit]" _
& "tblVisit.Diagnosis" _
& "[First Name] & ' ' & [Last Name] AS [Prescriber Names] FROM tblPatients INNER JOIN (tblDoctor INNER JOIN tblVisit ON tblDoctor.DoctorID = tblVisit.DoctorID) ON tblPatients.PatientID = tblVisit.PatientID" _
& "WHERE [Firstname] = '" & Me.txtKeywords & "' "

Me.subClientList.Form.RecordSource = SQL
Me.subClientList.Form.Requery

End Sub
?

The error is runtime error '3075'
syntax error (missing operator) in query expression 'tvist'.[DateVisit]tblVist.Diagnosis[First Name]& ' ' [Last Name]'

Let me know if this works for you!
Matt Henry
 
Upvote 0
you're missing a lot of commas

VBA Code:
dim sql as string 

sql = ""

sql = "SELECT tblVisit.PatientID, tblPatients.Firstname, tblPatients.Lastname, " & vbcrlf
sql = sql & "DateDiff('yyyy', tblPatients.Birthday, Now()) AS Age, tblVisit.[Date Visit], " & vbcrlf
sql = sql & "tblVisit.Diagnosis, " & vbcrlf
sql = sql & "[First Name] & ' ' & [Last Name] AS [Prescriber Names] " & vbcrlf
sql = sql & "FROM " & vbcrlf
sql = sql & "tblPatients " & vbcrlf 
sql = sql & "INNER JOIN " & vbcrlf 
sql = sql & "( " & vbcrlf
sql = sql & "  tblDoctor " & vbcrlf 
sql = sql & "    INNER JOIN " & vbcrlf 
sql = sql & "  tblVisit " & vbcrlf 
sql = sql & "    ON " & vbcrlf 
sql = sql & "    tblDoctor.DoctorID = tblVisit.DoctorID " & vbcrlf 
sql = sql & ") ON tblPatients.PatientID = tblVisit.PatientID " & vbcrlf 
sql = sql & "WHERE " & vbcrlf  
sql = sql & "  [Firstname] = '" & Me.txtKeywords & "' " & vbcrlf

debug.print sql
 
Upvote 0
I have the same error message but with different issue. I was trying to write a simple search in a split form and access seems to be hanging up on the DoCmd.ApplyFilter task line in both cases Search or Clear.

1618908498996.png


Search button error:
1618908419603.png


Clear button error:
1618908277646.png


VBA Code:
Option Compare Database

Private Sub Command22_Click()
' Search button
Call Search
End Sub
Sub Search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.OrderDateFrom) Or IsNull(Me.OrderDateTo) Then
    MsgBox "Kérem adjon meg dátum sort", vbInformation, "Dátum sor Szükséges"
    Me.OrderDateFrom.SetFocus
Else
    strCriteria = "([DATUM] >= #" & Me.OrderDateFrom & "& And [DATUM] <= #" & Me.OrderDateTo & "#)"
        task = "select * from tEFI2021 where (" & strCriteria & ") order by [DATUM]"
    DoCmd.ApplyFilter task
    
End If

End Sub

Private Sub Command23_Click()
Dim task As String

    Me.OrderDateFrom = Null
    Me.OrderDateTo = ""
        task = "select * from tEFI2021 where (" & strCriteria & ") order by [DATUM]"
        DoCmd.ApplyFilter task
    
     
End Sub

Any input would be greatly appriciated. Got stuck. Thanks.
 
Upvote 0
For the first error, change the & to #.

For the second I can't read it or even guess at what it's about...
 
Upvote 0
For the first error, change the & to #.

For the second I can't read it or even guess at what it's about...
Thank you very much, just found it myself but now the following error message popped up

Run-time error '3000'
Reserved error (-3201) no message for this error (trying to translate it)

The Clear button still have the same error as before.

translation: Run-time Error 3705 - Syntax error (missing operator) in query expression

Hope this makes sense, thank you.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top