New QueryDef won't work

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

Guest

OK this is a strange one. To allow users flexibility in a where clause, I
delete a querydef then recreate it with user's selections. When after
recreating the query, when I run it, I get no results. If I put th query in
design mode and make ANY change to it (such as erasing the adding the ";")
the query then runs just fine. Here's the SQL I'm putting into the new
querydef:

SELECT MonthName AS CallMonth, FacilityName AS Facility, CallZone,
Sum([CallDurEpoch]/60) AS Minutes, Month([CallStart]) AS MonthNum
FROM dbo_vTotalCalls
WHERE (CallStart Between 3/1/2005 And 3/31/2005) AND (FacilityName IN
("Education Center", "Hidden Hills"))
GROUP BY MonthName, FacilityName, CallZone, Month([CallStart]), CallYear,
CallDir, FacilityName, CallZone
HAVING ((CallDir)="OUT")
ORDER BY Month([CallStart]), FacilityName, CallZone DESC;

Anyone have a clue about what access is doing? Would posting the SQL as
saved by Access be of use?
 
Thanks Duane....that did it.

Duane Hookom said:
Dates should be delimited with #. ...CallStart Between #3/1/2005#...

--
Duane Hookom
MS Access MVP


MarcS said:
OK this is a strange one. To allow users flexibility in a where clause, I
delete a querydef then recreate it with user's selections. When after
recreating the query, when I run it, I get no results. If I put th query
in
design mode and make ANY change to it (such as erasing the adding the ";")
the query then runs just fine. Here's the SQL I'm putting into the new
querydef:

SELECT MonthName AS CallMonth, FacilityName AS Facility, CallZone,
Sum([CallDurEpoch]/60) AS Minutes, Month([CallStart]) AS MonthNum
FROM dbo_vTotalCalls
WHERE (CallStart Between 3/1/2005 And 3/31/2005) AND (FacilityName IN
("Education Center", "Hidden Hills"))
GROUP BY MonthName, FacilityName, CallZone, Month([CallStart]), CallYear,
CallDir, FacilityName, CallZone
HAVING ((CallDir)="OUT")
ORDER BY Month([CallStart]), FacilityName, CallZone DESC;

Anyone have a clue about what access is doing? Would posting the SQL as
saved by Access be of use?
Click to expand...
Click to expand...
 
Back
Top