Usind the Option Group Function

  • 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
 
G

Guest

Your subject says Option Group Function and your text says checkboxes. If
you are using an Option Group then the results of selecting an item in a
group is a numerical value and not a letter.

You could use a translation table - letter to number - and a query on that
table with criteria from the option group. Use the that query as criteria in
your select query.

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...
 

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

Using Check boxes 6
double negative? 5
Group date into Year and Quarters 4
Select next item in datareader, then insert a new record and update the rest 5
Login Logout Date Problem 2
NOD332: Removal Tool for my Worms?. 1
Direct x 9 gone 1
Crashing Repeatedly - Internet Explorer 6.0 4

Top