I'm sorry, I am asking you to please be more active
in finding the solution to your problem.
In my second response you will find...
***quote***
{do you know what I mean?
in query design of append query,
you can have Access show you the
results just like an ordinary SELECT
query by clicking on "View/Datasheet"
in top menu...these are the results you
will be appending into your table}
***unquote***
Was this not clear enough to help you
see the results of your append query?
I had proposed that if
Required = yes
or
Allow Zero Length = no
for a field in the append-to table
that you are sending a "blank" value
to in your append query,
that may be why you are getting validation
rule violations....I could be wrong.
You have identified that....
Required is no in all 11 fields.
Allow Zero Length is no in all 11 fields.
So we know it is not the Required property.
Did you try changing Allow Zero Length to Yes
for all the fields in your append-to table, then
attempt to run the append query?
Did the violations then go away?
If so, then we are on the right track.
If you can identify the fields that might
be "blank," I showed you an example of
the second "option to change the SELECT
clause of your append query to change
zero-length text field values to something meaningful"
****quote****
In the {append}query Design grid, it might look something like:
Field: JobTitle
Table: sometable
Sort:
Append To: JobTitle
Criteria:
Or:
change it to:
Field: IIF(Len([JobTitle] & "") > 0, [JobTitle], "N/A")
Table: sometable
Sort:
Append To: JobTitle
Criteria:
Or:
***unquote***
if JobTitle was "blank", it will append "N/A"
if JobTitle is not "blank," it will append value
of JobTitle
that's not high-fallutin' code,
it is just the IIF() function.
In your append query, it could be that
"Field:" is not "JobTitle"
(obviously, "Table:" is not "sometable")
but there should be a column in the grid
for
"Append To: JobTitle"
Was that a reason for you not trying this
possible solution?
In the append query Design grid, it might look something like:
Field: somefield
Table: sometable
Sort:
Append To: JobTitle
Criteria:
Or:
change it to:
Field: IIF(Len([somefield] & "") > 0, [somefield], "N/A")
Table: sometable
Sort:
Append To: JobTitle
Criteria:
Or:
faxylady said:
Here is a further reply to your response.
Another option is to change the SELECT clause of your append query to change
zero-length text field values to something meaningful (or to just NULL).
Please consider that I might not understand what you mean by Change the
SELECT clause of your append query to change zero-length text field values to
something meaningful (or to just NULL). I have no idea what the SELECT
clause of the append query is...
for example, I asked you to look at the results of your append query (like
it were just a select query) and identify fields in the results where "there
is nothing."
Please consider that I might not know how to look at the results of my
append query (like it were just a select query) and identify fields in the
results where "there is nothing." I need this to be broken down further. It
might sound dumb to you, but I DO NOT KNOW PROGRAMMING CODE.
My original training in Access came from the Thompson netG courses. I
completed the entire set through Expert User. Then I read Roger Jennings
book, Access 2000, Special Edition. I have also had some of the 599cd
courses through intermediate, which believe it or not, are quite good. In
addition, I have had some other supplemental CD training in Access which
helps.
So, please forgive me for being so dumb and stupid and not understanding
some of the replies I have received. Besides, when I said some of the
replies I have received were not satisfactory to me, that was not directed at
YOU. Over the last few months, I have received replies that I was not able
to understand and just gave up for the time being. Then I came back here
later for further resolution.
Thank you.
Gary Walter said:
An easy test would be to change
"Allow Zero Length"
to "Yes" for all fields
and run your append.....
Another option is to change the SELECT
clause of your append query to change
zero-length text field values to something
meaningful (or to just NULL).
I mean no offense but I have to say something:
the people who help on this newsgroup
do it for free.
so instead of spending time with their families,
they are giving their time and expertise away for
free to help people solve their Access problems.
there's something wrong with them. they have
a bad gene that causes them to want to help
people.
they take great pleasure in helping someone
see the light.
they especially like to help posters
"who help themselves."
but statements like:
"The responses I got before were not satisfactory to me."
leave a terrible taste in the mouth of
some who help here for free.
I understand it can be frustrating sometimes.....
problems get solved best when both parties
are actively involved in finding the solution.
for example, I asked you to look at the results
of your append query (like it were just a select
query) and identify fields in the results where
"there is nothing."
It is a valid option for the web selection tool
(or Outlook) to provide a zero-length field value
for a field if it finds no data for that field.
Your active goal is to first identify those fields
where this might occur, then provide a
workaround.
One workaround is to set "Allow Zero Length"
to "Yes" for those fields.
Another workaround is to change the field value
within your append query so you will never be trying
to append a zero length string to a field.
For example, there may be a "JobTitle" field in
the append query that when you look at the datasheet
of your append query you see some "blanks."
In the query Design grid, it might look something like:
Field: JobTitle
Table: sometable
Sort:
Append To: JobTitle
Criteria:
Or:
change it to:
Field: IIF(Len([JobTitle] & "") > 0, [JobTitle], "N/A")
Table: sometable
Sort:
Append To: JobTitle
Criteria:
Or:
This immediate if (IIF) tests if the length of
the field is greater than 0 (and is not NULL).
If it is, it returns the JobTitle.
If it is not, it returns "N/A"
Again, I meant no offense by earlier remarks.
Good luck.
I'm going to spend some time with my family today...
:
Thanks for breaking that down.
If a field is type "Number," the bottom grid
will only show "Required"
If a field is type "Text," the bottom grid
will show both "Required" and "Allow Zero Length."
All of the 11 fields are text, no number fields.
If a field has "Required = Yes"
and you are trying to append a NULL value
into that field, then you will be "violating a validation rule"...
If a field has "Allow Zero Length = No"
and you are trying to append a NULL (or zero-length) value
into that field, then you will be "violating a validation rule"...
Required is no in all 11 fields.
Allow Zero Lenght is no in all 11 fields.
I might be trying to append a NULL (or zero-length) value into that field.
What do you mean by this?
These contacts were obtained by use of a web contact selection tool that
enables you to select a large number of contacts at once then
automatically
exports them to Outlook. Of course, once they are in Outlook, I import
them
into Access. I have another tool that does essentially the same thing
with
business cards. With their program, I have had no problems. Could it
possibly be the web selection tool has something in their programming that
is
causing this? If so, I will inform them of this.
