Creating temporary tables and formatting fields

Question

I have VBA code in Access 2010 that I am using to make a temporary table. The only problem is it rounds the dollar amount and I need it to show the whole number. In the code below how can I  format the Amount field to show a dollar amount.

"

Public Sub ViewActionsAlreadyInMaster()

    Dim strSQL As String

    Dim rs1 As DAO.Recordset

        strSQL = ""

        strSQL = strSQL & "SELECT "

        strSQL = strSQL & """"" AS [Bank Account] , "

        strSQL = strSQL & "0 AS [Check Number], "

        strSQL = strSQL & "0 as [Amount], "

        strSQL = strSQL & "INTO TEMPViewActionsAlreadyInMaster "

        DoCmd.RunSQL (strSQL)

        Set rs1 = CurrentDb.OpenRecordset("tblDBIndex")

        Do While Not rs1.EOF

                    strSQL = ""

                    strSQL = strSQL & "INSERT INTO TEMPViewActionsAlreadyInMaster "

                    strSQL = strSQL & "SELECT "

                    strSQL = strSQL & "[" & rs1("DBname") & " Master Issued Checks].[Bank Account], "

                    strSQL = strSQL & "[" & rs1("DBname") & " Master Issued Checks].[Check Number], "

                    strSQL = strSQL & "[" & rs1("DBname") & " Master Issued Checks].[Amount], "

                    strSQL = strSQL & "FROM tblDailyPaids, [" & rs1("DBname") & " Master Issued Checks] "

                    strSQL = strSQL & "WHERE "

                    strSQL = strSQL & "tblDailyPaids.[Check Number] = [" & rs1("DBname") & " Master Issued Checks].[Check Number] "

                    strSQL = strSQL & "AND tblDailyPaids.[Bank Account] = nz([" & rs1("DBname") & " Master Issued Checks].[Bank Account],0) "

                    strSQL = strSQL & "AND [" & rs1("DBname") & " Master Issued Checks].[ACTION] is not null; "

                    DoCmd.RunSQL (strSQL)

                    Debug.Print rs1("DBname")

            rs1.MoveNext

        Loop

    DoCmd.RunSQL ("DELETE FROM TEMPViewActionsAlreadyInMaster WHERE [Action Date] = #1/1/1900#;")

    DoCmd.OpenTable "TEMPViewActionsAlreadyInMaster", acViewNormal

End Sub"

Answer 1

OK I understand the loop now, but you can greatly simplify your code.

Dim strtablename As String

       Set rs1 = CurrentDb.OpenRecordset("tblDBIndex")

               rs1.MoveFirst

        Do While Not rs1.EOF

             strTablename = "[" & rs1("DBName") & " Master Issued Checks]"

              strSQL = "NSERT INTO TEMPViewActionsAlreadyInMaster " & _

                             "SELECT [Bank Account], [Check Number], .[Amount] " & _

                              "FROM tblDailyPaids, " & strtablename  & _

                              " WHERE tblDailyPaids.[Check Number] = " & strtablename & ".[Check Number] " & _

                              " AND tblDailyPaids.[Bank Account] = nz([" & strTablename & ".[Bank Account],0) " & _

                               strTablename & ".[ACTION] is not null; "

                CurrentDB.Execute strSQL, dbFailOnError

               rs1.Movenext

        Loop

Much cleaner, more readable, will execute without warnings and run faster.

Answer 2

Try changing this line:

strSQL = strSQL & "0.00 as [Amount], "

Because you didn't use a decimal Access probably create the field as an integer datatype.

However, I wouldn't do it that way. I would create a table with the datatypes I wanted.  Then, instead of using a Make Table query I would use:

strSQL = "DELETE * FROM TEMPViewActionsAlreadyInMaster;"

CurrentDB.Execute strSQL, dbFailOnError

instead of that whole section for the make table. This way you have the formatting you want. and you don't bloat the database with constantly making the same table. 

Also why are you looping thorugh a recordset? Why not just use:

INSERT INTO (field list)

SELECT field list FROM tablename

WHERE ....

Answer 3

Someone else wrote the code and the user wanted to add the amount to the table, so I was trying to do a quick fix. I will try your code when I get a minute. Thank you for all your help. :)

Answer 4

I tried the strSQL = strSQL &"0.00 as [Amount], " and it was still giving me the rounded number. I used your second suggestion and it works fine. The reason I am using a loop is because I have multiple tables that I am getting data from. Thank you so much. Thank you so much for your help.