Access import of tab delimited text file

[vamoose]

I have a text file I am importing into Access 2003. There are numbers that sometimes appear as 62.00 and other times they appear as "62.00" This cannot be helped. The data imports fine into Access all into text columns. I then format the column using VBA to number, double and the numbers like "62.00" are deleted. My question is how to search for and eliminate the quotation marks leaving only the number behing before I reformat the column. Column name = Shortage

Thank you for the help

 

[Remou]

You could use a query:

Val("62.00")

Results in 62

So does

Val(62.00)

 

[PHV]

SQL code:

UPDATE yourTable SET Shortage=Val(Mid(Shortage,2)) WHERE Shortage Like '"*"'

Remou, the problem is that Val(Chr(34) & "62.00" & Chr(34)) results in 0 ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vamoose]

Like so: DoCmd.RunSQL "Update SAP_Import set Field8 = Val(Field8)"

 

[PHV]

I'd use this instead:

DoCmd.RunSQL "Update SAP_Import set Field8=Val(Mid(Field8,2)) WHERE Field8 Like '""*""'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886