By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,836 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

wildcard in QBE criteria causing data type mismatch error

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
this what you're after?
http://www.mvps.org/access/queries/qry0001.htm
Nov 12 '05 #2

P: n/a
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.


Nov 12 '05 #3

P: n/a
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!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.