UtterAccess Discussion Forums > Formating A Calculated Value In A Query Created Using Vba

Full Version: Formating A Calculated Value In A Query Created Using Vba

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

Kamulegeya

Apr 8 2012, 02:08 PM

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

theDBguy

Apr 8 2012, 02: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...

Kamulegeya

Apr 8 2012, 02:32 PM

QUOTE (theDBguy @ Apr 8 2012, 10:20 PM)

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

theDBguy

Apr 8 2012, 02:33 PM

Try using single quotes instead (forgot that you're using this in VBA).

Just my 2 cents...

Kamulegeya

Apr 8 2012, 02:40 PM

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

Hello Dbguy

Even single quotes dont compile.

Ronald

tina t

Apr 8 2012, 02:48 PM

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

Kamulegeya

Apr 8 2012, 02:59 PM

QUOTE (tina t @ Apr 8 2012, 10:48 PM)

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

theDBguy

Apr 8 2012, 03: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.

Kamulegeya

Apr 9 2012, 12:50 AM

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.

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

theDBguy

Apr 9 2012, 10:57 AM

Hi Ronald,

That makes sense now. Good luck with your project.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.