Please Help

  • Thread starter Thread starter Alicia
  • Start date Start date
A

Alicia

I know I'm being a pain but. I still can't get my query to do what I
need it to. It pull the date that contain dates within this month but
if an employee has a date that expires in this month it pulls all
dates for him accross the board.

Lincense Safety Card
Crane Certification Physical

test 1
9/3/08
8/4/08 5/7/08
test 2 9/28/08


Please help and keep in mind, I do not know that much about access.
I am a beginner so feel free to treat me like a two year old. I do
not have much experience.

SELECT Test.[ID], Test.[First Name], Test.[Last Name], Test.[Crane
Certification], Test.[Physical], Test.[Lincense], Test.[Safety Card]
FROM Test
WHERE ((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now()))) OR ((Year([Physical])=Year(Now())
And
Month([Physical])=Month(Now()))) OR ((Year([Crane
Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now()))) OR ((Year([Safety Card])=Year(Now())
And Month([Safety Card])=Month(Now())));
 
o you want to only show the value that is expired not all values why
didnt you say that last time

that will only take a few if statements

SELECT Test.id, Test.[first name], Test.[last name], IIf(((Year([Crane
Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now()))),Test.[Crane Certification],Null) AS
Expr1, IIf(((Year([Physical])=Year(Now()) And
Month([Physical])=Month(Now()))),Test.[Physical],Null) AS Expr2,
IIf(((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now()))),Test.[Lincense],Null) AS Expr3,
IIf(((Year([Safety Card])=Year(Now()) And Month([Safety
Card])=Month(Now()))),Test.[Safety Card],Null) AS Expr4
FROM Test
WHERE ((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now())))
OR ((Year([Physical])=Year(Now()) And
Month([Physical])=Month(Now())))
OR ((Year([Crane Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now())))
OR ((Year([Safety Card])=Year(Now()) And Month([Safety
Card])=Month(Now())));

that work and only shows the people with thoes values that are
expiring and only shows the expiring vlaues

IF you want to show all people regardless use

SELECT Test.id, Test.[first name], Test.[last name], IIf(((Year([Crane
Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now()))),Test.[Crane Certification],Null) AS
Expr1, IIf(((Year([Physical])=Year(Now()) And
Month([Physical])=Month(Now()))),Test.[Physical],Null) AS Expr2,
IIf(((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now()))),Test.[Lincense],Null) AS Expr3,
IIf(((Year([Safety Card])=Year(Now()) And Month([Safety
Card])=Month(Now()))),Test.[Safety Card],Null) AS Expr4
FROM Test

and that will show all people but only values that are expiring

hope this helps

Regards
Kelvan
 
sorry if this is a double post but it seems to not have made my post

all you need is a few iifs

SELECT Test.id, Test.[first name], Test.[last name], IIf(((Year([Crane
Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now()))),Test.[Crane Certification],Null) AS
Expr1, IIf(((Year([Physical])=Year(Now()) And
Month([Physical])=Month(Now()))),Test.[Physical],Null) AS Expr2,
IIf(((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now()))),Test.[Lincense],Null) AS Expr3,
IIf(((Year([Safety Card])=Year(Now()) And Month([Safety
Card])=Month(Now()))),Test.[Safety Card],Null) AS Expr4
FROM Test
WHERE ((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now())))
OR ((Year([Physical])=Year(Now()) And
Month([Physical])=Month(Now())))
OR ((Year([Crane Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now())))
OR ((Year([Safety Card])=Year(Now()) And Month([Safety
Card])=Month(Now())));

if you want to show everyone though event if they dont have an expired
date use

SELECT Test.id, Test.[first name], Test.[last name], IIf(((Year([Crane
Certification])=Year(Now()) And Month([Crane
Certification])=Month(Now()))),Test.[Crane Certification],Null) AS
Expr1, IIf(((Year([Physical])=Year(Now()) And
Month([Physical])=Month(Now()))),Test.[Physical],Null) AS Expr2,
IIf(((Year([Lincense])=Year(Now()) And
Month([Lincense])=Month(Now()))),Test.[Lincense],Null) AS Expr3,
IIf(((Year([Safety Card])=Year(Now()) And Month([Safety
Card])=Month(Now()))),Test.[Safety Card],Null) AS Expr4
FROM Test

hope this helps

Regards
Kelvan
 
Back
Top