Assistance with Decimal places in VBA script

[PatrickWW]

Private Sub Command0_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
strRptName = "PT Exports"
strSQL = "Select [IDCode] From CorePData;"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    With MyRS
    
        Do While Not MyRS.EOF
        DoCmd.OpenReport strRptName, acViewPreview, , "[ProductCode]='" & ![ProductCode] & "'"
        DoCmd.OutputTo acOutputReport, strRptName, acFormatRTF, "C:\Users\userid\my documents\PT\Reports\" & ![ProductCode] & "_NDXPublicationAttributes.doc"
        DoCmd.Close acReport, strRptName, acSaveNo
        .MoveNext
        Loop
    
    End With

MyRS.Close
Set MyRS = Nothing
End Sub

I am generating an SQL report which has a ton of trailing zero's and I was wondering if there was a way to eliminate those at the presentation layer. I read it's a big no-no to do that at the Database level (SQL) Any assistance would be greatly appreciated. This code copies the table and coverts each record into an RTF document. I am not against this going into an CSV or XLSX file, but the big piece is getting the leading zero's to slim down to 4 decimal places to the right. (.0000) no more than that. FYI this is in Access 2010

 

[xenou]

Hi,
Maybe in the SQL you can do this:

strSQL = "Select Format([IDCode],"#.0000") From CorePData;"

 

[PatrickWW]

Thanks for the code, however it seems to still bomb out. If you can think of anything else please let me know. I really appreciate it!

 

[xenou]

Hi,
What kind of data is [IDCode]?
ξ

 

[PatrickWW]

I'm not sure I follow you. The code is a series of number 7 digits always.

 

[xenou]

Is the code a *real* number or a number stored as text? If they are real numbers, do they have decimals (floating point numbers) or are they whole numbers (integers)?


Note:

Thanks for the code, however it seems to still bomb out.

Bomb out how - do you mean still has many trailing zeros?

 

[PatrickWW]

Not accepting the syntax. Compile Error Expected: End of Statement. The cursor lands on the # sign.

 

[xenou]

Okay,
sorry. Should be in VBA:

strSQL = "Select Format([IDCode],""#.0000"") From CorePData;"

 

[james_lankford]

maybe use the
round function
MS Access: Round Function
and try to round to 2 decimal places

 

[PatrickWW]

X thanks got me through part of the code however in the With MYRS docmd I am getting syntax errors. It seems like it needs to be changed in the bottom half of the script as well. Sorry I'm just starting to learn VBA. Kind regards