Force "Insert Into" to use default values

[paulquinlan100]

Hi

I'm using automation in excel to insert a lot of data into an Access
DB. Its all working fine, apart from the fact that the data entry
people occasionally put "n/a" in the cells instead of 0, which
obviously access doesnt like being put into a number field.

I know i can put if statements around every variable to check, but is
there a way to force it to just put the default value for that
particular field (mostly 0, but sometimes it may be different).

Thanks for any suggestions
Paul

[OldPro]

On Oct 10, 3:58 am, "[e-mail]"
<[e-mail]:

Hi

I'm using automation in excel to insert a lot of data into an Access
DB. Its all working fine, apart from the fact that the data entry
people occasionally put "n/a" in the cells instead of 0, which
obviously access doesnt like being put into a number field.

I know i can put if statements around every variable to check, but is
there a way to force it to just put the default value for that
particular field (mostly 0, but sometimes it may be different).

Thanks for any suggestions
Paul

What do you mean be default value? You mean if the field has
gibberish, put 0 or some other one-size-fits empty value? How about
something like:
"Select iif(isNumeric([Amount]),[Amount],0) AS Amount2, field2,field3

>From someTable)"

[Chuck Grimsby]

On Wed, 10 Oct 2007 06:56:15 -0700, OldPro <rr*******@sbcglobal.net>
wrote:

>On Oct 10, 3:58 am, "[e-mail]"
<[e-mail]:

>Hi

I'm using automation in excel to insert a lot of data into an Access
DB. Its all working fine, apart from the fact that the data entry
people occasionally put "n/a" in the cells instead of 0, which
obviously access doesnt like being put into a number field.

I know i can put if statements around every variable to check, but is
there a way to force it to just put the default value for that
particular field (mostly 0, but sometimes it may be different).

Thanks for any suggestions
Paul

What do you mean be default value? You mean if the field has
gibberish, put 0 or some other one-size-fits empty value? How about
something like:
"Select iif(isNumeric([Amount]),[Amount],0) AS Amount2, field2,field3

>>From someTable)"

You need to be =very= careful with the IsNumberic function. It
doesn't always work as expected.

IsNumeric("($1,23,,3.4,,,5,,E67$)") = True
(My thanks to Rick Rothstein for this excellent example.)

Basically, I'll only use IsNumberic when checking a single character.

A better way to do this is to use a function:
Function IsDigitsOnly(NumberIn As Variant) As Boolean
IsDigitsOnly = (NumberIn Like String$(Len(NumberIn), "#"))
End Function
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!