Setting #,##0.00 format in SQL statement

[antomack]

I'm creating forms to allow users create queries on the fly allowing them to select the fields they want to appear. When they select the fields it creates the SQL and then uses this to create a querydef which then open to display the results.

I want to have the number fields formatted as #,##0.00 but if I use the format function to achieve this it appears as text with everything to the left of the field rather than to the right as with numbers.

Does anyone know of a way to create the querydef so the number fields are formatted as I want, being standard number with 2 decimal places.

I'm using 97 so I don't have FormatNumber available to use, unless someone knows how to implement it in 97.

 

[SforSoftware]

Hi,

Why do you want to get the right format in the query?
When it's set well in the table, the query will present it as well. Else you can set it in your 'presentation medium', form or report

Greetz

 

[pdx_man]

Like Software mentioned, have you tried setting the property Format to Standard in the underlying table? The query should inherit that format.

 

[antomack]

Thanks for the suggestions but the format on the table is already set to Standard with 2 decimal places which is not coming over to the query. The data is not being output to a form or report so can't use this to set the format, user just wants it to display as a table which they can then copy to excel or wherever so I need the formatting to be done in the query.

Any other ideas out there?

 

[namliam]

Right click on the column top of the cell column you wish to format.

Click properties

Set your format.

Done...

Regards

 

[antomack]

namliam,

That suggestion would be fine if I was not rebuiling the query each time depending on what the user selects from the form provided to them. The user selects the fields required which creates the required SQL, then use this to create a querydef, then open the query to show results. I want the query to open with the correctly formatted numbers without requiring any further user interaction with the query, so need to apply the formatting in the SQL. The code behind the run button is as follows but the format treats the numbers as text and they appear left aligned rather than right aligned.

What I want to know is, is there any other means of formatting the fields within the SQL that will give a number with the format of #,##0.00?

Private Sub cmdRun_Click()
On Error GoTo Err_cmdRun_Click

  Dim strSQL As String, strEnd As String, strQueryName As String
  Dim db As Database
  Dim qdf As QueryDef

  Set db = CurrentDb

  strSQL = "SELECT NewReportBaseData.Regn, NewReportBaseData.Region_desc, NewReportBaseData.Master_Con,"
  strSQL = strSQL + " NewReportBaseData.Consultant_name, NewReportBaseData.NewBusGrp, NewReportBaseData.AgencyName"
  If Me.chkAgNo = True Then
    strSQL = strSQL + ", NewReportBaseData.AgencyNo"
    strEnd = ", NewReportBaseData.AgencyNo"
  End If
  If Me.chkCurYr = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2003),'#,##0.00') AS APE_CurPTD"
  End If
  If Me.chkCurYr1 = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2002),'#,##0.00') AS APE_PTD1"
  End If
  If Me.chkCurYr2 = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2001),'#,##0.00') AS APE_PTD2"
  End If
  If Me.chkCurYr3 = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2000),'#,##0.00') AS APE_PTD3"
  End If
  If Me.chkFY1 = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2002_FY),'#,##0.00') AS APE_FY1"
  End If
  If Me.chkFY2 = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2001_FY),'#,##0.00') AS APE_FY2"
  End If
  If Me.chkFY2 = True Then
    strSQL = strSQL + ", Format(Sum(NewReportBaseData.APE_2000_FY),'#,##0.00') AS APE_FY3"
  End If
  strSQL = strSQL + " FROM NewReportBaseData"
  If chkLiveOnly = True Then
    strSQL = strSQL + " WHERE (((NewReportBaseData.[Live@wk25]) = Yes))"
  End If
  strSQL = strSQL + " GROUP BY NewReportBaseData.Regn, NewReportBaseData.Region_desc,"
  strSQL = strSQL + " NewReportBaseData.Master_Con, NewReportBaseData.Consultant_name,"
  strSQL = strSQL + " NewReportBaseData.NewBusGrp, NewReportBaseData.AgencyName"
  If Not IsNull(Me.cmbRegion) Then
    strEnd = strEnd + " HAVING ((NewReportBaseData.Regn)='" & Me.cmbRegion & " ')"
  End If
  If Not IsNull(Me.cmbMBC) Then
    strEnd = strEnd + " HAVING ((NewReportBaseData.Master_Con)='" & Me.cmbMBC & "');"
  Else
    strEnd = strEnd + ";"
  End If
  strSQL = strSQL + strEnd

  strQueryName = "BCPanelRep"
  DoDelQuery strQueryName
  Set qdf = db.CreateQueryDef(strQueryName, strSQL)
  DoCmd.OpenQuery qdf.Name
  Set db = Nothing
  
Exit_cmdRun_Click:
    Exit Sub

Err_cmdRun_Click:
    MsgBox Err.Description
    Resume Exit_cmdRun_Click
    
End Sub

 

[Tiro Cupidus]

Assuming the current format returned is like ######, this might work:

LEFT(Sum(NewReportBaseData.APE_2003),1) + "," + MID(Sum(NewReportBaseData.APE_2003),2,3) + "." + RIGHT(Sum(NewReportBaseData.APE_2003),2)

If that's not exactly what you need, you may be able to use something similar. There's probably a better way to fix it too. =)