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]

One problem appears to be that you reference a field called [ProductCode]:

        DoCmd.OutputTo acOutputReport, strRptName, acFormatRTF, "C:\Users\userid\my documents\PT\Reports\" & [COLOR="#FF0000"]![ProductCode][/COLOR] & "_NDXPublicationAttributes.doc"

But there is no such field in your query (there is only [IDCode]):

strSQL = "Select [IDCode] From CorePData;"

 

[PatrickWW]

Both should be ProductCode. My apologies, it has been changed. It is still receiving errors when I run it. Do I have to add additional syntax to the first piece of code you posted to run the script?>

 

[xenou]

Not sure. I suggest your post your code as it is now. What is the error message?
ξ

 

[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 = "PTile Exports"
'strSQL = "Select [ProductCode] From CorePData;"
strSQL = "Select Format([ProductCode],"#.0000) 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] & "_FileName.doc"
        DoCmd.Close acReport, strRptName, acSaveNo
        .MoveNext
        Loop
    
    End With

MyRS.Close
Set MyRS = Nothing
End Sub

Thank you for hanging in there with me. I am trying to prevent the decimals from getting out of control and try to control that at this presentation layer. The raw SQL is pulling a ton of decimal places.

 

[xenou]

This line is not right:

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

It should be:

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

 

[PatrickWW]

X sorry for just getting back. It now seems to get hung up here

DoCmd.OpenReport strRptName, acViewPreview, , "[ProductCode]='" & ![ProductCode] & "'"

I am getting a run time error, stating Item not found in this collection. Thanks again for you time.

 

[xenou]

Post your complete code again, now that you've edited it again. You should do that every time - the problem is probably your SQL statement but I can't be sure because I don't know what happened to it after your last correction.
ξ

 

[PatrickWW]

X maybe my approach is wrong. The problem is I am importing data from MS SQL Server to Excel. In SQL the decimals are going out over 9 places. Well when I bring it into Excel I am having to convert the range to Text or else a lot of rows drop out into the Access error catching table. If I turn to text it will bring it into Access without errors. I'm thinking that maybe my problem. Any thoughts on where I should tackle this problem? At the database level when I write my SQL or application layer with Excel / Access? Any thoughts would be great. I am just trying not to reinvent the wheel here. Thanks again X Ill post the code in an hour or so. I gotta run to a meeting.

 

[james_lankford]

use the round function

both sql server and access have one

ROUND (Transact-SQL)

MS Access: Round Function

 

[xenou]

Try round as noted above. That should work in SQL server or in Access. You can/should also consider just formatting the report field in the report. That's usually as easy as right clicking on the field in design view and choosing a format. If however, the fields are being imported as text, then you can't round or format them (well, maybe you can format them like numbers - I'm not sure). So possibly you want to convert them back to numbers and then format them as numbers.

That said you should have no problems with importing SQL server number data. So there may be more problems here. Most likely, you have data in the range that isn't numeric. Which means your data is not clean. Garbage in, garbage out.