M
mohd21uk via OfficeKB.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
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