"Type Mismatch" error with search macro

H

hbear

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.
 
S

Steve Schapel

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.
 
H

hbear

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top