Syntax Error In Query Expression

[mtagliaferri]

I have copied a fully functional code from another form in the DB for selecting a specific record when double clicking on a field, but I receive this error:
"Syntax Error (missing operator)In Query Expression '[HotelLoyaltyProgram]=CLUB CARLSON'

On Error GoTo HotelLoyaltyProgramme_DblClick_Err

    DoCmd.OpenForm "frmHotelLoyaltyProgramme", acNormal, "", "[HotelLoyaltyProgramme]=" & HotelLoyaltyProgramme, , acDialog
    
HotelLoyaltyProgramme_DblClick_Exit:
    Exit Sub

HotelLoyaltyProgramme_DblClick_Err:
    MsgBox Error$
    Resume HotelLoyaltyProgramme_DblClick_Exit

 

[Minty]

You need to put the criteria in single quotes if it is a string.

 DoCmd.OpenForm "frmHotelLoyaltyProgramme", acNormal, "", "[HotelLoyaltyProgramme]=[COLOR="Red"]'[/COLOR]" & HotelLoyaltyProgramme [COLOR="red"]& "'"[/COLOR], , acDialog

 

[Cronk]

Because the field is text, you need to enclose the data in quotes. Try
DoCmd.OpenForm "frmHotelLoyaltyProgramme", acNormal, "", "[HotelLoyaltyProgramme]='" & HotelLoyaltyProgramme & "'", , acDialog

or

DoCmd.OpenForm "frmHotelLoyaltyProgramme", acNormal, "", "[HotelLoyaltyProgramme]=" & chr(34) & HotelLoyaltyProgramme & chr(34) , , acDialog

 

[mtagliaferri]

Thanks Minty & Cronk!
I do this as a hobby and have very limited knowledge could you please guide me through the criteria of single quote when is a string? and also can you tell me the chr(34) meaning? curious curious curious...
Reason for asking is that I have two other forms with a Add Record command, one works...

 DoCmd.OpenForm "frmCrewMember", acNormal, "", "", acAdd
    Forms!frmCrewMember.CmdEdit.Visible = False
    Forms!frmCrewMember.CmdAddPhoto.Visible = True
    Forms!frmCrewMember.CmdDeletePhoto.Visible = True

and this one does not...

DoCmd.OpenForm "frmFleet", acNormal, "", "", acAdd
Forms!frmFleet.CmdEdit.Visible = False
Forms!frmFleet.CmdAddPhoto.Visible = True
Forms!frmFleet.CmdDeletePhoto.Visible = True

:banghead::banghead:

 

[The_Doc_Man]

Chr$(34) is using the character function to translate ASCII code number 34 into its equivalent symbol, which happens to be a double-quote (") character.

When you have a text item to be added literally to a string, you need to understand that Access recognizes two kinds of quotes - double-quote (the traditional string delimiter) and single-quote or apostrophe (when syntax gets in the way).

Let's say you wanted to count the number of records in your table for people whose last name is EXACTLY "Smith" - and you wanted to use DCount to do it. Well, the problem is that DCount uses double quotes for its input arguments because they are strings.

TheNum = DCount( "[LastName]","Persons","[LastName]=Smith")

The above fails because it looks for a variable named Smith in some internal context and of course there isn't one.

TheNum = DCount( "[LastName]","Persons","[LastName]="Smith"")

This one fails because the quote following the equals-sign terminates the third argument, so the DCount doesn't really know what it is trying to count.

TheNum = DCount( "[LastName]","Persons","[LastName]='Smith'")

This one would work pretty well because this is an alternative use of single-quote when a double-quote has a different meaning in context.

TheName = "Smith"
TheNum = DCount( "[LastName]","Persons","[LastName]='" & TheName & "'")

This would work equally well and would substitute the contents of the variable in question, setting it apart using single quotes.

 

[Cronk]

And to add a little more, anyone who has been working with text data, particularly family names, sooner or later a name will crop up with a single quote eg O'Riley, but it could be with other data such as say, a business names eg Tom's Gardening

This will cause a run time error when encountered.

I prefer to use chr(34) to insert double-quotes around all text variables unless it is data from lookup table where I can be certain it will never contain a single quote.

Others will use a triple double-quote, ie
debug.print """
gives
""