I am still struggling with trying to match a user supplied string with
strings in a field of a table, where spaces [probably eventually a
broader whitespace] are to be ignored.
Arno suggested that I use the Dlookup function, but I apparently can't
get its syntax right.
Access gives me "Run-time error '2001' You canceled the previous operation"
url: http://www.fontstuff.com/access/acctut15.htm,
a set of 'Access Tips' has this to say about this error:
"The next example must get the prize for the most confusing error
message that Access has to offer!
<snip>
....this message also appears when there is a different kind of error,
when you attempt to use an SQL statement from VBA in which the data
type of a WHERE clause's criteria does not match the data type of the
corresponding field. For example you might have a date field and
supply a text data type: WHERE tblStaff.BirthDate='Tuesday0#."
I am hopelessly confused in the jungle of single quotes, double
quotes, parantheses, square brackets...
Here are what I hope are the offending lines of code:
Dim A_ID As Integer
Dim DataVal As String
' ---DataVal = User choice w/ blanks removed---
DataVal = Replace(cboChooser.Text, Blank, Q2)
'^^^combobox text
' ---This Doesn't Work: it triggers the 'cancel'---
A_ID = DLookup("[AA_ID]", "A0toD", "Replace(" & _
[Name] & ", Blank, Q2) = '" _& DataVal & "'")
' ----But this does Work---
A_ID = DLookup("[AA_ID]", "A0toD", "[Name] = '" _
& DataVal & "'")
...so I hope that my error is somewhere in trying to insert the
Replace function into the Dlookup function.
What have I done?
--thelma