UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Formating A Calculated Value In A Query Created Using Vba    
 
   
Kamulegeya
post Apr 8 2012, 02:08 PM
Post #1

UtterAccess Ruler
Posts: 1,292
From: Kampala,Uganda The Pearl of Africa
Hello UA

I have an SQL string with a calculated field. I export the results to Excel. i want to format the calculated value.

Here is the code.(part of it)

CODE
Private Sub cmdBalance_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
On Error GoTo myErr
Select Case Me.Frame4.Value
Case Is = 1
strSQL = "SELECT qryQuarterlyRF.Description, qryQuarterlyRF.ActivityName," & _
        " qryQuarterlyRF.CategoryName, qryQuarterlyRF.Aproved_Amount, qryQuarterlyRF.Q1, " & _
        "Nz[qryQuarterlyRF]![Aproved_Amount],0)-Nz([qryQuarterlyRF]![Q1],0) AS Balance" & _
        " From qryQuarterlyRF " & _
        " WHERE (((qryQuarterlyRF.Quarter)=1));"        

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("qryQBalance", strSQL)
DoCmd.OutputTo acOutputQuery, "qryQBalance", "ExcelWorkbook(*.xlsx)", "", True, "", 0, acExportQualityPrint
myExit:
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryQBalance"
Set qdf = Nothing
Set db = Nothing
myErr:
MsgBox " Error" & Err.Description
Resume myExit

End Sub


The part i want formatted( Currency formatting) in SQL is this one

CODE
"Nz[qryQuarterlyRF]![Aproved_Amount],0)-Nz([qryQuarterlyRF]![Q1],0) AS Balance" & _




Ronald
Go to the top of the page
 
+
theDBguy
post Apr 8 2012, 02:20 PM
Post #2

Access Wiki and Forums Moderator
Posts: 48,059
From: SoCal, USA
Hi Ronald,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Have you tried something like?

Format(Nz(qryQuarterlyRF.Approved_Amount,0)-Nz(qryQuarterlyRF.Q1,0), "Currency") As Balance

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Go to the top of the page
 
+
Kamulegeya
post Apr 8 2012, 02:32 PM
Post #3

UtterAccess Ruler
Posts: 1,292
From: Kampala,Uganda The Pearl of Africa
QUOTE (theDBguy @ Apr 8 2012, 10:20 PM) *
Hi Ronald,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Have you tried something like?

Format(Nz(qryQuarterlyRF.Approved_Amount,0)-Nz(qryQuarterlyRF.Q1,0), "Currency") As Balance

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)



Hello Dbguy.

I am using Access 2007.

Tried your suggestion but it is not compiling.

can an SQL string work with out the quotes(")?

Ronald
Go to the top of the page
 
+
theDBguy
post Apr 8 2012, 02:33 PM
Post #4

Access Wiki and Forums Moderator
Posts: 48,059
From: SoCal, USA
Try using single quotes instead (forgot that you're using this in VBA).

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
Kamulegeya
post Apr 8 2012, 02:40 PM
Post #5

UtterAccess Ruler
Posts: 1,292
From: Kampala,Uganda The Pearl of Africa
QUOTE (theDBguy @ Apr 8 2012, 10:33 PM) *
Try using single quotes instead (forgot that you're using this in VBA).

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)



Hello Dbguy

Even single quotes dont compile.

Ronald
Go to the top of the page
 
+
tina t
post Apr 8 2012, 02:48 PM
Post #6

UtterAccess Ruler
Posts: 1,820
From: SoCal, USA
QUOTE
strSQL = "SELECT qryQuarterlyRF.Description, qryQuarterlyRF.ActivityName," & _
" qryQuarterlyRF.CategoryName, qryQuarterlyRF.Aproved_Amount, qryQuarterlyRF.Q1, " & _
"Nz[qryQuarterlyRF]![Aproved_Amount],0)-Nz([qryQuarterlyRF]![Q1],0) AS Balance" & _
" From qryQuarterlyRF " & _
" WHERE (((qryQuarterlyRF.Quarter)=1));"

PMFJI, guys. Ronald, above is the SQL statement you originally posted. i noticed that the bolded NZ function is incorrect - it's missing the opening perens. if you didn't fix that already, you'll want to do so or the query won't run even after the code compiles.

hth
tina
Go to the top of the page
 
+
Kamulegeya
post Apr 8 2012, 02:59 PM
Post #7

UtterAccess Ruler
Posts: 1,292
From: Kampala,Uganda The Pearl of Africa
QUOTE (tina t @ Apr 8 2012, 10:48 PM) *
PMFJI, guys. Ronald, above is the SQL statement you originally posted. i noticed that the bolded NZ function is incorrect - it's missing the opening perens. if you didn't fix that already, you'll want to do so or the query won't run even after the code compiles.

hth
tina



Thank you Tina t

This one has worked

CODE
strSQL = "SELECT qryQuarterlyRF.Description, qryQuarterlyRF.ActivityName," & _
        " qryQuarterlyRF.CategoryName, qryQuarterlyRF.Aproved_Amount, qryQuarterlyRF.Q1, " & _
        " Format(Nz([qryQuarterlyRF]![Aproved_Amount],0)-Nz([qryQuarterlyRF]![Q1],0),'Currency') AS Balance" & _
        " From qryQuarterlyRF " & _
        " WHERE (((qryQuarterlyRF.Quarter)=1));"



Ronald
Go to the top of the page
 
+
theDBguy
post Apr 8 2012, 03:51 PM
Post #8

Access Wiki and Forums Moderator
Posts: 48,059
From: SoCal, USA
Hi Ronald,

Glad to hear you got it to work but I don't understand. It looks like you used the single quote suggestion I made earlier; but back then, you said it didn't work. (IMG:style_emoticons/default/iconfused.gif)
Go to the top of the page
 
+
Kamulegeya
post Apr 9 2012, 12:50 AM
Post #9

UtterAccess Ruler
Posts: 1,292
From: Kampala,Uganda The Pearl of Africa
QUOTE (theDBguy @ Apr 8 2012, 11:51 PM) *
Hi Ronald,

Glad to hear you got it to work but I don't understand. It looks like you used the single quote suggestion I made earlier; but back then, you said it didn't work. (IMG:style_emoticons/default/iconfused.gif)



Hello Dbguy

I think it was not working because i did not include the opening quotes...i was using

CODE
Format(Nz([qryQuarterlyRF]![Aproved_Amount],0)-Nz([qryQuarterlyRF]![Q1],0),'Currency') AS Balance & _


Then tried

CODE
" Format(Nz([qryQuarterlyRF]![Aproved_Amount],0)-Nz([qryQuarterlyRF]![Q1],0),'Currency') AS Balance" & _



which worked

Of course got it working because of your advice

Thank you so much

Ronald
Go to the top of the page
 
+
theDBguy
post Apr 9 2012, 10:57 AM
Post #10

Access Wiki and Forums Moderator
Posts: 48,059
From: SoCal, USA
Hi Ronald,

(IMG:style_emoticons/default/yw.gif)

That makes sense now. Good luck with your project.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 06:53 PM