Converting text to number type

[togo]

Ok so I imported a comma separated table into Access. All went well but in the report we want to have it rounded to two decimals, but Access only sees it as text. If the table is imported through the wizard and type is set to number, it just blanks out all the fields. I assume there's a way to do this using VBA?

[alansidman]

you could create an update query using the Val function. Alternatively, you could add an additional column (field) to a query and put the Val function in an expression and use the new field as a record source for your report.

Look here for how the val function works

http://www.techonthenet.com/access/f...umeric/val.php

[JoeM]

I don't understand why you cannot get the data to import properly.
Can you post an example of one of the rows of the CSV file you are trying to import?

[togo]

Here's the subroutine I try to use, it doesn't work.

Private Sub Command40_Click()
Dim db As Object
    Dim db1 As Object
    Dim db2 As Object
    Dim db3 As Object
    Set dbVideoCollection = CurrentDb
    Set db1 = dbVideoCollection.OpenRecordset("numbers")
    Set db3 = db1.Fields
    ' Scan the records from beginning to each
    While Not db1.EOF
        '
        For Each db2 In db1.Fields
           
               
                    ' then change its value
                    db1.Edit
                    db1("GradeID").Value = Val(db1.[GradeID].Value)
                    '[GradeID].NumberFormat = "general"
                    db1.Update
        Next
       ' Move to the next record and continue the same approach
        db1.MoveNext
    Wend
stDocName = "Report"
DoCmd.OpenReport stDocName, acPreview
End Sub

says "Invalid use of Null"

I have a query which uses GETCSWord to break up the CSV file.

Step 1: CSV file comes from machine
Step 2: Access opens CSV file and uses VBA to open a session of Excel to save it into generic 2003 XLS file which Access can understand
Step 3: Access opens this file as a linked file, and the above VBA code is applied to multiple columns in order to reduce the decimal count to two where necessary.

Alternatively, simply importing the XLS file and telling the import wizard that a column is integers, such as GRADE ID, blanks out the entire column on import.

(example)DMS-CCRL,I121000068,Drawing Number,mk-50-PA2039,description,shawntestflange,,,303,0,304,0,, ,,,,,,,,,ND,,ND,, 18.38, 0.20, 1.71, 0.10, 70.86, 0.45,ND,, 8.35, 0.19, 0.4040, 0.0497,ND,,ND,,ND,, 0.2893,(/example)

Grade ID in the above line is 303.

[JoeM]

You should be able to import the CSV directly into Access using the TransferText Action. You can use it in a Macro or VBA.
Or, you can just manually import the CSV file and invoke the Import Wizard.

[togo]

That worked, but need to be able to import the file dynamically (the file will be different each time the database is opened). Dragging the imported table into the macro area just gives me the DoCmd.OpenTable. Should be easy to figure out how to do this, having a bit of a brain dead morning. Thanks for the help.

[JoeM]

How is the file different each time?
Is it just that the data is different (no big deal), or is the structure (i.e. number of fields) different each time?

[togo]

no just the data is different each time.

[JoeM]

Great, then it should be pretty easy.

The first thing you want to do is create an Import Specification. This is pretty easy. Just go through the process of manually importing the CSV file using the Import Wizard. In the Wizard, go through and format each field the way it needs to be. Then, just before clicking "Finish", click Advanced, and then "Save As". This is saving the Import Specification. Give it any name you like.

Now, you can create a Macro to import files using the TransferText Action. Use the name of the Import Specification you created in that argument. If you would like to make it more dynamic, set up one example in the Macro, then save the Macro, and then run the "Convert Macros to Visual Basic" utility. This will give you the VBA you need, and you can make your file names more dynamic.

[togo]

strange, is the transfertext action available only in 2007? I run 2010.

[JoeM]

strange, is the transfertext action available only in 2007? I run 2010.

Nope, you have it too. Access has decided, by default, to hide some of the Actions for Security reasons.
Just click on the "Show All Actions" icon on the Show/Hide ribbon and you will see it.

[togo]

ah. It's called ImportExportText in 2010 fyi. Thank you

[JoeM]

Interesting, it looks like Access 2010 may have both:
http://answers.microsoft.com/en-us/o...d-ea9b585fb8a9
Not sure what the rationale for that is.

Anyway, if you do try converting it to VBA, you have have to use TransferText (see the "Note" at bottom of this link: http://msdn.microsoft.com/en-us/libr.../ff192475.aspx).