Numeric Field Overflow Problem

[Guest]

Hi

I have an Access dB that uses a linked Excel Spreadsheet. When we try to link in a spreadsheet, it sometimes formats certain columns as Number instead of Text. When I try run a query, it gives me the error, "Numeric Field Overflow.

Is there a way to convert those columns into Text when Access recognizes it? I have read there is a macro I could write, but am unsure how to do it. Any tips

Thanks
Matt

 

[Guest]

I've had the exact same problem w/ an Access/Excel app I made. I couldn't find a way in the Access side of things to coerce numeric values to be treated as text. I had to write a macro in Excel. I actually saved these macros in a seperate book, so that they're available to any sheets I need to do it to. (You just need to open the book with the macros in it, in addition to any other sheets you want to affect.)

Here are the macros:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddZip()
Dim C As Excel.Range
For Each C In Application.Selection.Cells

If IsNull(C.Value) Then

C.Value = ""

Else

C.Value = Trim(C.Value)

End If

Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
If IsNumeric(C.Value) Then

C.Value = CInt(C.Value)

End If
Next
End Sub


AddApostrophes is the macro that does what you'd want. To use it, just highlight the range you're interested in (I just click on the whole column) and run that macro. It takes any numeric value and adds an apostrophe (') in front of it. That will make Access treat it like text.

Unfortunately, that will ALSO make Excel treat it like text. Thus, RemoveApostrophes. It will simply reverse the process. AddZip deals with the problems I've encountered with spaces or nulls in Excel.

HTH

 

[Guest]

Great, thanks a lot

I really appreciate the help

Matt

 

[Guest]

You could use cstr(fieldname) in a query using the builder
to convert to a string

-----Original Message-----
Hi,

I have an Access dB that uses a linked Excel

Spreadsheet. When we try to link in a spreadsheet, it
sometimes formats certain columns as Number instead of
Text. When I try run a query, it gives me the
error, "Numeric Field Overflow."

Is there a way to convert those columns into Text when

Access recognizes it? I have read there is a macro I
could write, but am unsure how to do it. Any tips?