Conversion of Select Qry to Dlookup

I had this question after viewing syntax error in query expression in MS/ACCESS 2016 (DLOOKUP).

Hello,
Presently i am using below query in a Listbox (with single column), to get a desired qty on a Form.

SELECT Sum(tblLifting.Lift_Qty) AS SumOfLift_Qty
FROM tblLifting
WHERE (((tblLifting.PO_no)=[forms]![FormSearchPurchase2]![PO_No]) AND ((tblLifting.Lift_Date)<=[Forms]![FormSearchPurchase2]![txtFreestorage]));

Now as i want to do some calculation on the above value, i wish to get the above result in Textbox and not in Listbox.
In short, i want to convert this qry into a txtbox value.
How can i achieve this ?
Please help.
Regards,
Kamlesh Jain
Kamlesh JainManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Kamlesh,

if the listbox is getting what you want, you could hide it by setting Visible = No

Then you can create a textbox with this control source:
= [controlName_mylistbox]

... but that may not be necessary

> "want to do some calculation on the above value"

what kind of calculation? Do you just want a value you can copy? Use a combobox instead of a listbox. In the design view, right-click on the listbox control and choose: Change To > Combo box

have an awesome day,
crystal
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
In short, i want to convert this qry into a txtbox value.
How can i achieve this ?
try use DSum function.

like in the Control Source of the textbox, set it as:

=DSum("Lift_Qty","tblLifting","PO_no >= '" & replace([forms]![FormSearchPurchase2]![PO_No],"'","''") & "' and Lift_Date <= #" & [Forms]![FormSearchPurchase2]![txtFreestorage] & "# ")

Open in new window

(not tested)

more ref:
DSum Function
https://support.office.com/en-us/article/dsum-function-08f8450e-3bf6-45e2-936f-386056e61a32
0
Kamlesh JainManagerAuthor Commented:
Thanks Crystal for so prompt response. Let me explain further.
In the attached Screenshot, Qty values, 41.66 and 54.11 is the sumofQtyLifted, arriving from the qry, stated above.

I want this qty to be deducted and the result value shall be show in a textbox, next to it. (Not in Screen shot).

In the example, say i have Pur_Qty = 100 Mt
In the screen shot, at one listbox , qty is shown as 41.66 Mt.
What i want : In the next text box i want value as = Pur_Qty - value of listbox (41.55) Result as = 58.34
Hope i am making clear.
Thanks
2018-01-11--2-_LI.jpg
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Kamlesh JainManagerAuthor Commented:
Thanks Mr Ryan. Solution seems working but in some records, its showing wrong data... :( Max records are showing correct but some records are yileding wrong data.
Shall revert asap..

Thanx again. :)
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Max records are showing correct but some records are yileding wrong data.
for those that returned with wrong summed data, try check the source table(s) and see if got "duplicate" records are matched the criterion.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Kamlesh,

perhaps you want to put calculations in a subform that is synchronized with the main form using LinkMasterFields and LinkChildFields.

here is an EE video tutorial with an example:

Subform to show Calculations in Microsoft Access (10:48)
https://www.experts-exchange.com/videos/9937/Subform-to-show-Calculations-in-Microsoft-Access.html

have an awesome day,
crystal
0
Kamlesh JainManagerAuthor Commented:
Hope crystal wont mind...;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.