What to do about " signs

[rfear]

The following code works great and is designed to tell the difference between finding a record or not :-

If Not IsNull(DLookup("[Title]", "tblDataMIC", "[Title] = '" & (Me.ComboTitle) & "'")) Then
DoCmd.FindRecord RequestedRecord
Else
Me.Title = RequestedRecord
End If

Where (me.ComboTitle) is a combobox on a form that users can use for searches or for inputting a text value.

If the text value is something like ( MST21 ) it works fine.

It doesn't work if the text is ( MST21 1/2" and 2" ) i.e. when " means inches.

Any suggestions for a workaround ?

 

[JEA]

Try

"[Title] = '" & "(Me.[" & ComboTitle & "])" & "'"

 

[ajetrumpet]

The following code works great and is designed to tell the difference between finding a record or not :-

If Not IsNull(DLookup("[Title]", "tblDataMIC", "[Title] = '" & (Me.ComboTitle) & "'")) Then
            DoCmd.FindRecord RequestedRecord
        Else
            Me.Title = RequestedRecord
        End If

It doesn't work if the text is ( MST21 1/2" and 2" ) i.e. when " means inches.

Any suggestions for a workaround ?

Yes, maybe this...??

If Not IsNull(DLookup("[Title]", "tblDataMIC", '[Title] = "' & (Me.ComboTitle) & '"')) Then
            DoCmd.FindRecord RequestedRecord
        Else
            Me.Title = RequestedRecord
        End If

How about switch of the single and double quotes (to fool the program into not recognizing the double quotes in the combo value as the end of the criteria statement)???

I suppose you could run an update too, to get rid of all values that have the " symbol in it, and maybe replace it with "in."??

 

[rfear]

I'll give those a try, thanks.

I think though that I've nailed it down to the DoCmd.FindRecord statement - even without all the previous blurb this statement does not find a record containing speech marks.

The DLookup appears to be doing its job. Maybe I need to find the record using DLookup ( because it works ) and return a different uniqueID number field instead. That way I use a number instead of a text field.

 

[JEA]

Not trying to argue, just curious.

Would the square parenthesis not be enough to show that any quotation mark in 'ComboTitle' is part of the control name?

 

[ajetrumpet]

Would the square parenthesis not be enough to show that any quotation mark in 'ComboTitle' is part of the control name?

Actually, I don't think that is enough, strictly because the bracketing symbols are not meant to indicate a string. And, in this case, the quotation marks being questioned are indeed part of the entire string being referenced (which, is dangerous, and should be changed!! ).

There is a page in one of my Access books that lists all the reasons why bracketing symbols would be used. I'll have to look that up and get back to you on it (if you're interested). I read that a long time ago, but I forget the rules, and the reasons for them, and the consequences of breaking them! (I remember reading about what the program does if they are omitted when it's not permissible)

 

[rfear]

Thanks for your help yesterday. I have managed to work around the problem ( I think ).

If intnewrec = False Then
RequestedID = DLookup("[UniqueID]", "tblDataMIC", "[Title]='" & RequestedRecord & "'")
NullCheck = IsNull(RequestedID)
If NullCheck = True Then
Me.Title = RequestedRecord
Else
DoCmd.GoToControl "UniqueID"
DoCmd.FindRecord RequestedID
End If
Else
Me.Title = RequestedRecord
End If

Basically the same routine because the DLookup function appears to work with speech marks in the syntax but I search the form using the uniqueID of the record.

This covers 3 scenarios, the user entering a new record, the users searching for an existing record and the user editing an existing record and no need to prevent them from using speech marks.