"Type Mismatch" error with search macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following macro attached to a textbox used to find records in a
database. It's used as a condition to display a msgbox if there are no
records found. The condition reads:

[Last]<>([txtSearch] OR "*" &[txtSearch] OR "*" &[txtSearch]&"*" OR
[txtSearch]& "*")

The original condition simply read [Last]<>[txtSearch], but its addition
seemed to disable the use of the wildcard character, so I added the asterisks
to the macro. Now the wildcard character is working, but it produces a "Type
Mismatch" msgbox whenever it's used. This isn't causing any serious
problems, but I'm wondering how to get rid of it or if the statement could be
written better to enable wildcards.
 
Hbear,

I can't really understand what you are getting at here, with the
[txtSearch] OR "*" repeated 4 times. But in any case, something like
this will not relate to "if there are no records found". The [Last] in
your expression can only ever relate to one record, presumably the
current record on your form. If you want to assess no matching records,
you will need to use a domain function. Perhaps the equivalent of this...
DCount("*","YourTable","[Last]='" & [txtSearch] & "'")=0

By the way, as an aside, Last is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control.
 
Thanks, Steve. I'll research domain functions and see if I can solve the
problem that way. Thanks also for the pointer on Last. Every bit of
knowledge is appreciated.


Steve Schapel said:
Hbear,

I can't really understand what you are getting at here, with the
[txtSearch] OR "*" repeated 4 times. But in any case, something like
this will not relate to "if there are no records found". The [Last] in
your expression can only ever relate to one record, presumably the
current record on your form. If you want to assess no matching records,
you will need to use a domain function. Perhaps the equivalent of this...
DCount("*","YourTable","[Last]='" & [txtSearch] & "'")=0

By the way, as an aside, Last is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control.

--
Steve Schapel, Microsoft Access MVP
I have the following macro attached to a textbox used to find records in a
database. It's used as a condition to display a msgbox if there are no
records found. The condition reads:

[Last]<>([txtSearch] OR "*" &[txtSearch] OR "*" &[txtSearch]&"*" OR
[txtSearch]& "*")

The original condition simply read [Last]<>[txtSearch], but its addition
seemed to disable the use of the wildcard character, so I added the asterisks
to the macro. Now the wildcard character is working, but it produces a "Type
Mismatch" msgbox whenever it's used. This isn't causing any serious
problems, but I'm wondering how to get rid of it or if the statement could be
written better to enable wildcards.
Click to expand...
Click to expand...
 
Back
Top