By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

Force "Insert Into" to use default values

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.