query needs work

[ccoffelt]

When the query is run, I want the user to enter the shift. So, in the Shift field I have the following in the criteria box:

Like "*" & [Enter Shift] & "*"

However, some of the workorders do not have a shift(the box was left blank). The query as is doesn't pick those up. How can I modify it to pick up all shifts even though some are blank?

 

[Golom]

Shift Like "*" & [Enter  Shift] & "*" OR (Shift & '') = ''

 

[ccoffelt]

That kind of worked. When I entered a shift of 1 it pulled up workorders that had a shift of 1 in the box as well as some with blanks. However, when I did it for a shift of 2 it pulled in the 2's but also the same ones with the blanks again. I'm looking for all the 1's, 2's, 3's and then a total (including blank fields). Maybe I have gone about this query the wrong way.

 

[dhookom]

I don't like any parameter prompts faq701-6763. However if you really want to match Nulls, try"

Shift & "" Like "*" & [Enter  Shift] & "*"

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

what exactly are you trying to do? count the number of people on each shift? if so,

select shift, count(*) as counter from tablename
GROUP BY Shift

will return
[tt]
shift counter
7
1 15
2 25
3 18
[/tt]

if you need a total you can set a report footer to count the counter field and show a grand total....



Leslie

Have you met Hardy Heron?

 

[ccoffelt]

Thanks dhookom. That worked.