CODEWITHSUNDEEP
ms-accessvba
Public Health Inspectorate
Public Health Inspectorate

Reputation: 13

Run Time Error 3075 (Missing Syntax)

If DCount("Business_Name", "Food Permisestbl", "[Business_Name] = '" & Me.Business_Name & "'") > 0 Then...

The code works fine unless an individual has an apostrophe in their name (eg. D'Abero). Then the Run Time Error 3075 (Missing Syntax) occurs!

How can I revise this to prevent the error when the name includes an apostrophe?

Upvotes: 1

Views: 420

Answers (1)

HansUp
HansUp

Reputation: 97101

Either use double quotes instead of single quotes before and after Me.Business_Name or substitute 2 single quotes within Me.Business_Name for each single quote it contains.

If DCount("Business_Name", "Food Permisestbl", "[Business_Name] = """ & Me.Business_Name.Value & """") > 0 Then

If DCount("Business_Name", "Food Permisestbl", "[Business_Name] = '" & Replace(Me.Business_Name.Value, "'", "''") & "'") > 0 Then

You might find troubleshooting easier if you use a string variable for the third Dcount argument, Criteria.

Dim strCriteria As String
' then one of these next 2 lines ...
strCriteria = "[Business_Name] = """ & Me.Business_Name.Value & """"
'strCriteria = "[Business_Name] = '" & Replace(Me.Business_Name.Value, "'", "''") & "'"
Debug.Print strCriteria '<- inspect in Immediate window; Ctrl+g will take you there
' and finally ...
If DCount("Business_Name", "Food Permisestbl", strCriteria) > 0 Then

Upvotes: 1

Related Questions