opus111
asked on
Format Currency in List Box using RowSource
Need to pass a SQL query into a list box when a record is selected from an adjacent list box. On the trigger even, the below SQL query resuls are sent to a list box with the RowSource command. I can't get the Sum figure outputed as Order Total to be formatted into Currency.
strSQL_OrderHistory = "SELECT dbo.tblorders.idorder, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, SUM(dbo.qry_customer_followup_orderhistory.[Line Total]) AS [Order Total], " & _
"dbo.tblorders.dtrequestship , dbo.tblorders.idcustomer, dbo.tblorders.intcancelled, dbo.tblorders.inttranstype " & _
"FROM dbo.tblorders LEFT OUTER JOIN " & _
"dbo.qry_customer_followup_orderhistory ON dbo.tblorders.idorder = dbo.qry_customer_followup_orderhistory.idorder " & _
"GROUP BY dbo.tblorders.idorder, dbo.tblorders.idcustomer, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, dbo.tblorders.dtrequestship, " & _
"dbo.tblorders.intcancelled , dbo.tblorders.inttranstype " & _
"HAVING (dbo.tblorders.idcustomer = '" & Me.lstcustomers & "') And (dbo.tblorders.intcancelled = 0) And (dbo.tblorders.inttranstype = 1)"
Me.lsthistory.RowSource = strSQL_OrderHistory
ASKER
I tried that, as soon as I put that in and run the form, the list box stays blank when I run the query. The form is in MS Access, Using Visual Basic to call this event on the "on click" of a different list box. Basically, click on the customer to the left, and the customers order history comes up on the list box on the right. To be safe, I copied your suggestion into it and still didn't work.
You can avoid this hassle by using a Continuous subform ...
mx
mx
ASKER
I know, I've got several different list boxes on several different tabs that get updated when a customer is selected. It seems to be much easier to deal with the list boxes than multiple continuous forms. I was just hoping there would be an easy fix. Thanks for any advise you can give....
Well, what Marcus posted should work ... you have to do it in a query. Then after you get it to display Currency, you get to figure out how to make it Right justified :-)
mx
mx
This should be in a way identical to the solution from Great (°v°):
Format(SUM(dbo.qry_custome r_followup _orderhist ory.[Line Total]), '#,#.00') AS [Order Total]
Format(SUM(dbo.qry_custome
ASKER
Here's the code I'm using. It works, then I add the Format....'currency' and it doesn't work. I'm confused.
Private Sub lstcustomers_Click()
Dim strSQL_OrderHistory As String, strSQL_ProductHistory As String, strSQL_FollowupHistory As String, strSQL_Locations As String
'Pass Thru Order History
strSQL_OrderHistory = "SELECT dbo.tblorders.idorder, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, Format(SUM(dbo.qry_customer_followup_orderhistory.[Line Total]),'Currency') AS [Order Total], " & _
"dbo.tblorders.dtrequestship , dbo.tblorders.idcustomer, dbo.tblorders.intcancelled, dbo.tblorders.inttranstype " & _
"FROM dbo.tblorders LEFT OUTER JOIN " & _
"dbo.qry_customer_followup_orderhistory ON dbo.tblorders.idorder = dbo.qry_customer_followup_orderhistory.idorder " & _
"GROUP BY dbo.tblorders.idorder, dbo.tblorders.idcustomer, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, dbo.tblorders.dtrequestship, " & _
"dbo.tblorders.intcancelled , dbo.tblorders.inttranstype " & _
"HAVING (dbo.tblorders.idcustomer = '" & Me.lstcustomers & "') And (dbo.tblorders.intcancelled = 0) And (dbo.tblorders.inttranstype = 1)"
Me.lsthistory.RowSource = strSQL_OrderHistory
End Sub
w/ $
Format(SUM(dbo.qry_custome r_followup _orderhist ory.[Line Total]), '$#,#.00') AS [Order Total]
Format(SUM(dbo.qry_custome
hoe about:
Format(SUM(Nz(dbo.qry_cust omer_follo wup_orderh istory.[Li ne Total],0)), '$#,0.00') AS [Order Total]
Format(SUM(Nz(dbo.qry_cust
ASKER
Last two still didn't work. I remove the changes, and it works.
Private Sub lstcustomers_Click()
Dim strSQL_OrderHistory As String, strSQL_ProductHistory As String, strSQL_FollowupHistory As String, strSQL_Locations As String
'Pass Thru Order History
strSQL_OrderHistory = "SELECT dbo.tblorders.idorder, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, Format(SUM(dbo.qry_customer_followup_orderhistory.[Line Total]), '$#,#.00') AS [Order Total], " & _
"dbo.tblorders.dtrequestship , dbo.tblorders.idcustomer, dbo.tblorders.intcancelled, dbo.tblorders.inttranstype " & _
"FROM dbo.tblorders LEFT OUTER JOIN " & _
"dbo.qry_customer_followup_orderhistory ON dbo.tblorders.idorder = dbo.qry_customer_followup_orderhistory.idorder " & _
"GROUP BY dbo.tblorders.idorder, dbo.tblorders.idcustomer, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, dbo.tblorders.dtrequestship, " & _
"dbo.tblorders.intcancelled , dbo.tblorders.inttranstype " & _
"HAVING (dbo.tblorders.idcustomer = '" & Me.lstcustomers & "') And (dbo.tblorders.intcancelled = 0) And (dbo.tblorders.inttranstype = 1)"
Me.lsthistory.RowSource = strSQL_OrderHistory
End Sub
Try creating a saved query with that SQL , then set the Row Source to that query - instead of doing the SQL in code.
mx
mx
Use a DLOOKUP to format the result. As I have elsewhere noted Access is quite annoying.
ASKER
I tried the Sum(Nz... it didn't work. I believe the code would work if I set the Row Source. However, I don't know how to feed the dynamic customer ID into the saved qry. That's why I resorted to using an event trigger in Visual basic.
ASKER
As for the DLookup, in my limited knowledge, I can only do a DLookup based on a saved qry, and I don't know how to pass the variable into the saved qry. Is this now extending outside the scope of my original question?
How are you seeing DLookup playing into this mix ?
ASKER
Can you do a Dlookup that references a save qry and pass a variable into it? (a variable into the saved queary? I haven't been able to do that?
Where is the 'variable' coming from? A form?
Ok ... I see
Me.lstcustomers
So ... that part of the WHERE clause would be Forms.YourFormName.lstCust omers
mx
Ok ... I see
Me.lstcustomers
So ... that part of the WHERE clause would be Forms.YourFormName.lstCust
mx
ASKER
Thanks, I'll mess around with a DLookup and get back to you. Thanks!
DLookup ??? What for? Use a saved query and reference the Form as I showed. DLookup will be a performance hit.
mx
mx
ASKER
Oh I see...sorry. I'll try it.
ASKER
It won't let me reference a form for the criteria in a saved query....gives me an error. HOWEVER -- I'm making this in Access with their stored procedures. Am I supposed to create a stored query outside of Access using only SQL? This is new to me...sorry.
" Am I supposed to create a stored query outside of Access using only SQL? "
No. Inside of Access, which doesn't have Stored Procedures, instead 'saved' queries.
Show me the SQL for the saved query.
mx
No. Inside of Access, which doesn't have Stored Procedures, instead 'saved' queries.
Show me the SQL for the saved query.
mx
ASKER
I'm sorry, I don't think I'm working in the right place: I put the reference to the form into the SQL and I get The column prefix Forms.frmcustomer_followup does not match with a table name or alias name used in the query."
I'm building these in the design view on access.
I'm building these in the design view on access.
SELECT dbo.tblorders.idorder, dbo.tblorders.dtordered, SUM(dbo.qry_customer_followup_orderhistory.[Line Total]) AS [Order Total],
dbo.tblorders.dtrequestship, dbo.tblorders.idcustomer, dbo.tblorders.intcancelled, dbo.tblorders.inttranstype
FROM dbo.tblorders LEFT OUTER JOIN
dbo.qry_customer_followup_orderhistory ON dbo.tblorders.idorder = dbo.qry_customer_followup_orderhistory.idorder
GROUP BY dbo.tblorders.idorder, dbo.tblorders.idcustomer, dbo.tblorders.chrponumber, dbo.tblorders.dtordered, dbo.tblorders.dtrequestship,
dbo.tblorders.intcancelled, dbo.tblorders.inttranstype
HAVING (dbo.tblorders.idcustomer = Forms.frmcustomer_followup.lstCustomers) AND (dbo.tblorders.intcancelled = 0) AND (dbo.tblorders.inttranstype = 1)
"The column prefix Forms.frmcustomer_followup does not match with a table name or alias name used in the query.""
I've never seen that error message. Are you in the Access query designer ?
mx
I've never seen that error message. Are you in the Access query designer ?
mx
ASKER
yes.
Well, sorry. I don't work with SQL .. so, maybe it's different. The SQL 'looks' ok ... that form reference and all.
Have you actually saved this query - so that you see it listed in the database window - in the queries tab?
mx
Have you actually saved this query - so that you see it listed in the database window - in the queries tab?
mx
ASKER
All the queries are saved on the SQL sever under the "Views" tab. I'm so sorry for the trouble. I've always wanted to be able to use the same save query, but pass a variable into it, but every time I've built one and referenced a form field for it's search criteria it barks at me. Never been able to pull it off.
"All the queries are saved on the SQL sever under the "Views" tab."
OK ... well, that's something completely different. Not likely that would work. Not sure why the formatting Marchus (Harfang) showed you doesn't work either. Weird.
Marcus ????
OK ... well, that's something completely different. Not likely that would work. Not sure why the formatting Marchus (Harfang) showed you doesn't work either. Weird.
Marcus ????
ASKER
I too, obviously am at a loss. it doesn't make sense. I sure appreciate your efforts. I've spent hours researching and have found the same solutions from other threads that you guys have offered me....but they don't work. Somethings getting lost between assigning the SQL statement to a Rowsource on a list box via a "on click" event.
It's too late for me to go into it but a DLOOKUP allows you to have a totally separate source for the data you want to obtain.
Tomorrow I'll check and see if you have found your answer.
After all, whatever works for you is all that matters.
I'm a little curious what answer works. This is a bit interesting.
Emil
Tomorrow I'll check and see if you have found your answer.
After all, whatever works for you is all that matters.
I'm a little curious what answer works. This is a bit interesting.
Emil
ASKER
Thanks...I'll look more into the DLookup option as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are awesome!!! Thank you!!!
You are welcome!
/gustav
/gustav
Great. I'm glad you got your answer.
Thanks Gustav.
mx
mx
Format(SUM(dbo.qry_custome
(°v°)