Converting the “#Num!” value in Access

[ciarra41]

Hi,

When I link to an excel document at times it shows this in Access, (“#Num!”) in one of the fields. I was able to find this code in excel to convert the column to its correct format before I link it.

Sub Addspace()
Dim cell As Object

For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next

End Sub

Now I would like to do this in Access without doing it in excel. How can I convert this to do this in a append query using a separate module then I’ll call the function using the appended query to convert the data to append into a new table.

 

[RivetHed]

Am I right in thinking you just want to convert this sub into a function that you can use in a query?

If so:

Public Function Addspace(InputString as String)

Inputstring = " " & Inputstring
addspace = right(inputstring,len(inputstring)-1)

end function

 

[PWise]

try this

insert into tablename (field1,field2,....)
SELECT [worksheetname$].column1.[worksheetname$].column2,.....
FROM [worksheetname$] IN '' [Excel 8.0;imex=0;DATABASE=c:\foldername\filename.xls]

[code]