Need Help on getting the query to come back with a blank value if thevalue is equal to the criteria

[Alicia]

Okay guys this is the last one.

My query will pull everything that has this months date in it. When
the query finds a date that falls in this month it also prints the
dates by the person's name that does not fall in this month. How can
I get it to return a blank value If it does not match the criteria.

Thanks for your help so for.


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())));

 

[Steve.C.Hutchinson]

Okay guys this is the last one.

My query will pull everything that has this months date in it.  When
the query finds a date that falls in this month it also prints the
dates by the person's name that does not fall in this month.  How can
I get it to return a blank value If it does not match the criteria.

Thanks for your help so for.

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())));

Alicia,
First, you don't need to specify the Year test since the Month test
will suffice.
Second, "License" not "Lincense" (?)
Third, You can return a blank as the result of a date by using an Iif
statement in the Field line:
License: IIf(Month([License])<>Month(Now()),"",[License])

 

[Alicia]

You are right I'm sorry I spelled Lincense wrong.

I do need it to pull from this year and this month because, I might
have something that expires in 9/28/09. And I do not want my database
to pull that information.

 

[Steve.C.Hutchinson]

Alicia,
I've had problems using "Now()" when I really just want the date
without the time included. You can use "DateValue(Now)" instead of
"Now()".
I switched after realizing that 9/30/2008 does not equal Now(). The
"9/30/2008" has an implied time stamp of "00:00:00" and Now() is like
"9/30/2008 12:17:30". If my date field is [License] = #9/30/2008# and
I set criteria:
[License] = Now()
then I won't get any results because the times don't match, where this
does work:
[License] = DateValue(Now)
When you throw in a logical operator like "<" or ">" then everything
will be fine except on the calendar date that is the same as the Now()
date. I just happened to catch this when I ran a query on the same
day as the date I was testing to, and no results appeared (it just so
happened to be the first day of a new fiscal quarter).

 

[Steve.C.Hutchinson]

You are right I'm sorry I spelled Lincense wrong.

I do need it to pull from this year and this month because, I might
have something that expires in 9/28/09.  And I do not want my database
to pull that information.

Ahh yes you are right!

 

[John Spencer]

SELECT Test.[ID], Test.[First Name], Test.[Last Name]
, IIF(Test.[Crane Certification] Between
DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),[Crane Certification],Null) as
CraneDate

, IIF(Test.[Physical] Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),[Physical],Null) as PhysicalDate

, IIF(Test.[Lincense] Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),Lincense,Null) as LincenseDate

, IIF(Test.[Safety Card]Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),[Safety Card],Null) as SafetyDate

FROM Test
WHERE Lincense Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)
OR Physical Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)
OR [Crane Certification] Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)
OR [Safety Card] Between DateSerial(Year(Date()),Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0)

Using actual dates should speed up the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County