field Null but not Null

[johnboy7676]

Don't really know how to explain this, but here goes. Have a table
with fields [BLOCK], [NUMBER], and [ALPHA]. [ALPHA] can be null.
These are concantenated together to form Acct# (block & "-" & number &
alpha). Example, 9-152 is different account from 9-152G.

I append block billing charges to a temp table for review, and then
post (append) those charges to transaction table. But, here is
strange thing. When I run a totals query, sometimes there are two
records instead of one.

For example; acct# 9-152 might show as:
Acct SumOfTransactions
9-152 $20.00
9-152 $15.00

instead of just:
9-152 $35.00

Access sees "something" in the Alpha field sometimes. In example
above, if I bring up those two records, and put cursour in the Alpha
field and backspace, or tab into field and delete, then Access will
still see them as two records. But, if I put something in the Alpha
field, save record, go back and delete out what I just put in, then
Access now considers them the same and will give me just one total.

If I run a query using Is Null in Alpha field, then Access returns
both records, so I don't understand why Access then returns two
records in a totals query. (leaving Acct# out of the query still
returns 2 records, btw)

It only happens sometimes. For example, out of approx 800 records I
just appended, there were 32 of these, where the totals query came up
with two different records rather than one.

I'm stumped.........Anybody ever hear of anything like this?

by the way, the block, number, alpha are used for sorting and
differenct charge levels. Since the Alpha field can be blank, I didn't
use this as a composite PK, but rather the Acct# is the PK.

Thanks for any help.
John

 

[johnboy7676]

Access 2003, by the way.............

 

[johnboy7676]

Thanks, but the Fields are "defined" in a master acct table, such that
there are 4 fields (among others); BLOCK, NUMBER,ALPHA, ACCTNUM
so that the concatenation occurs when Acct# is first created. Later,
when fillinjg the temp table with billing charges, it is appending
those Fields, and since ALPHA is Null in these cases, whatever ACCTNUM
is doesn't really matter...........I think.

Maybe I should try as you say, or get rid of ACCTNUM, make ALPHA
required field, put in empty string, or zero and then in report don't
display zero. Make composite PK. I just thought it very strange.
John

Hi -

Untested, but try concatenating a blank or zero-length string instead of a
null when Alpha is Null. Use the Nz function, like this:

Acct# : block & "-" & number & nz(alpha," ")

John

Don't really know how to explain this, but here goes. Have a table
with fields [BLOCK], [NUMBER], and [ALPHA]. [ALPHA] can be null.
These are concantenated together to form Acct# (block & "-" & number &
alpha). Example, 9-152 is different account from 9-152G.

I append block billing charges to a temp table for review, and then
post (append) those charges to transaction table. But, here is
strange thing. When I run a totals query, sometimes there are two
records instead of one.

For example; acct# 9-152 might show as:
Acct SumOfTransactions
9-152 $20.00
9-152 $15.00

instead of just:
9-152 $35.00

Access sees "something" in the Alpha field sometimes. In example
above, if I bring up those two records, and put cursour in the Alpha
field and backspace, or tab into field and delete, then Access will
still see them as two records. But, if I put something in the Alpha
field, save record, go back and delete out what I just put in, then
Access now considers them the same and will give me just one total.

If I run a query using Is Null in Alpha field, then Access returns
both records, so I don't understand why Access then returns two
records in a totals query. (leaving Acct# out of the query still
returns 2 records, btw)

It only happens sometimes. For example, out of approx 800 records I
just appended, there were 32 of these, where the totals query came up
with two different records rather than one.

I'm stumped.........Anybody ever hear of anything like this?

by the way, the block, number, alpha are used for sorting and
differenct charge levels. Since the Alpha field can be blank, I didn't
use this as a composite PK, but rather the Acct# is the PK.

Thanks for any help.
John

 

[John W. Vinson]

Don't really know how to explain this, but here goes. Have a table
with fields [BLOCK], [NUMBER], and [ALPHA]. [ALPHA] can be null.
These are concantenated together to form Acct# (block & "-" & number &
alpha). Example, 9-152 is different account from 9-152G.

I append block billing charges to a temp table for review, and then
post (append) those charges to transaction table. But, here is
strange thing. When I run a totals query, sometimes there are two
records instead of one.

For example; acct# 9-152 might show as:
Acct SumOfTransactions
9-152 $20.00
9-152 $15.00

instead of just:
9-152 $35.00

Access sees "something" in the Alpha field sometimes. In example
above, if I bring up those two records, and put cursour in the Alpha
field and backspace, or tab into field and delete, then Access will
still see them as two records. But, if I put something in the Alpha
field, save record, go back and delete out what I just put in, then
Access now considers them the same and will give me just one total.

Check the properties of the Alpha field. Is its "Allow Zero Length" property
set to Yes? If so, it may contain either a NULL - undefined, no contents - or
a zero length string - a precisely defined string value "". They'll be seen as
different!

It's also possible that you have some other nonprinting character. Try
including a calculated field Len([ALPHA]) in a query to see if there is
something other than null in the field. If there is you can use the Asc()
function to get its ASCII value.

 

[Daryl S]

Where and how is the acctnum created? This may be the source of your issue.
I would check the code that creates the ACCTNUM and fix it. That will
prevent future issues from coming up.

Back up your database, then clean up the existing data.

You should clean up your ACCTNUM data by trimming off any extra spaces at
the end of the string. It could be that ALPHA is null, but your ACCTNUM is
not. This would need to be cleaned up in both the master acct table and in
the billing charges table (and anywhere else the ACCTNUM is used).

Alternatively, do not use ACCTNUM from the tables at all, but 'build' that
value each time, and group on the built value.

--
Daryl S

Thanks, but the Fields are "defined" in a master acct table, such that
there are 4 fields (among others); BLOCK, NUMBER,ALPHA, ACCTNUM
so that the concatenation occurs when Acct# is first created. Later,
when fillinjg the temp table with billing charges, it is appending
those Fields, and since ALPHA is Null in these cases, whatever ACCTNUM
is doesn't really matter...........I think.

Maybe I should try as you say, or get rid of ACCTNUM, make ALPHA
required field, put in empty string, or zero and then in report don't
display zero. Make composite PK. I just thought it very strange.
John

Hi -

Untested, but try concatenating a blank or zero-length string instead of a
null when Alpha is Null. Use the Nz function, like this:

Acct# : block & "-" & number & nz(alpha," ")

John

Don't really know how to explain this, but here goes. Have a table
with fields [BLOCK], [NUMBER], and [ALPHA]. [ALPHA] can be null.
These are concantenated together to form Acct# (block & "-" & number &
alpha). Example, 9-152 is different account from 9-152G.

I append block billing charges to a temp table for review, and then
post (append) those charges to transaction table. But, here is
strange thing. When I run a totals query, sometimes there are two
records instead of one.

For example; acct# 9-152 might show as:
Acct SumOfTransactions
9-152 $20.00
9-152 $15.00

instead of just:
9-152 $35.00

Access sees "something" in the Alpha field sometimes. In example
above, if I bring up those two records, and put cursour in the Alpha
field and backspace, or tab into field and delete, then Access will
still see them as two records. But, if I put something in the Alpha
field, save record, go back and delete out what I just put in, then
Access now considers them the same and will give me just one total.

If I run a query using Is Null in Alpha field, then Access returns
both records, so I don't understand why Access then returns two
records in a totals query. (leaving Acct# out of the query still
returns 2 records, btw)

It only happens sometimes. For example, out of approx 800 records I
just appended, there were 32 of these, where the totals query came up
with two different records rather than one.

I'm stumped.........Anybody ever hear of anything like this?

by the way, the block, number, alpha are used for sorting and
differenct charge levels. Since the Alpha field can be blank, I didn't
use this as a composite PK, but rather the Acct# is the PK.

Thanks for any help.
John

.

 

[johnboy7676]

Don't really know how to explain this, but here goes. Have a table
with fields [BLOCK], [NUMBER], and [ALPHA]. [ALPHA] can be null.
These are concantenated together to form Acct# (block & "-" & number &
alpha). Example, 9-152 is different account from 9-152G.

I append block billing charges to a temp table for review, and then
post (append) those charges to transaction table. But, here is
strange thing. When I run a totals query, sometimes there are two
records instead of one.

For example; acct# 9-152 might show as:
Acct SumOfTransactions
9-152 $20.00
9-152 $15.00

instead of just:
9-152 $35.00

Access sees "something" in the Alpha field sometimes. In example
above, if I bring up those two records, and put cursour in the Alpha
field and backspace, or tab into field and delete, then Access will
still see them as two records. But, if I put something in the Alpha
field, save record, go back and delete out what I just put in, then
Access now considers them the same and will give me just one total.

Check the properties of the Alpha field. Is its "Allow Zero Length" property
set to Yes? If so, it may contain either a NULL - undefined, no contents - or
a zero length string - a precisely defined string value "". They'll be seen as
different!

It's also possible that you have some other nonprinting character. Try
including a calculated field Len([ALPHA]) in a query to see if there is
something other than null in the field. If there is you can use the Asc()
function to get its ASCII value.

Yes, it was set to "Allow Zero Length". I'll change that and see if
the problem reoccurs.

Thanks,
John

 

[johnboy7676]

Where and how is the acctnum created? This may be the source of your issue.
I would check the code that creates the ACCTNUM and fix it. That will
prevent future issues from coming up.

Back up your database, then clean up the existing data.

You should clean up your ACCTNUM data by trimming off any extra spaces at
the end of the string. It could be that ALPHA is null, but your ACCTNUM is
not. This would need to be cleaned up in both the master acct table and in
the billing charges table (and anywhere else the ACCTNUM is used).

Alternatively, do not use ACCTNUM from the tables at all, but 'build' that
value each time, and group on the built value.

I think "this" problem was that it was set to allow zero length, but I
agree that in general maybe I'd better rethink my method.

Thanks, John

 

[James A. Fortune]

It's also possible that you have some other nonprinting character. Try
including a calculated field Len([ALPHA])  in a query to see if there is
something other than null in the field. If there is you can use the Asc()
function to get its ASCII value.

Yes, it was set to "Allow Zero Length".  I'll change that and see if
the problem reoccurs.

Thanks,
John

Would that be considered an Alpha Blocker ?

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

Yes, it was set to "Allow Zero Length". I'll change that and see if
the problem reoccurs.

You'll also need to update the table replacing "" with Null in the records
which have it, if you do so.

 

[Bob Quintal]

(e-mail address removed) wrote in

I think "this" problem was that it was set to allow zero length,
but I agree that in general maybe I'd better rethink my method.

Thanks, John

What I would suggest is that the field be left at 'allow zero
length', set the default to "" (the empty string), and set
'required' to Yes, The required property prohibits nulls.

Then I would create an unique index on the three fields. You might
even set it to Primary key.