Queries using Where Like / CheckBox

  • Thread starter Thread starter sebarpin
  • Start date Start date
S

sebarpin

Hi,

I need help to built a queries into MS Access.. The queries i want to
built should be someting like this...

SELECT mtblRequest.refNum, mtblRequest.Date, mtblRequest.CSRName,
mtblRequest.Office, mtblRequest.TL, mtblRequest.ATL,
mtblRequest.Skillset, mtblRequest.ReqDate1, mtblRequest.VolYes1,
mtblRequest.VolHrs1, mtblRequest.VolStatus1, mtblRequest.AddYes1,
mtblRequest.AddBefAft1, mtblRequest.AddStatus1, mtblRequest.OTYes1,
mtblRequest.OTBefAft1, mtblRequest.OTStatus1, mtblRequest.ReqDate2,
mtblRequest.VolYes2, mtblRequest.VolHrs2, mtblRequest.VolStatus2,
mtblRequest.AddYes2, mtblRequest.AddBefAft2, mtblRequest.AddStatus2,
mtblRequest.OTYes2, mtblRequest.OTBefAft2, mtblRequest.OTStatus2,
mtblRequest.ReqDate3, mtblRequest.VolYes3, mtblRequest.VolHrs3,
mtblRequest.VolStatus3, mtblRequest.AddYes3, mtblRequest.AddBefAft3,
mtblRequest.AddStatus3, mtblRequest.OTYes3, mtblRequest.OTBefAft3,
mtblRequest.OTStatus3, mtblRequest.Comments
FROM mtblRequest
WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) OR
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))
ORDER BY mtblRequest.Date;

If I run it and select MED for the skillset query then I have all
recordset with MED as mtblRequest.Skillset and mtblRequest.AddYes
value true, but I also have other kind of skillset... I want a queries
that will give me the option to select the mtblRequest.Skillset name
and the result should be only the recordset with this skillset and
with mtblRequest.AddYes value true...

Someone can help me please...

Thanks
 
Hi,

I need help to built a queries into MS Access.. The queries i want to
built should be someting like this...

SELECT mtblRequest.refNum, mtblRequest.Date, mtblRequest.CSRName,
mtblRequest.Office, mtblRequest.TL, mtblRequest.ATL,
mtblRequest.Skillset, mtblRequest.ReqDate1, mtblRequest.VolYes1,
mtblRequest.VolHrs1, mtblRequest.VolStatus1, mtblRequest.AddYes1,
mtblRequest.AddBefAft1, mtblRequest.AddStatus1, mtblRequest.OTYes1,
mtblRequest.OTBefAft1, mtblRequest.OTStatus1, mtblRequest.ReqDate2,
mtblRequest.VolYes2, mtblRequest.VolHrs2, mtblRequest.VolStatus2,
mtblRequest.AddYes2, mtblRequest.AddBefAft2, mtblRequest.AddStatus2,
mtblRequest.OTYes2, mtblRequest.OTBefAft2, mtblRequest.OTStatus2,
mtblRequest.ReqDate3, mtblRequest.VolYes3, mtblRequest.VolHrs3,
mtblRequest.VolStatus3, mtblRequest.AddYes3, mtblRequest.AddBefAft3,
mtblRequest.AddStatus3, mtblRequest.OTYes3, mtblRequest.OTBefAft3,
mtblRequest.OTStatus3, mtblRequest.Comments
FROM mtblRequest
WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) OR
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))
ORDER BY mtblRequest.Date;

If I run it and select MED for the skillset query then I have all
recordset with MED as mtblRequest.Skillset and mtblRequest.AddYes
value true, but I also have other kind of skillset... I want a queries
that will give me the option to select the mtblRequest.Skillset name
and the result should be only the recordset with this skillset and
with mtblRequest.AddYes value true...

Someone can help me please...

Thanks
Click to expand...

What are you trying to accomplish? According to the WHERE clause, you're
selecting records either with a skillset like *MED* or where the AddYes
fields are all True. If you want only records with a skillset like *MED* and
the AddYes fields =True, change your OR to AND:

WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) AND
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))

Carl Rapson
 
Hi,
Click to expand...
I need help to built a queries into MS Access.. The queries i want to
built should be someting like this...
Click to expand...
SELECT mtblRequest.refNum, mtblRequest.Date, mtblRequest.CSRName,
mtblRequest.Office, mtblRequest.TL, mtblRequest.ATL,
mtblRequest.Skillset, mtblRequest.ReqDate1, mtblRequest.VolYes1,
mtblRequest.VolHrs1, mtblRequest.VolStatus1, mtblRequest.AddYes1,
mtblRequest.AddBefAft1, mtblRequest.AddStatus1, mtblRequest.OTYes1,
mtblRequest.OTBefAft1, mtblRequest.OTStatus1, mtblRequest.ReqDate2,
mtblRequest.VolYes2, mtblRequest.VolHrs2, mtblRequest.VolStatus2,
mtblRequest.AddYes2, mtblRequest.AddBefAft2, mtblRequest.AddStatus2,
mtblRequest.OTYes2, mtblRequest.OTBefAft2, mtblRequest.OTStatus2,
mtblRequest.ReqDate3, mtblRequest.VolYes3, mtblRequest.VolHrs3,
mtblRequest.VolStatus3, mtblRequest.AddYes3, mtblRequest.AddBefAft3,
mtblRequest.AddStatus3, mtblRequest.OTYes3, mtblRequest.OTBefAft3,
mtblRequest.OTStatus3, mtblRequest.Comments
FROM mtblRequest
WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) OR
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))
ORDER BY mtblRequest.Date;
Click to expand...
If I run it and select MED for the skillset query then I have all
recordset with MED as mtblRequest.Skillset and mtblRequest.AddYes
value true, but I also have other kind of skillset... I want a queries
that will give me the option to select the mtblRequest.Skillset name
and the result should be only the recordset with this skillset and
with mtblRequest.AddYes value true...
Click to expand...
Someone can help me please...
Click to expand...
Thanks
Click to expand...

What are you trying to accomplish? According to the WHERE clause, you're
selecting records either with a skillset like *MED* or where the AddYes
fields are all True. If you want only records with a skillset like *MED* and
the AddYes fields =True, change your OR to AND:

WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) AND
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))

Carl Rapson- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -
Click to expand...

Well I want to be able to select the skillset name and see every
skillset MED who have one of the 3 checkbox True or all of the 3
checkbox true or 2 of the 3 checkbox true...
 
WHERE mtblRequest.Skillset Like "*" & [Enter Skillset] & "*" AND
(mtblRequest.AddYes1=True Or mtblRequest.AddYes2=True Or
mtblRequest.AddYes3=True)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,
Click to expand...
I need help to built a queries into MS Access.. The queries i want to
built should be someting like this...
Click to expand...
SELECT mtblRequest.refNum, mtblRequest.Date, mtblRequest.CSRName,
mtblRequest.Office, mtblRequest.TL, mtblRequest.ATL,
mtblRequest.Skillset, mtblRequest.ReqDate1, mtblRequest.VolYes1,
mtblRequest.VolHrs1, mtblRequest.VolStatus1, mtblRequest.AddYes1,
mtblRequest.AddBefAft1, mtblRequest.AddStatus1, mtblRequest.OTYes1,
mtblRequest.OTBefAft1, mtblRequest.OTStatus1, mtblRequest.ReqDate2,
mtblRequest.VolYes2, mtblRequest.VolHrs2, mtblRequest.VolStatus2,
mtblRequest.AddYes2, mtblRequest.AddBefAft2, mtblRequest.AddStatus2,
mtblRequest.OTYes2, mtblRequest.OTBefAft2, mtblRequest.OTStatus2,
mtblRequest.ReqDate3, mtblRequest.VolYes3, mtblRequest.VolHrs3,
mtblRequest.VolStatus3, mtblRequest.AddYes3, mtblRequest.AddBefAft3,
mtblRequest.AddStatus3, mtblRequest.OTYes3, mtblRequest.OTBefAft3,
mtblRequest.OTStatus3, mtblRequest.Comments
FROM mtblRequest
WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) OR
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))
ORDER BY mtblRequest.Date;
Click to expand...
If I run it and select MED for the skillset query then I have all
recordset with MED as mtblRequest.Skillset and mtblRequest.AddYes
value true, but I also have other kind of skillset... I want a queries
that will give me the option to select the mtblRequest.Skillset name
and the result should be only the recordset with this skillset and
with mtblRequest.AddYes value true...
Click to expand...
Someone can help me please...
Click to expand...
Thanks
Click to expand...

What are you trying to accomplish? According to the WHERE clause, you're
selecting records either with a skillset like *MED* or where the AddYes
fields are all True. If you want only records with a skillset like *MED*
and
the AddYes fields =True, change your OR to AND:

WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) AND
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))

Carl Rapson- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -
Click to expand...

Well I want to be able to select the skillset name and see every
skillset MED who have one of the 3 checkbox True or all of the 3
checkbox true or 2 of the 3 checkbox true...
 
WHERE mtblRequest.Skillset Like "*" & [Enter Skillset] & "*" AND
(mtblRequest.AddYes1=True Or mtblRequest.AddYes2=True Or
mtblRequest.AddYes3=True)

This doesn't works... When I run this SQL I got some record set with
the wrong Skillset name but with the checkbox True and I also have
some recordset with the right skillset name but some of the recordset
have the all the 3 chekcbox False.. wich I dont want...
 
Sorry my mistake...

when I put this SQL script into access

WHERE mtblRequest.Skillset Like "*" & [Enter Skillset] & "*" AND
(mtblRequest.AddYes1=True Or mtblRequest.AddYes2=True Or
mtblRequest.AddYes3=True)

Then save the queries access change the script for:

WHERE (((mtblRequest.Skillset) Like "*" & [Enter Skillset] & "*")) OR
(((mtblRequest.AddYes1)=True) AND ((mtblRequest.AddYes2)=True) AND
((mtblRequest.AddYes3)=True))

Thanks
 
Are you using the query grid to enter this? If so, you will need to enter
your criteria on three criteria lines. The (number) in the following
indicates three different criteria rows.

Field: SkillSet
Criteria (1): Like "*" & [Enter Skillset] & "*"
Field: AddYes1
Criteria(1): True

Field: SkillSet
Criteria (2): Like "*" & [Enter Skillset] & "*"
Field: AddYes2
Criteria(2): True

Field: SkillSet
Criteria (3): Like "*" & [Enter Skillset] & "*"
Field: AddYes3
Criteria(3): True

It sounds as if you are not getting the parentheses correct.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top