My Dlookup is 'canceled'

[Thelma Lubkin]

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.BirthD ate='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(cboChoo ser.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

[Arno R]

Thelma, why don't you post back on the original thread ?

I told you that you could use DLookup, but I also told you this:
"The NotInList_event only occurs when LimitToList is set to True ..."
So you better DON'T use this event for what you need.

I will give you more info on the original thread, so others can also see what this is about.

Arno R
"Thelma Lubkin" <th****@alpha2. csd.uwm.edu> schreef in bericht news:d7******** **@uwm.edu...

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.BirthD ate='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(cboChoo ser.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