wildcard in QBE criteria causing data type mismatch error

[Laurel]

this is driving me crazy. i need to use a form control as a criteria
in a select query, and the control's value is set depending upon what
a user selects in an option group on the form. the query results
should return information on either a single employee or all
employees.

the problem that i have is that if i type in "*" (but without quotes)
in the QBE criteria, it works fine. however, if i use Iif() to
determine whether or not there is a value in the control and then use
* if there is not, i get a "data type mismatch" error. the empID field
is a numeric field.....so, that makes sense, but i don't understand
why * works if typed in, but not if you use a function or a form
control. i suspect it's because of a string / numeric data type
difference.

the question boils down to how do i use "*" or "like *", by using Iif
or a function (i've also created a function that returns * and that
doesn't work either) without explicitly typing it in?

thanks for any help you can offer.

[Pieter Linden]

this what you're after?
http://www.mvps.org/access/queries/qry0001.htm

[Pink Panther]

See

http://www.mvps.org/access/queries/qry0001.htm

(Q) I'm using a control on a form as a parameter for my query. I've
defined the criteria in the query as

Forms!myForm!myControl

How do I set this up so that if the field is left blank, all records are
returned by the query?

(A) Change the criteria in the query to

Forms!myForm!myContrl OR forms!myForm!myControl Is Null

I also use

nz([parameter],[tablename].[fieldname])

so take your pick.

Peter


lk****@cox.net (Laurel) wrote in news:28aa456e.0310131706.1e334627
@posting.google.com:

this is driving me crazy. i need to use a form control as a criteria
in a select query, and the control's value is set depending upon what
a user selects in an option group on the form. the query results
should return information on either a single employee or all
employees.

the problem that i have is that if i type in "*" (but without quotes)
in the QBE criteria, it works fine. however, if i use Iif() to
determine whether or not there is a value in the control and then use
* if there is not, i get a "data type mismatch" error. the empID field
is a numeric field.....so, that makes sense, but i don't understand
why * works if typed in, but not if you use a function or a form
control. i suspect it's because of a string / numeric data type
difference.

the question boils down to how do i use "*" or "like *", by using Iif
or a function (i've also created a function that returns * and that
doesn't work either) without explicitly typing it in?

thanks for any help you can offer.

[Laurel]

thanks to both of you!

i used

Forms!myForm!myContrl OR forms!myForm!myControl Is Null

and it worked like a charm. i'm new to this. obviously. thanks again!