How to resolve Enter Parameter Value?

[jeff3457]

Hi,

Does anyone know why I am recieiveing the enter parameter value message?

I am using the totals function totaling by Sum.

Thanks,

 

[theDBguy]

Hi. Can you post the SQL statement for your Totals Query? Also, what does the parameter prompt say?

 

[jeff3457]

Hi. Can you post the SQL statement for your Totals Query? Also, what does the parameter prompt say?

SELECT [T59 CS - Reimagine].[E Grouping], Sum([T59 CS - Reimagine].[CS Hrs]) AS [SumOfCS Hrs], [T59 B - Reimagine].[E Hrs], Sum([T59-AreaSuffixStore].BaseWeekHours) AS SumOfBaseWeekHours, ([SumOfBaseWeekHours]-[SumOfCS Hrs]) AS [E Hours Needed]
FROM ([T59 CS - Reimagine] INNER JOIN [T59 B - Reimagine] ON [T59 CS - Reimagine].[E Grouping] = [T59 B - Reimagine].[FLR Zone]) INNER JOIN [T59-AreaSuffixStore] ON [T59 CS - Reimagine].STASS = [T59-AreaSuffixStore].STASS
GROUP BY [T59 CS - Reimagine].[E Grouping], [T59 B - Reimagine].[E Hrs], ([SumOfBaseWeekHours]-[SumOfCS Hrs]);

 

[Gasman]

Better still, as requested, what is the name of the parameter it is asking for?

Normally a misspelled name?

 

[jeff3457]

Better still, as requested, what is the name of the parameter it is asking for?

Normally a misspelled name?

It is actually asking for one for "SumofBaseWeekHours" which is my BaseWeekHours sum column and then "CS Hrs" which is my CS Hrs sum column.

 

[theDBguy]

SELECT [T59 CS - Reimagine].[E Grouping], Sum([T59 CS - Reimagine].[CS Hrs]) AS [SumOfCS Hrs], [T59 B - Reimagine].[E Hrs], Sum([T59-AreaSuffixStore].BaseWeekHours) AS SumOfBaseWeekHours, ([SumOfBaseWeekHours]-[SumOfCS Hrs]) AS [E Hours Needed]
FROM ([T59 CS - Reimagine] INNER JOIN [T59 B - Reimagine] ON [T59 CS - Reimagine].[E Grouping] = [T59 B - Reimagine].[FLR Zone]) INNER JOIN [T59-AreaSuffixStore] ON [T59 CS - Reimagine].STASS = [T59-AreaSuffixStore].STASS
GROUP BY [T59 CS - Reimagine].[E Grouping], [T59 B - Reimagine].[E Hrs], ([SumOfBaseWeekHours]-[SumOfCS Hrs]);

Hi. Try this:

SELECT [T59 CS - Reimagine].[E Grouping]
  , Sum([T59 CS - Reimagine].[CS  Hrs]) AS [SumOfCS Hrs]
  , [T59 B - Reimagine].[E Hrs]
  ,  Sum([T59-AreaSuffixStore].BaseWeekHours) AS SumOfBaseWeekHours
  ,  ([SumOfBaseWeekHours]-[SumOfCS Hrs]) AS [E Hours Needed]
FROM ([T59 CS - Reimagine] 
INNER JOIN [T59 B - Reimagine] 
  ON [T59 CS -  Reimagine].[E Grouping] = [T59 B - Reimagine].[FLR Zone])
INNER JOIN  [T59-AreaSuffixStore] 
  ON [T59 CS - Reimagine].STASS =  [T59-AreaSuffixStore].STASS
GROUP BY [T59 CS - Reimagine].[E Grouping]
  , [T59 B - Reimagine].[E Hrs]
  , Sum([T59-AreaSuffixStore].BaseWeekHours)-[SumOfCS Hrs];

(untested)

 

[jeff3457]

Hi. Try this:

SELECT [T59 CS - Reimagine].[E Grouping]
  , Sum([T59 CS - Reimagine].[CS  Hrs]) AS [SumOfCS Hrs]
  , [T59 B - Reimagine].[E Hrs]
  ,  Sum([T59-AreaSuffixStore].BaseWeekHours) AS SumOfBaseWeekHours
  ,  ([SumOfBaseWeekHours]-[SumOfCS Hrs]) AS [E Hours Needed]
FROM ([T59 CS - Reimagine] 
INNER JOIN [T59 B - Reimagine] 
  ON [T59 CS -  Reimagine].[E Grouping] = [T59 B - Reimagine].[FLR Zone])
INNER JOIN  [T59-AreaSuffixStore] 
  ON [T59 CS - Reimagine].STASS =  [T59-AreaSuffixStore].STASS
GROUP BY [T59 CS - Reimagine].[E Grouping]
  , [T59 B - Reimagine].[E Hrs]
  , Sum([T59-AreaSuffixStore].BaseWeekHours)-[SumOfCS Hrs];

(untested)

I received a syntax error in the join operation.
(I am not SQL knowledge able so I am not sure how to resolve)

 

[theDBguy]

I received a syntax error in the join operation.
(I am not SQL knowledge able so I am not sure how to resolve)

Are you able to post a copy of your db?

 

[jeff3457]

Are you able to post a copy of your db?

No, it is a bit massive and predates me. So I am working off of someone else's work.

I did resolve the syntax though. Now I have a "Cannot have aggregate function in Group By clause (Sum(([T59-AreaSuffixStore].BaseWeekHours)-[SumofCS Hrs])"

 

[theDBguy]

No, it is a bit massive and predates me. So I am working off of someone else's work.

I did resolve the syntax though. Now I have a "Cannot have aggregate function in Group By clause (Sum(([T59-AreaSuffixStore].BaseWeekHours)-[SumofCS Hrs])"

Hi. Try changing the GROUP BY clause to simply have the following:

...GROUP BY [T59 CS - Reimagine].[E Grouping], [T59 B - Reimagine].[E Hrs]

 

[jeff3457]

Hi. Try changing the GROUP BY clause to simply have the following:

...GROUP BY [T59 CS - Reimagine].[E Grouping], [T59 B - Reimagine].[E Hrs]

Thanks!

That did run it. But I am now getting a parameter message for "T59 CS - Reimagine.CS Hrs". It is also no longer returning values for "SumOfCS Hrs" or "E Hours Needed"

 

[theDBguy]

Thanks!

That did run it. But I am now getting a parameter message for "T59 CS - Reimagine.CS Hrs". It is also no longer returning values for "SumOfCS Hrs" or "E Hours Needed"

Hi. Can you post the new/updated SQL statement? Thanks.

 

[jeff3457]

Hi. Can you post the new/updated SQL statement? Thanks.

SELECT [T59 CS - Reimagine].[E Grouping], Sum([T59 CS - Reimagine].[CS Hrs]) AS [SumOfCS Hrs], [T59 B - Reimagine].[E Hrs], Sum([T59-AreaSuffixStore].BaseWeekHours) AS SumOfBaseWeekHours, ([SumOfBaseWeekHours]-[SumOfCS Hrs]) AS [E Hours Needed]

FROM ([T59 CS - Reimagine] INNER JOIN [T59 B - Reimagine] ON [T59 CS - Reimagine].[E Grouping] = [T59 B - Reimagine].[FLR Zone]) INNER JOIN [T59-AreaSuffixStore] ON [T59 CS - Reimagine].STASS = [T59-AreaSuffixStore].STASS

GROUP BY [T59 CS - Reimagine].[E Grouping], [T59 B - Reimagine].[E Hrs];

 

[theDBguy]

SELECT [T59 CS - Reimagine].[E Grouping], Sum([T59 CS - Reimagine].[CS Hrs]) AS [SumOfCS Hrs], [T59 B - Reimagine].[E Hrs], Sum([T59-AreaSuffixStore].BaseWeekHours) AS SumOfBaseWeekHours, ([SumOfBaseWeekHours]-[SumOfCS Hrs]) AS [E Hours Needed]

FROM ([T59 CS - Reimagine] INNER JOIN [T59 B - Reimagine] ON [T59 CS - Reimagine].[E Grouping] = [T59 B - Reimagine].[FLR Zone]) INNER JOIN [T59-AreaSuffixStore] ON [T59 CS - Reimagine].STASS = [T59-AreaSuffixStore].STASS

GROUP BY [T59 CS - Reimagine].[E Grouping], [T59 B - Reimagine].[E Hrs];

Hi. Thanks. Unfortunately, I don't see anything obviously wrong with that. I think all the brackets are in the right places. Can you create a mockup db with only the table structure (no data) for the three tables concerned in this query and then post it?

 

[jeff3457]

Hi. Thanks. Unfortunately, I don't see anything obviously wrong with that. I think all the brackets are in the right places. Can you create a mockup db with only the table structure (no data) for the three tables concerned in this query and then post it?

I have attached the tables and queries.

 

[theDBguy]

I have attached the tables and queries.

Hi. Thanks! Give this one a try.

 

[jeff3457]

Hi. Thanks! Give this one a try.

This seems to work.

THank you Thank you!!

 

[theDBguy]

This seems to work.

THank you Thank you!!

Hi. You're welcome. Sometimes, it's easier to fix something when you can "touch" it. Glad we could assist. Good luck with your project.

 

[The_Doc_Man]

Almost invariably, when you get an "Enter Parameter" box but didn't think you were working with parameters, it is because something is spelled wrong. To Access, if it can't find the object, then it must be because that object is a parameter, so it asks for it.

I noticed your field names had spaces and dashes in them. In general, these are not good name choices. The only special characters you can use in VBA names are the dollar sign and underscoree. Dash and space are not recommended.