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
jfringerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
All Courses

From novice to tech pro — start learning today.