Solved

Enter Parameter Value

Posted on 2006-11-11
4
458 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 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