If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
field Null but not Null
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 |
#2
|
|||
|
|||
field Null but not Null
|
#4
|
|||
|
|||
field Null but not Null
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@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 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 |
#5
|
|||
|
|||
field Null but not Null
|
#6
|
|||
|
|||
field Null but not Null
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 " wrote: 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@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 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 . |
#7
|
|||
|
|||
field Null but not Null
On Wed, 07 Apr 2010 11:02:16 -0600, John W. Vinson
wrote: On Wed, 07 Apr 2010 09:49:17 -0600, 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. Yes, it was set to "Allow Zero Length". I'll change that and see if the problem reoccurs. Thanks, John |
#8
|
|||
|
|||
field Null but not Null
On Wed, 7 Apr 2010 10:17:02 -0700, Daryl S
wrote: 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 |
#9
|
|||
|
|||
field Null but not Null
On Apr 7, 3:17*pm, wrote:
On Wed, 07 Apr 2010 11:02:16 -0600, John W. Vinson 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 |
#10
|
|||
|
|||
field Null but not Null
|
|
Thread Tools | |
Display Modes | |
|
|