Format Date in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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~
 
WHERE Format(IncidentDate, "mmdd") = "0704"

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

WHERE Format(IncidentDate, "mmdd") = Format(Date(), "mmdd")
 
Rhett_Y said:
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#
Click to expand...


Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"
 
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~
 
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~

Rhett_Y said:
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~

Marshall Barton said:
Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"
Click to expand...
Click to expand...
 
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)


Rhett_Y said:
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~

Marshall Barton said:
Add a calculated field to the query
Format(incident, "mm/dd")
and use the criteria
"07/04"
Click to expand...
Click to expand...
 
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]


Rhett_Y said:
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..


Rhett_Y said:
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~

Marshall Barton said:
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"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top