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?
 
Ryan ChongCommented:
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
 
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 ChongCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.