?
Solved

Enter Parameter Value

Posted on 2006-11-11
4
Medium Priority
?
475 Views
Last Modified: 2012-05-05
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
Comment
Question by:jfringer
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 17922600


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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17922603
Have you tried just omitting   [qryMultiplier1]*[H1]   from your Group By clause?
 
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17922616
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17922635
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question