criteria in query

[gscma1]

I have a query and i want the report to print out details of employee cash required that week.

These have been input in tblHours.

However i have set the query up with 'True' in the criteria for each of the Cash fields. so as not to print off all employees including those without cash.

these are my fields:

EmpId
Week No
Cash
Petrol Receipts
Other Receipts
Miscellaneous

i understand that i cannot put true as the criteria for all four fields as the query will only look for those people with all four fields filled in however i would like the report to print everyone with a total other than £0.00 in any of those four fields.

any help is much appreciated.

thanks

 

[ChopinFan]

"I would like the report to print everyone with a total other than £0.00 in any of those four fields."

It sounds like you're just looking for a where clause that says WHERE field1 <>0 OR field2 <>0 OR field3 <>0 etc. Am I understanding your need correctly?

 

[gscma1]

sorry i'm unclear.

I want the report to print a list of employees who are owed for Cash, Petrol Receipts, Other Receipts and Miscellaneous.

i do not want the report to show any employee with £0.00 in those fields.

my query is set up as follows:

Week No EmpId Cash PetrolReceipts OtherReceipts Misc

Parameter Query on week no [Enter Week No:]

obviously some employees could have just a petrol receipt and others could have all four fields. but i'm unsure how to retrieve just the information of those with money within the fields.

i tried the "WHERE field1<>0" but i'm unsure if i'm inputting this into the correct place.

any help appreciated. thanks

 

[lespaul]

switch to the SQL view and paste:

SELECT * FROM tblHours WHERE Cash <> 0 OR [Petrol Receipts] <> 0 OR [Other Receipts] <> 0 OR Miscellaneous <> 0

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

My query now looks like this:

SELECT *
FROM tblHours
WHERE Cash <> 0 OR [Petrol Receipts] <> 0 OR [Other Receipts] <> 0 OR Miscellaneous <> 0
(((tblHours.[Week No])=[Enter Week No:]));

The report brings up an error message saying there is an invalid usage of "," "!"

sorry this is really not my thing so i'm struggling quite a bit.

thanks

 

[Golom]

SELECT *
FROM tblHours
WHERE [red][b]([/b][/red]Cash <> 0 OR [Petrol Receipts] <> 0 OR [Other Receipts] <> 0 OR Miscellaneous <> 0[red][b])[/b][/red]
  [red]AND[/red] tblHours.[Week No]=[Enter Week No:]

 

[lespaul]

you're missing the AND (I'd also add these parens):

SELECT *
FROM tblHours
WHERE (Cash <> 0 OR [Petrol Receipts] <> 0 OR [Other Receipts] <> 0 OR Miscellaneous <> 0) AND tblHours.[Week No]=[Enter Week No:];


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

Consider normalizing your table structure so you are not storing data in field names. If you can't change your table structure, use a union query like:

SELECT EmpId, [Week No],Cash, "Cash" as CashType
FROM tblHours
WHERE Nz(Cash,0) <>0
UNION ALL
SELECT EmpId, [Week No],[Petrol Receipts], "Petrol Receipts"
FROM tblHours
WHERE Nz([Petrol Receipts],0) <> 0
UNION ALL
SELECT EmpId, [Week No],[Other Receipts], "Other Receipts"
FROM tblHours
WHERE Nz([Other Receipts],0) <> 0
UNION ALL
SELECT EmpId, [Week No],[Miscellaneous], "Miscellaneous"
FROM tblHours
WHERE Nz([Miscellaneous],0) <> 0;

You can then use this query as the base for a report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]