Prev: access external hard drive
Next: Problem exporting data to Excel using Access VBA
From: johnboy7676 on 7 Apr 2010 11:49
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
From: johnboy7676 on 7 Apr 2010 11:53
Access 2003, by the way.............


On Wed, 07 Apr 2010 09:49:17 -0600, johnboy7676(a)yahoo.com wrote:

>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
From: J_Goddard via AccessMonster.com on 7 Apr 2010 12:11
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


johnboy7676(a)yahoo.com wrote:
>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 Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

From: johnboy7676 on 7 Apr 2010 12:21

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


On Wed, 07 Apr 2010 16:11:48 GMT, "J_Goddard via AccessMonster.com"
<u37558(a)uwe> wrote:

>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
>
>
>johnboy7676(a)yahoo.com wrote:
>>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
From: John W. Vinson on 7 Apr 2010 13:02
On Wed, 07 Apr 2010 09:49:17 -0600, johnboy7676(a)yahoo.com wrote:

>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.
--

John W. Vinson [MVP]
 |  Next  |  Last
Pages: 1 2 3
Prev: access external hard drive
Next: Problem exporting data to Excel using Access VBA