Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format Currency in List Box using RowSource

Posted on 2008-06-10
36
Medium Priority
?
992 Views
Last Modified: 2008-06-11
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

Open in new window

0
Comment
Question by:opus111
[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
  • 16
  • 11
  • 3
  • +3
36 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 21756389
A listbox understands only text. You need to format your field in your query, e.g.

Format(SUM(dbo.qry_customer_followup_orderhistory.[Line Total]), 'Currency') AS [Order Total]

(°v°)
0
 

Author Comment

by:opus111
ID: 21756408
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.
0
 
LVL 75
ID: 21756462
You can avoid this hassle by using a Continuous subform ...

mx
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:opus111
ID: 21756475
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....
0
 
LVL 75
ID: 21756485
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 21756504
This should be in a way identical to the solution from Great (°v°):
Format(SUM(dbo.qry_customer_followup_orderhistory.[Line Total]), '#,#.00') AS [Order Total]
0
 

Author Comment

by:opus111
ID: 21756505
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

Open in new window

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 21756507
w/ $

Format(SUM(dbo.qry_customer_followup_orderhistory.[Line Total]), '$#,#.00') AS [Order Total]
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 21756515
hoe about:

Format(SUM(Nz(dbo.qry_customer_followup_orderhistory.[Line Total],0)), '$#,0.00') AS [Order Total]
0
 

Author Comment

by:opus111
ID: 21756518
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

Open in new window

0
 
LVL 75
ID: 21756524
Try creating a saved query with that SQL , then set the Row Source to that query - instead of doing the SQL in code.

mx
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 21756534
Use a DLOOKUP to format the result. As I have elsewhere noted Access is quite annoying.
0
 

Author Comment

by:opus111
ID: 21756537
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.
0
 

Author Comment

by:opus111
ID: 21756542
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?
0
 
LVL 75
ID: 21756553
How are you seeing DLookup playing into this mix ?
0
 

Author Comment

by:opus111
ID: 21756564
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?  
0
 
LVL 75
ID: 21756574
Where is the 'variable' coming from?  A form?

Ok ... I see

 Me.lstcustomers

So ... that part of the WHERE clause would be Forms.YourFormName.lstCustomers

mx
0
 

Author Comment

by:opus111
ID: 21756601
Thanks, I'll mess around with a DLookup and get back to you.  Thanks!
0
 
LVL 75
ID: 21756612
DLookup ???  What for?  Use a saved query and reference the Form as I showed.  DLookup will be a performance hit.

mx
0
 

Author Comment

by:opus111
ID: 21756622
Oh I see...sorry.  I'll try it.
0
 

Author Comment

by:opus111
ID: 21756663
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.
0
 
LVL 75
ID: 21756692
" 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
0
 

Author Comment

by:opus111
ID: 21756736
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.



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)

Open in new window

0
 
LVL 75
ID: 21756756
"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
0
 

Author Comment

by:opus111
ID: 21756762
yes.
0
 
LVL 75
ID: 21756770
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
0
 

Author Comment

by:opus111
ID: 21756786
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.
0
 
LVL 75
ID: 21756823
"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 ????
0
 

Author Comment

by:opus111
ID: 21756842
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.
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 21756914
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
0
 

Author Comment

by:opus111
ID: 21756926
Thanks...I'll look more into the DLookup option as well.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1200 total points
ID: 21757645
> I can't get the Sum figure outputed as Order Total to be formatted into Currency.

This works fine here:

SELECT
  Format(Sum(tblTest.Amount),'currency') AS Total,
  tblTest.CustomerId
FROM
  tblTest
GROUP BY
  tblTest.CustomerId;

so Format is not the issue. However, your dbo.prefix of the table names indicate a pass-through query; thus you must use the syntax of SQL Server, like:

SELECT
  CAST(Sum(dbo.tblTest.Amount) AS money) AS Total,
  dbo.tblTest.CustomerId
FROM
  dbo.tblTest
GROUP BY
  dbo.tblTest.CustomerId;

/gustav
0
 

Author Comment

by:opus111
ID: 21757668
You are awesome!!! Thank you!!!

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 21757699
You are welcome!

/gustav
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 21757770
Great. I'm glad you got your answer.
0
 
LVL 75
ID: 21761361
Thanks Gustav.

mx
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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