Prev: access external hard drive
Next: Problem exporting data to Excel using Access VBA
From: Daryl S on 7 Apr 2010 13:17
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


"johnboy7676(a)yahoo.com" 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(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: johnboy7676 on 7 Apr 2010 15:17
On Wed, 07 Apr 2010 11:02:16 -0600, John W. Vinson
<jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote:

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


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

Thanks,
John
From: johnboy7676 on 7 Apr 2010 15:18
On Wed, 7 Apr 2010 10:17:02 -0700, Daryl S
<DarylS(a)discussions.microsoft.com> 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
From: James A. Fortune on 7 Apr 2010 15:41
On Apr 7, 3:17 pm, johnboy7...(a)yahoo.com 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
MPAPoster(a)FortuneJames.com
From: John W. Vinson on 7 Apr 2010 15:54
On Wed, 07 Apr 2010 13:17:46 -0600, johnboy7676(a)yahoo.com wrote:

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

--

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