Parameter query with Sums

[smol84]

Hi,
I'm having a lot of trouble creating a parameter query that will work when i sum a column.

I can get it to work without a seum (code below)
SELECT "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP, "Daily VDNs"."Inbound Calls"

FROM "RSS Data".RssUserRW."Daily VDNs" "Daily VDNs", "RSS Data".RssUserRW."IVR VDN Filter" "IVR VDN Filter"

WHERE "Daily VDNs".ACD = "IVR VDN Filter".ACD AND "Daily VDNs"."VDN Number" = "IVR VDN Filter".VDN AND (("Daily VDNs".Date=?) AND ("IVR VDN Filter".NSWCTP Is Not Null))

But I cant get it to work when I change "Daily VDNs"."Inbound Calls" to a sum (code below)

SELECT "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP, Sum("Daily VDNs"."Inbound Calls") AS 'Sum of Inbound Calls'

FROM "RSS Data".RssUserRW."Daily VDNs" "Daily VDNs", "RSS Data".RssUserRW."IVR VDN Filter" "IVR VDN Filter"

WHERE "Daily VDNs".ACD = "IVR VDN Filter".ACD AND "Daily VDNs"."VDN Number" = "IVR VDN Filter".VDN

GROUP BY "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP

HAVING ("Daily VDNs".Date={?}) AND ("IVR VDN Filter".NSWCTP Is Not Null)

However, I can use the above code without a parameter query and define a value. But life would be so much easier with a parameter query.

Any assistance is greatly, greatly appreciated!!

 

[Pat Hartman]

1. Define doesn't work
2. Doesn't it get to be annoying to have to type all those quotes? If you used proper naming convertions they wouldn't be necessary.

 

[smol84]

Thanks for your reply pat,
what do you mean by definae doesn't work?

and I agree with the quotes, very annoying. However MSQuery doesn't seem to work graphically even when the correct naming convention is used.

 

[Pat Hartman]

The syntax of your query is not standard Access syntax so I'm not sure I can help at all. Are you trying to run this SQL from an .adp? Are we looking at a view, a stored procedure, or an SQL string from your application?

 

[smol84]

its the SQL retrieved from MS Query, the query its self was created with the MS query wizard and runs fine (when not using a parameter query).

 

[Pat Hartman]

I guess I don't know what this has to do with Access then. The way you supply parameters to queries when they are run in an Access database wouldn't apply if you are using MS Query. Are you actually using Word or Excel? Perhaps one of those newsgroups might be a better source for an answer.