468,110 Members | 1,879 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,110 developers. It's quick & easy.

Force "Insert Into" to use default values

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

Oct 10 '07 #1
2 3119
On Oct 10, 3:58 am, "paulquinlan...@hotmail.com"
<paulquinlan...@hotmail.comwrote:
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)"
Oct 10 '07 #2
On Wed, 10 Oct 2007 06:56:15 -0700, OldPro <rr*******@sbcglobal.net>
wrote:
>On Oct 10, 3:58 am, "paulquinlan...@hotmail.com"
<paulquinlan...@hotmail.comwrote:
>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!
Oct 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by newbie_mw | last post: by
1 post views Thread by newbie_mw | last post: by
6 posts views Thread by ewpatton | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.