Format Date in query

[Guest]

Hi all...

I have two queries feeding into one query that gives me a desired result.
However I need it to do this..

Right now is show everything that has an incident On 07/04/2006 (
#07/04/2006# ) and before.. that part works.... What I would like it to
show is if it has a hit for any 07/04/YEAR..... so if there is an incident
for 07/04/2005 and an incident for 07/03/2004 show me...

Right now all of my dates look like this in the criteria section of the
queries:
(one query looks for same locations on 7/4/2006, one query looks for that
same location on or before 7/4/2006 and the last one tells me if there are
any matchs)


q1= #7/4/2006#
q2= <=#7/4/2006#
q3= <=#7/4/2006#

How do I show for any year??
R~

 

[Douglas J. Steele]

WHERE Format(IncidentDate, "mmdd") = "0704"

More generically, if you want it for today, you can use:

WHERE Format(IncidentDate, "mmdd") = Format(Date(), "mmdd")

 

[Marshall Barton]

I have two queries feeding into one query that gives me a desired result.
However I need it to do this..

Right now is show everything that has an incident On 07/04/2006 (
#07/04/2006# ) and before.. that part works.... What I would like it to
show is if it has a hit for any 07/04/YEAR..... so if there is an incident
for 07/04/2005 and an incident for 07/03/2004 show me...

Right now all of my dates look like this in the criteria section of the
queries:
(one query looks for same locations on 7/4/2006, one query looks for that
same location on or before 7/4/2006 and the last one tells me if there are
any matchs)


q1= #7/4/2006#
q2= <=#7/4/2006#
q3= <=#7/4/2006#

Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"

 

[Guest]

Marshall and Douglas..

I tried both of them... and I keep getting errors.. I may be putting them
in the wrong areas.

1. Do I also leave my other criteria in the other field?
2. The error that I am getting the most is this:

"You tried to execute a query that does not include the specified expression
"Not ZQuery_Q1.Expr1 Is Null and Format([Incident],"mm/dd")="07/04" as part
of an aggregate function"

R~

 

[Guest]

One more thing... Would I need to change the date format in all three of the
queries or just the last one that gives me the info I am looking at.. I have
been trying different ways and none of them are working..

R~

Marshall and Douglas..

I tried both of them... and I keep getting errors.. I may be putting them
in the wrong areas.

1. Do I also leave my other criteria in the other field?
2. The error that I am getting the most is this:

"You tried to execute a query that does not include the specified expression
"Not ZQuery_Q1.Expr1 Is Null and Format([Incident],"mm/dd")="07/04" as part
of an aggregate function"

R~

Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"

 

[Douglas J. Steele]

Open the query in SQL View (look under the View menu) and paste the SQL of
your query here.

It might make sense to post the SQL for all 3 queries if you want an answer
to your other question.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Marshall and Douglas..

I tried both of them... and I keep getting errors.. I may be putting them
in the wrong areas.

1. Do I also leave my other criteria in the other field?
2. The error that I am getting the most is this:

"You tried to execute a query that does not include the specified
expression
"Not ZQuery_Q1.Expr1 Is Null and Format([Incident],"mm/dd")="07/04" as
part
of an aggregate function"

R~

Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"

 

[Marshall Barton]

The changes Doug and I suggested are not formats, so no, you
should not have to change anything else.

Post a Copy/Paste of your query's SQL statement so we can
see what you have.
--
Marsh
MVP [MS Access]

One more thing... Would I need to change the date format in all three of the
queries or just the last one that gives me the info I am looking at.. I have
been trying different ways and none of them are working..

Marshall and Douglas..

I tried both of them... and I keep getting errors.. I may be putting them
in the wrong areas.

1. Do I also leave my other criteria in the other field?
2. The error that I am getting the most is this:

"You tried to execute a query that does not include the specified expression
"Not ZQuery_Q1.Expr1 Is Null and Format([Incident],"mm/dd")="07/04" as part
of an aggregate function"

R~

Rhett_Y wrote:
I have two queries feeding into one query that gives me a desired result.
However I need it to do this..

Right now is show everything that has an incident On 07/04/2006 (
#07/04/2006# ) and before.. that part works.... What I would like it to
show is if it has a hit for any 07/04/YEAR..... so if there is an incident
for 07/04/2005 and an incident for 07/03/2004 show me...

Right now all of my dates look like this in the criteria section of the
queries:
(one query looks for same locations on 7/4/2006, one query looks for that
same location on or before 7/4/2006 and the last one tells me if there are
any matchs)


q1= #7/4/2006#
q2= <=#7/4/2006#
q3= <=#7/4/2006#


Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"