query won't append fields with spaces to text field

[Guest]

I have an Access2000 database that we have been using for several years. One
user got a new XP computer running Access 2003. Everything seems to work fine
except for one append query. The query gets information for orders based on
input order numbers and appends the information to a table. I have admin
privileges and other users only have user privileges. When I run the query on
my machine signed in as any user the query works. When I run the query from
another users machine signed in as myself or any user, the query will get the
information but will not append it to the table.
I've discovered that my machine creates blank text fields with nulls. Other
users machines seem to create blank text fields with spaces. Records with
nulls will append, but records with spaces will not. I don't understand why
the same query creates different data on different machines, nor why records
with spaces will not add spaces to text fields.

 

[Michel Walsh]

Hi,


Jet does not consider that a null is equal to another null under a
constraint of "no duplicated value", but definitively a one character sting,
made up of a space, IS equal to another one length character string made of
a space. You *probably* have a no-dup constraint on the said field.


I have no real explanation about the difference in settings, ... have
you check Tools | Options | Tables/Queries [ SQL Server Compatible Syntax
(ANSI 92) just in case it may part of your problem?



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

These fields are not indexed. I am capturing order information and the
fields that create problems are address lines 2 and 3 and shipping
instruction lines 1 & 2. These may or may not have anything in them.

I looked at the table/queries options, but I didn't see anything there about
server compatability.

Is there a setting somewhere that sets the default value for a text field to
null or space? I can't find anything like that.

The query I'm using has a table and two pass-thru queries as the basis.
Would there be something in the pass-thru query setup? Is there a way to set
a default value on a pass-thru query?

Hi,


Jet does not consider that a null is equal to another null under a
constraint of "no duplicated value", but definitively a one character sting,
made up of a space, IS equal to another one length character string made of
a space. You *probably* have a no-dup constraint on the said field.


I have no real explanation about the difference in settings, ... have
you check Tools | Options | Tables/Queries [ SQL Server Compatible Syntax
(ANSI 92) just in case it may part of your problem?



Hoping it may help,
Vanderghast, Access MVP

I have an Access2000 database that we have been using for several years.
One
user got a new XP computer running Access 2003. Everything seems to work
fine
except for one append query. The query gets information for orders based
on
input order numbers and appends the information to a table. I have admin
privileges and other users only have user privileges. When I run the query
on
my machine signed in as any user the query works. When I run the query
from
another users machine signed in as myself or any user, the query will get
the
information but will not append it to the table.
I've discovered that my machine creates blank text fields with nulls.
Other
users machines seem to create blank text fields with spaces. Records with
nulls will append, but records with spaces will not. I don't understand
why
the same query creates different data on different machines, nor why
records
with spaces will not add spaces to text fields.

 

[Chris2]

I have an Access2000 database that we have been using for several years. One
user got a new XP computer running Access 2003. Everything seems to work fine
except for one append query. The query gets information for orders based on
input order numbers and appends the information to a table. I have admin
privileges and other users only have user privileges. When I run the query on
my machine signed in as any user the query works. When I run the query from
another users machine signed in as myself or any user, the query will get the
information but will not append it to the table.
I've discovered that my machine creates blank text fields with nulls. Other
users machines seem to create blank text fields with spaces. Records with
nulls will append, but records with spaces will not. I don't understand why
the same query creates different data on different machines, nor why records
with spaces will not add spaces to text fields.

kessmiller,

You mentioned: "I've discovered that my machine creates blank text
fields with nulls. Other users machines seem to create blank text
fields with spaces."

I can't be certain, but it sounds to me like an attribute of the
destination table has somehow become changed.

Can you check the "Required" attribute of the offending column or
columns in the table in question on both a working and the not-working
machines?


Sincerely,

Chris O.

 

[Michel Walsh]

Hi,


If there is a pass-through query, then maybe the setting is different on the
foreign server. Is it the same "catalog", the same user?

It is not usual, with Jet, to get empty string in place of Null.


Hoping it may help,
Vanderghast, Access MVP

These fields are not indexed. I am capturing order information and the
fields that create problems are address lines 2 and 3 and shipping
instruction lines 1 & 2. These may or may not have anything in them.

I looked at the table/queries options, but I didn't see anything there
about
server compatability.

Is there a setting somewhere that sets the default value for a text field
to
null or space? I can't find anything like that.

The query I'm using has a table and two pass-thru queries as the basis.
Would there be something in the pass-thru query setup? Is there a way to
set
a default value on a pass-thru query?

Hi,


Jet does not consider that a null is equal to another null under a
constraint of "no duplicated value", but definitively a one character
sting,
made up of a space, IS equal to another one length character string made
of
a space. You *probably* have a no-dup constraint on the said field.


I have no real explanation about the difference in settings, ... have
you check Tools | Options | Tables/Queries [ SQL Server Compatible
Syntax
(ANSI 92) just in case it may part of your problem?



Hoping it may help,
Vanderghast, Access MVP

I have an Access2000 database that we have been using for several years.
One
user got a new XP computer running Access 2003. Everything seems to
work
fine
except for one append query. The query gets information for orders
based
on
input order numbers and appends the information to a table. I have
admin
privileges and other users only have user privileges. When I run the
query
on
my machine signed in as any user the query works. When I run the query
from
another users machine signed in as myself or any user, the query will
get
the
information but will not append it to the table.
I've discovered that my machine creates blank text fields with nulls.
Other
users machines seem to create blank text fields with spaces. Records
with
nulls will append, but records with spaces will not. I don't understand
why
the same query creates different data on different machines, nor why
records
with spaces will not add spaces to text fields.

 

[Guest]

I looked on both machines and none of these fields were set as required nor
could I find anywhere to set a default for a text field to spaces or null.
What I ended up doing was adding an IIF statement to all my text fields -
IIF([textfield] > ' ',[textfield],null). This still doesn't answer what
changed and why it suddenly won't work, but it gets us back to where my user
can do her job without having to call me 10 times a day.

 

[Chris2]

I looked on both machines and none of these fields were set as required nor
could I find anywhere to set a default for a text field to spaces or null.
What I ended up doing was adding an IIF statement to all my text fields -
IIF([textfield] > ' ',[textfield],null). This still doesn't answer what
changed and why it suddenly won't work, but it gets us back to where my user
can do her job without having to call me 10 times a day.

:

kessmiller,

Well, one common error in moving an mdb is broken library links.
(Even though it doesn't sound like this is that type of problem, you
might as well check.)

Open the VBE, and go to Tools > References.

Are there any?



Also, when you say, "nor could I find anywhere to set a default".

That is strange. "Default Value" is an attribute on the General tab
of each column in a table's Design View.


Sincerely,

Chris O.