[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

Enter Parameter Value

When I run the following statement I receive "enter parameter Value" qrymuliplier?  Qrymultiplier1 is a calculated field in a query.  When I press "OK" on the "enter parameter value" the query runs fine and the results are correct.  I'm not sure if this is the cause but the query does have null values in the qryMultiplier field.

SELECT DLookUp("Multiplier","tblSummary","profile=401 and FromDate<=#" & [dtProfile] & "# And ToDate>=#" & [dtProfile] & "#") AS qryMultiplier1, tblProfile.dtProfile, tblProfile.H1, tblProfile.ProfileNumber, [qryMultiplier1]*[H1] AS S_H1
FROM tblProfile, tblSummary
GROUP BY tblProfile.dtProfile, tblProfile.H1, tblProfile.ProfileNumber, [qryMultiplier1]*[H1]
HAVING (((tblProfile.ProfileNumber)=401));

Thanks...John
0
jfringer
Asked:
jfringer
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:


try


SELECT DLookUp("Multiplier","tblSummary","profile=401 and FromDate<=#" & [dtProfile] & "# And ToDate>=#" & [dtProfile] & "#") AS qryMultiplier1, tblProfile.dtProfile, tblProfile.H1, tblProfile.ProfileNumber, [qryMultiplier1]*[H1] AS S_H1
FROM tblProfile, tblSummary
GROUP BY tblProfile.dtProfile, tblProfile.H1, tblProfile.ProfileNumber, DLookUp("Multiplier","tblSummary","profile=401 and FromDate<=#" & [dtProfile] & "# And ToDate>=#" & [dtProfile] & "#") *[H1]
HAVING (((tblProfile.ProfileNumber)=401));
0
 
Leigh PurvisDatabase DeveloperCommented:
Have you tried just omitting   [qryMultiplier1]*[H1]   from your Group By clause?
 
0
 
rockiroadsCommented:
why have group by's when u dont have any aggregate functions

u could just DISTINCT?


SELECT DISTINCT DLookUp("Multiplier","tblSummary","profile=401 and FromDate<=#" & [dtProfile] & "# And ToDate>=#" & [dtProfile] & "#") AS qryMultiplier1, tblProfile.dtProfile, tblProfile.H1, tblProfile.ProfileNumber, [qryMultiplier1]*[H1] AS S_H1
FROM tblProfile, tblSummary
WHERE tblProfile.ProfileNumber=401



But u may need to repeate your DLOOKUP instead of using qryMultiplier1

0
 
Leigh PurvisDatabase DeveloperCommented:
I had a feeling you'd accept Cap's answer straight away - and it would indeed work.
(And I don't mind - as I said, it does work and it was first).

I'd considered it, but held off from it and I didn't post that suggestion for a reason.
Firstly, it's an extra hit on the function to grab that data again.
But fundamentally, it's based on data that is already grouped.
So it shouldn't change anything by ommitting it.

All I'm saying is give it a go - it should be quicker :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now