im kinda new at this stuff so you might have to bare with me..

i have a database and im trying to create a query which will do the following...

here is some fields which will be in my query..

Team of Referrer - Drop Down Box in table
16/17 Yr Old - Yes/No Box in table
Insurance - Yes/No Box in table
Newletter - Yes/No Box in table

so i need to run a query which will search a certain referrer but for people who are only have a certain Yes/No Box ticked..

e.g. show all Carers (Team of Referrer) who are recieving Newsletter

i know that i need [Enter Referrer] for my Team of Referrer but im not sure how to set up so i can enter which Yes/No i am looking for..

if you need more explaination on what i mean just ask and ill try to help more..

Thanks

DreamOn

Welcome to UtterAccess.

One approach would be to have them key in the name of the field they want to check, and then use something like the following as your Where clause:

WHERE (([16/17 Yr Old] = True) AND ([Enter Detail] = "16/17"))
OR (([Insurance] = True) AND ([Enter Detail] = "Insurance"))
OR (([Newsletter] = True) AND ([Enter Detail] = "Newsletter"))

To do this via the graphical query builder, you'd put

True AND ([Enter Detail] = "16/17")

as the criteria under the [16/17 Yr Old] field,

True AND ([Enter Detail] = "Insurance")

as the criteria under the [Insurance] field, and so on. Just make sure that you put exactly the same thing for [Enter Detail] in each place, or you'll get multiple prompts.

Thanks for the welcome, and thanks for the help.

im not sure where i have to put

WHERE (([16/17 Yr Old] = True) AND ([Enter Detail] = "16/17"))
OR (([Insurance] = True) AND ([Enter Detail] = "Insurance"))
OR (([Newsletter] = True) AND ([Enter Detail] = "Newsletter"))

but the rest of it i got.

ops nvm i got it now thanks for the help.

Edited by: DreamOn on Fri Mar 28 10:23:31 EDT 2008.

ok i got another problem now

when i use [Enter Referrer] and the [Enter Detail]

it comes up with everything for the Referrer..

e.g.

i search for Carer and 16/17 Yr Old Only, buy its shows everyone who is with the Carer.

any idea how to fix this one?

What's the SQL for your query? If you're not familiar with dealing directly with the SQL that Access generates, open your query in Design view, then select SQL view from the View menu.

this is my SQL atm..


SELECT ServiceUser.Title, ServiceUser.[First Name], ServiceUser.[Last Name], ServiceUser.[Team of Referrer], ServiceUser.[New Referral], ServiceUser.Newsletter, ServiceUser.Payroll, ServiceUser.[Returns Visit], ServiceUser.[Recruitment Support], ServiceUser.[Receipt of DP], ServiceUser.[Working Towards DP], ServiceUser.[Support Service Completing Returns], ServiceUser.[Specific Financial Support], ServiceUser.[No Longer Using DP], ServiceUser.[Fish Insurance], ServiceUser.HCD, ServiceUser.ICF, ServiceUser.[Peer Support], ServiceUser.Consultation
FROM ServiceUser
WHERE (((ServiceUser.[New Referral])=True) AND (([Enter Detail])="New Referral")) OR (((ServiceUser.Newsletter)=True) AND (([Enter Detail])="Newsletter")) OR (((ServiceUser.Payroll)=True) AND (([Enter Detail])="Payroll")) OR (((ServiceUser.[Returns Visit])=True) AND (([Enter Detail])="Returns Visit")) OR (((ServiceUser.[Recruitment Support])=True) AND (([Enter Detail])="Recruitment Support")) OR (((ServiceUser.[Receipt of DP])=True) AND (([Enter Detail])="Receipt of DP")) OR (((ServiceUser.[Working Towards DP])=True) AND (([Enter Detail])="Working Towards DP")) OR (((ServiceUser.[Support Service Completing Returns])=True) AND (([Enter Detail])="Support Service Completing Returns")) OR (((ServiceUser.[Specific Financial Support])=True) AND (([Enter Detail])="Specific Financial Support")) OR (((ServiceUser.[No Longer Using DP])=True) AND (([Enter Detail])="No Longer Using DP")) OR (((ServiceUser.[Fish Insurance])=True) AND (([Enter Detail])="Fish Insurance")) OR (((ServiceUser.HCD)=True) AND (([Enter Detail])="HCD")) OR (((ServiceUser.ICF)=True) AND (([Enter Detail])="ICF")) OR (((ServiceUser.[Peer Support])=True) AND (([Enter Detail])="Peer Support")) OR (((ServiceUser.Consultation)=True) AND (([Enter Detail])="Consultation")) OR (((ServiceUser.[Team of Referrer])=[Enter Referrer]));

Try changing

OR (((ServiceUser.[Team of Referrer])=[Enter Referrer]));

to

AND (((ServiceUser.[Team of Referrer])=[Enter Referrer]));

To be honest, I was a little lazy and didn't check that your parentheses are correct. You'll probably need parentheses around all of the (((ServiceUser.[New Referral])=True) AND (([Enter Detail])="New Referral")) OR (((ServiceUser.Newsletter)=True) AND (([Enter Detail])="Newsletter")) etc statements as well. (In other words, one more opening parenthesis after WHERE, and one more closing parenthesis before AND (((ServiceUser.[Team of Referrer])=[Enter Referrer]));