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 :).

[MMcCarthy]

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 :).

No problem. Glad you've got it working.

Mary

[NeoPa]

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 :).

Until then, then :) (What did I just let myself in for :D)

[Rotor]

Until then, then :) (What did I just let myself in for :D)

Hiya Neo,

One more question on this subject. I would like to take this code a little bit further and maybe you or mccarthy can tell me how I can make this work if possible:

WHERE (((tblEmployees.[Employee Number])=[Enter Emp #])
  And IIf(IsNull([Enter Task Type]),[Task Type] In ('Borescope','ET','PT','UT','RT'),[Task Type]=[Enter Task Type])
  And ((tblAcftJobsComp.[Created Date Local])>=[From Dte mm/dd/yy]));

To the above code I would like to add

=Nz([Enter Date mm/dd/yy], DateAdd("d", 30, Now))))

I am not sure how to work it in with the >= comparison operator. I am asking the user from what date he wants to display the records. However, based on previous ideas, if he leaves the field blank, I would like the query to return the past 30 days.

[NeoPa]

I can't get to this now as it's been hectic all day.
It looks like you're on the right lines - I'll have a look later at finalising the details for you :)

[NeoPa]

Try using :

...>Nz([Enter Date mm/dd/yy], DateAdd("d", -30, Date()))

Let me know if that works. If not then post your current SQL code but try to lay it out so that I can read it without copying it to a text-editor first.

[Rotor]

Try using :

...>Nz([Enter Date mm/dd/yy], DateAdd("d", -30, Date()))

Let me know if that works. If not then post your current SQL code but try to lay it out so that I can read it without copying it to a text-editor first.

Good Morning Neopa,

I tried the above and I am getting a syntax error. Here is the SQL:

And ((tblAcftJobsComp.[Created Date Local])
>Nz([Enter Date mm/dd/yy], DateAdd("d", -30, Date()));
 

Hope I layed it out okay for ya, wasnt sure what you meant, so I assumed to mean to break it up in the window.

[NeoPa]

It's hard without the full SQL (Well laid out by the way - perfectly readable), but immediately obvious is that you've introduced an unclosed opening parenthesis '('. Try balancing them all up properly first, then, if that won't work, post the full SQL & I'll check it for you. If there's anything more to the error message that would be helpful too.

[Rotor]

It's hard without the full SQL (Well laid out by the way - perfectly readable), but immediately obvious is that you've introduced an unclosed opening parenthesis '('. Try balancing them all up properly first, then, if that won't work, post the full SQL & I'll check it for you. If there's anything more to the error message that would be helpful too.

Okay, parenthesis fixed. Thxs.

Now to deal with that other thread :). I am going to address the field naming issue foist, then get back to you with the rest.

[NeoPa]

Sounds fine.
Well done for fixing the parentheses :) Glad to hear it fixed the problem.