Using Check boxes

  • Thread starter mohd21uk via AccessMonster.com
  • Start date
M

mohd21uk via AccessMonster.com

I have a form with checkboxes used to select certain values. These checkboxes
allow users to query data from a table called CATS. A sample of this table is
provided below:

WBS Code User Name MaxOfDate
GB2-E0005.10 EGAN-SHEPHERD, CLIVE 09/08/2005
GB2-E0020.10 BANKS, TIM 12/05/2005
GB2-E0020.10 PARRIS, PHIL 12/23/2005
GB2-E0020.10 WHITMARSH, KEVIN 12/14/2005
GB2-E0020.25 ALDERTON, MALCOLM 09/05/2005
GB2-E0020.35 REGAN, DAVID 12/15/2005
GB2-E0020.40 BAKER, ANDY 09/26/2005
GB2-E0020.40 BOONE, CHRISTOPHER 09/20/2005
GB2-E0020.40 BURDON, JOHN 09/26/2005
GB2-E0020.40 CREAN, PAUL 09/30/2005
GB2-E0020.40 CUTLAND, TIM 09/30/2005
GB2-E0020.40 GILBERT, PAUL 09/30/2005
GB2-E0020.40 GREEN, DAVID 12/21/2005
GB2-E0020.40 NEALE, BRIAN 07/27/2005
GB2-E0020.40 OVER, ROBERT 09/28/2005
GB2-E0020.40 SIEUW, DIRK 09/30/2005
GB2-E0020.40 YOUART, PETER 10/14/2005
GB2-E0020.45 NEILSON, GORDON 12/07/2005
GB2-E0020.50 ALDERTON, MALCOLM 12/09/2005
GB2-E0020.55 CLOUGH, MICHAEL 01/27/2006
GB2-E0020.55 HUSSAIN, HABID 10/14/2005
GB2-E0020.55 MCCALDON, SEAN 03/03/2006
GB2-E0020.60 BANKS, TIM 09/11/2005
GB2-E0020.60 PARRIS, PHIL 10/11/2005
GB2-E0026.10 BANKS, MARTIN 10/17/2005
GB2-E0026.10 CLARKE, PHILIP 04/07/2005
GB2-E0026.10 DAVIES, GARETH 10/06/2005
GB2-E0026.10 GODFREY, STEVE 06/15/2005
GB2-E0026.10 HARRISON, DAVE 12/05/2005
GB2-E0026.10 HEEKS, FRED 12/05/2005
GB2-E0026.10 JEPSON, ANDY 12/05/2005
GB2-E0026.10 POPKA, MICHAEL 12/14/2005
GB2-E0026.10 WRIGHT, STEVE 12/22/2005

I am currently using a query to select records where the WBS Code contains a
letter 'E' in the fifth character. The query that I use is provided below:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[E]*"));

I have designed a form to allow the user to select limited options. I would
then like the form to generate a query (like the one above based on these
options (if the checkbox is selected)

If the E-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[E]*"));

If the L-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[L]*"));

If the M-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[M]*"));

Many Thanks
 
A

Al Camp

mohd21uk,
Try...
Mid([WBS Code],5,1) = "E" to find the E in the fifth position.

I would use a combobox (or lisbox) to select a letter to filter by. Checkboxes deliver
numbers, which must be converted (1 = "A", 2 = "B", etc..) With a combo or listbox, you
can use the value returned directly...
Mid([WBS Code],5,1) = Forms!YourFormName!cboLetterSelect
A combo takes up a lot less "real estate" on the form.

If you must use checkboxes, then put your possible letter choices in an OptionGroup so
you only have to address one value in your query, and assign each of them the ascii value
of the letter choices. Make the "A" checkbox deliver numeric 64, the "B" checkbox deliver
65, etc...
Then
Asc(Mid([WBS Code],5,1) = Forms!YourFormName!optLetterSelect
should do it.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


mohd21uk via AccessMonster.com said:
I have a form with checkboxes used to select certain values. These checkboxes
allow users to query data from a table called CATS. A sample of this table is
provided below:

WBS Code User Name MaxOfDate
GB2-E0005.10 EGAN-SHEPHERD, CLIVE 09/08/2005
GB2-E0020.10 BANKS, TIM 12/05/2005
GB2-E0020.10 PARRIS, PHIL 12/23/2005
GB2-E0020.10 WHITMARSH, KEVIN 12/14/2005
GB2-E0020.25 ALDERTON, MALCOLM 09/05/2005
GB2-E0020.35 REGAN, DAVID 12/15/2005
GB2-E0020.40 BAKER, ANDY 09/26/2005
GB2-E0020.40 BOONE, CHRISTOPHER 09/20/2005
GB2-E0020.40 BURDON, JOHN 09/26/2005
GB2-E0020.40 CREAN, PAUL 09/30/2005
GB2-E0020.40 CUTLAND, TIM 09/30/2005
GB2-E0020.40 GILBERT, PAUL 09/30/2005
GB2-E0020.40 GREEN, DAVID 12/21/2005
GB2-E0020.40 NEALE, BRIAN 07/27/2005
GB2-E0020.40 OVER, ROBERT 09/28/2005
GB2-E0020.40 SIEUW, DIRK 09/30/2005
GB2-E0020.40 YOUART, PETER 10/14/2005
GB2-E0020.45 NEILSON, GORDON 12/07/2005
GB2-E0020.50 ALDERTON, MALCOLM 12/09/2005
GB2-E0020.55 CLOUGH, MICHAEL 01/27/2006
GB2-E0020.55 HUSSAIN, HABID 10/14/2005
GB2-E0020.55 MCCALDON, SEAN 03/03/2006
GB2-E0020.60 BANKS, TIM 09/11/2005
GB2-E0020.60 PARRIS, PHIL 10/11/2005
GB2-E0026.10 BANKS, MARTIN 10/17/2005
GB2-E0026.10 CLARKE, PHILIP 04/07/2005
GB2-E0026.10 DAVIES, GARETH 10/06/2005
GB2-E0026.10 GODFREY, STEVE 06/15/2005
GB2-E0026.10 HARRISON, DAVE 12/05/2005
GB2-E0026.10 HEEKS, FRED 12/05/2005
GB2-E0026.10 JEPSON, ANDY 12/05/2005
GB2-E0026.10 POPKA, MICHAEL 12/14/2005
GB2-E0026.10 WRIGHT, STEVE 12/22/2005

I am currently using a query to select records where the WBS Code contains a
letter 'E' in the fifth character. The query that I use is provided below:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[E]*"));

I have designed a form to allow the user to select limited options. I would
then like the form to generate a query (like the one above based on these
options (if the checkbox is selected)

If the E-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[E]*"));

If the L-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[L]*"));

If the M-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[M]*"));

Many Thanks
Click to expand...
 
K

Klatuu

You can do this with only one query. First, I would suggest you change your
individual check boxes to an option group since you will be selection only
one option. Then some minor changes to the query.
For the option group, Assign an option button (or check box) to each letter
you want to filter on. For this example, I will use E, L and M as you
described.
So, make the button for E have an Option Value of 1, L 2, and M 3.
Now we will modify the query to use the numeric values (required by the
option value property).

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((Mid(tbl_CATS.[WBS Code]4,1)) =
Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M:)));

mohd21uk via AccessMonster.com said:
I have a form with checkboxes used to select certain values. These checkboxes
allow users to query data from a table called CATS. A sample of this table is
provided below:

WBS Code User Name MaxOfDate
GB2-E0005.10 EGAN-SHEPHERD, CLIVE 09/08/2005
GB2-E0020.10 BANKS, TIM 12/05/2005
GB2-E0020.10 PARRIS, PHIL 12/23/2005
GB2-E0020.10 WHITMARSH, KEVIN 12/14/2005
GB2-E0020.25 ALDERTON, MALCOLM 09/05/2005
GB2-E0020.35 REGAN, DAVID 12/15/2005
GB2-E0020.40 BAKER, ANDY 09/26/2005
GB2-E0020.40 BOONE, CHRISTOPHER 09/20/2005
GB2-E0020.40 BURDON, JOHN 09/26/2005
GB2-E0020.40 CREAN, PAUL 09/30/2005
GB2-E0020.40 CUTLAND, TIM 09/30/2005
GB2-E0020.40 GILBERT, PAUL 09/30/2005
GB2-E0020.40 GREEN, DAVID 12/21/2005
GB2-E0020.40 NEALE, BRIAN 07/27/2005
GB2-E0020.40 OVER, ROBERT 09/28/2005
GB2-E0020.40 SIEUW, DIRK 09/30/2005
GB2-E0020.40 YOUART, PETER 10/14/2005
GB2-E0020.45 NEILSON, GORDON 12/07/2005
GB2-E0020.50 ALDERTON, MALCOLM 12/09/2005
GB2-E0020.55 CLOUGH, MICHAEL 01/27/2006
GB2-E0020.55 HUSSAIN, HABID 10/14/2005
GB2-E0020.55 MCCALDON, SEAN 03/03/2006
GB2-E0020.60 BANKS, TIM 09/11/2005
GB2-E0020.60 PARRIS, PHIL 10/11/2005
GB2-E0026.10 BANKS, MARTIN 10/17/2005
GB2-E0026.10 CLARKE, PHILIP 04/07/2005
GB2-E0026.10 DAVIES, GARETH 10/06/2005
GB2-E0026.10 GODFREY, STEVE 06/15/2005
GB2-E0026.10 HARRISON, DAVE 12/05/2005
GB2-E0026.10 HEEKS, FRED 12/05/2005
GB2-E0026.10 JEPSON, ANDY 12/05/2005
GB2-E0026.10 POPKA, MICHAEL 12/14/2005
GB2-E0026.10 WRIGHT, STEVE 12/22/2005

I am currently using a query to select records where the WBS Code contains a
letter 'E' in the fifth character. The query that I use is provided below:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[E]*"));

I have designed a form to allow the user to select limited options. I would
then like the form to generate a query (like the one above based on these
options (if the checkbox is selected)

If the E-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[E]*"));

If the L-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[L]*"));

If the M-Code checkbox is selected:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((tbl_CATS.[WBS Code]) Like "***[M]*"));

Many Thanks
Click to expand...
 
M

mohd21uk via AccessMonster.com

Thanks for the code but it does not seem to be working. It comes with a
message ' Syntax Error (Missing Operator) '. I would be grateful if you could
sort this for me
You can do this with only one query. First, I would suggest you change your
individual check boxes to an option group since you will be selection only
one option. Then some minor changes to the query.
For the option group, Assign an option button (or check box) to each letter
you want to filter on. For this example, I will use E, L and M as you
described.
So, make the button for E have an Option Value of 1, L 2, and M 3.
Now we will modify the query to use the numeric values (required by the
option value property).

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((Mid(tbl_CATS.[WBS Code]4,1)) =
Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M:)));
I have a form with checkboxes used to select certain values. These checkboxes
allow users to query data from a table called CATS. A sample of this table is
Click to expand...
[quoted text clipped - 73 lines]
Many Thanks
Click to expand...
Click to expand...
 
K

Klatuu

This may be the problem, a Typo on my part. The colon right after the letter
M should be a quote mark:

Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M:)));

Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M")));

If that does not solve it, I will need for you to send me the code as you
entered it so I can have a look at it.

mohd21uk via AccessMonster.com said:
Thanks for the code but it does not seem to be working. It comes with a
message ' Syntax Error (Missing Operator) '. I would be grateful if you could
sort this for me
You can do this with only one query. First, I would suggest you change your
individual check boxes to an option group since you will be selection only
one option. Then some minor changes to the query.
For the option group, Assign an option button (or check box) to each letter
you want to filter on. For this example, I will use E, L and M as you
described.
So, make the button for E have an Option Value of 1, L 2, and M 3.
Now we will modify the query to use the numeric values (required by the
option value property).

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((Mid(tbl_CATS.[WBS Code]4,1)) =
Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M:)));
I have a form with checkboxes used to select certain values. These checkboxes
allow users to query data from a table called CATS. A sample of this table is
Click to expand...
[quoted text clipped - 73 lines]
Many Thanks
Click to expand...
Click to expand...
Click to expand...
 
M

mohd21uk via AccessMonster.com

Hi,

I have entered the code as:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((Mid(tbl_CATS.[WBS Code]4,1)) =
Choose(Forms!frm_CN1-2!WBSCode,"E","L","M")));

But is still does not seem to work. Please suggest any changes.
This may be the problem, a Typo on my part. The colon right after the letter
M should be a quote mark:

Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M:)));

Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M")));

If that does not solve it, I will need for you to send me the code as you
entered it so I can have a look at it.
Thanks for the code but it does not seem to be working. It comes with a
message ' Syntax Error (Missing Operator) '. I would be grateful if you could
Click to expand...
[quoted text clipped - 23 lines]
Click to expand...
 
K

Klatuu

Needs a comma after Code]
HAVING (((Mid(tbl_CATS.[WBS Code],4,1))
Sorry about my crappy typing.

mohd21uk via AccessMonster.com said:
Hi,

I have entered the code as:

SELECT tbl_CATS.[WBS Code], tbl_CATS.[User Name], Max(tbl_CATS.Date) AS
MaxOfDate
FROM tbl_CATS
GROUP BY tbl_CATS.[WBS Code], tbl_CATS.[User Name]
HAVING (((Mid(tbl_CATS.[WBS Code]4,1)) =
Choose(Forms!frm_CN1-2!WBSCode,"E","L","M")));

But is still does not seem to work. Please suggest any changes.
This may be the problem, a Typo on my part. The colon right after the letter
M should be a quote mark:

Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M:)));

Choose(Forms!MyFormName!MyOptionGrouName,"E","L","M")));

If that does not solve it, I will need for you to send me the code as you
entered it so I can have a look at it.
Thanks for the code but it does not seem to be working. It comes with a
message ' Syntax Error (Missing Operator) '. I would be grateful if you could
Click to expand...
[quoted text clipped - 23 lines]
Many Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Usind the Option Group Function 1
Uisng an option group query 0
Importing tables and linking them 1
Find then highlight in yellow 6
Advanced problem using auto-number and datediff to find readmissio 2
double negative? 5
Office Update is unable to check for updates 2
Searching/Forms/Reports 0

Top