• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

IIF syntax, if null return all problem

MS Access 2003
I need help with my syntax.

IIF([district1] is null, like "*" or NZ([district]), [district1])

This is my criteria, [district1] is undefined and gives a msgbox(think thats the term, pops up asking for a value).

[district] is the field for which i am trying to establish a criteria.

What i want is to return all lines unfiltered when nothing is typed into the msgbox.
what i get is this "this expression is typed incorrectly or is toocomplex to be evaluated...."

can you help please.
I'd prefer not to do this in vb but can if needed
Thank you
  • 2
1 Solution
What do you want your query to do ?

Are [district] and [district1] 2 different fields and are they in the same table?
What is their datatype?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
WHERE [district] LIKE NZ([district1], '*')

Open in new window

norris900Author Commented:
[district] is a text field in my table

[district1] is a undefined criteria for the field [district]

select table1.district from table1 where (((table1.district)=[district1]));

I want the query to return all data when no criteria is entered by the user in the msgbox that pops up
norris900Author Commented:
Thanks was being a bit slow even for me on a monday morning :)
Perfect solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now