Query "or" help needed.

[Rotor]

In Access 2003 I have a query where it asks the user for two parameter using [Enter Emp #] and [Enter Task Type] pop up. However, I need for the query to return all tasks for the Emp# if TaskType is left blank. I tried "In" in the or section of the query, but that returned all records for all emp#s....unless I used incorrectly.

Thxs for help.

[MMcCarthy]

Try something like this.

SELECT * FROM TableName
WHERE ([Emp#] =  [Enter Emp #]
AND [Task Type] = nz([Enter Task Type],"*")

Mary

[Rotor]

Try something like this.

SELECT * FROM TableName
WHERE ([Emp#] =  [Enter Emp #]
AND [Task Type] = nz([Enter Task Type],"*")

Mary

Thank you Mary, will give it a shot.

[NeoPa]

Neat idea but I'd use the LIKE in place of the '='.

SELECT * FROM TableName
WHERE ([Emp#]=[Enter Emp #])
  AND ([Task Type] Like Nz([Enter Task Type],'*')

[Rotor]

Neat idea but I'd use the LIKE in place of the '='.

SELECT * FROM TableName
WHERE ([Emp#]=[Enter Emp #])
  AND ([Task Type] Like Nz([Enter Task Type],'*')

Okay, that worked.

And you guys are going to kill me. Lets say I wanted to filter out certain task types when the field is left blank. The tasks I am interested in tracking are PT, ET, UT. So, with the this code the user can either input ET in [Enter Task Type] or leave bank and the code returns all task types for the user. That is good, but what would be perfect if it only returned ET, PT, UT for the user if [Enter Task Type] is left blank. I know I did not have this in my original question, but at the time it did not dawn on me.

Thank you for any extra help, you guys have been great.

[NeoPa]

This gets a little more oblique, but can be done. Try :

SELECT *
FROM [TableName]
WHERE ([Emp#]=[Enter Emp #])
  AND IIf(IsNull([Enter Task Type]),
          [Task Type] In('ET','PT','UT'),
          [Task Type]=[Enter Task Type])

This one you owe me a pint for :D
BTW I tested the concept and it worked perfectly.

[MMcCarthy]

This gets a little more oblique, but can be done. Try :

SELECT *
FROM [TableName]
WHERE ([Emp#]=[Enter Emp #])
  AND IIf(IsNull([Enter Task Type]),
          [Task Type] In('ET','PT','UT'),
          [Task Type]=[Enter Task Type])

This one you owe me a pint for :D
BTW I tested the concept and it worked perfectly.

Nice solution Ade!

[Rotor]

Well Neo, where can I send that pint?

Worked like a champ.

One more question :).

Is this coding something that can be learned by book or some sort of intermediate course on Access? Or is it code that one would have to take VBA courses to learn?

Just looking ahead.

[MMcCarthy]

Well Neo, where can I send that pint?

Worked like a champ.

One more question :).

Is this coding something that can be learned by book or some sort of intermediate course on Access? Or is it code that one would have to take VBA courses to learn?

Just looking ahead.

This is actually a combination of SQL code and Access functions. It is pretty standard as far as the SQL is concerned and the IIf function is a commonly used Access function.

The Access help file is a good place to learn about Access functions. SQL is a little more complicated.

[NeoPa]

Well Neo, where can I send that pint?

Worked like a champ.

One more question :).

Is this coding something that can be learned by book or some sort of intermediate course on Access? Or is it code that one would have to take VBA courses to learn?

Just looking ahead.

Mary just beat me into this one. I thought I'd get home before answering properly ;)
Actually the Access Help file also has a full set of help for Access SQL if you can find it.
Really, the SQL I posted was just basic stuff. It was the concept that is a bit out of the ordinary. I simply used the IIf() function to return boolean values rather than numeric or string values which are more commonly used. That is just an experience thing really, and a willingness to look for the less obvious solutions sometimes.
In this case (where IIf() is used within a SQL string), the IIf() function is actually SQL code rather than VBA. It has an important difference from the VBA version, which is that it will only evaluate the required leg of the function.

[Rotor]

Okay,

I tried the same code on another application where if user enters [Enter Emp #] and if [Enter Date dd/mm/yy] is left blank, displays records with current date.

This is what I did:

WHERE ([emp#]=[Enter Emp #]) 
AND Like Nz([Enter Date dd/mm/yy],'Date()')

So, that returns current date in report but no records are returned for employee or anyone else.

Obviously I did not do it right, so where did I miss the boat?

[MMcCarthy]

Okay,

I tried the same code on another application where if user enters [Enter Emp #] and if [Enter Date dd/mm/yy] is left blank, displays records with current date.

This is what I did:

WHERE ([emp#]=[Enter Emp #]) 
AND Like Nz([Enter Date dd/mm/yy],'Date()')

So, that returns current date in report but no records are returned for employee or anyone else.

Obviously I did not do it right, so where did I miss the boat?

Remove the single quotes from around Date(). It's a function, not a string.

Otherwise, very good attempt.

[Rotor]

Remove the single quotes from around Date(). It's a function, not a string.

Otherwise, very good attempt.

Tried that, getting syntax error. Here is actuial snippet of code from teh query:

Where (((QryjoinCompjobswithempquery.[Employee #])=[Enter Emp #] AND ((tblJobsComp.[Created Date Local])=Nz[Enter Date mm/dd/yy], Date()));

Where is the syntax error?

[MMcCarthy]

Where (((QryjoinCompjobswithempquery.[Employee #])=[Enter Emp #])
AND ((tblJobsComp.[Created Date Local])=Nz([Enter Date mm/dd/yy], Date())));

You didn't have an opening bracket after nz. Try the above.

Mary

[Rotor]

Where (((QryjoinCompjobswithempquery.[Employee #])=[Enter Emp #])
AND ((tblJobsComp.[Created Date Local])=Nz([Enter Date mm/dd/yy], Date())));

You didn't have an opening bracket after nz. Try the above.

Mary

Ding!

Thank you very much.

Great stuff, if it is any consolation I am off for the rest of the week, so I wont pester you guys any more...until then :).