If you cannot change it you need to convert it to a number for purposes of
the calculation, provided it can be interpreted as a number:
Chr(Val([inv_cm#])+64)
However, if that is the invoice number you cannot use Chr, for reasons
described in the previous post.
Information about functions, including Chr and Val, are in the Help files.
At 3:40 PM yesterday (it was my second posting in this thread) I posted a
way to do exactly what you want. It was generic SQL because I didn't know
what determines the order of the records within the groups. I have asked
you to post the table structure, and I have asked for information about what
determines the sort order within groups. Without that information there is
no way to proceed. Please review the posts and note the information I have
requested. Assume I had a good reason for asking.
Bob sg said:
I did have the missing bracket. I just didn't have it in this message.
Sorry
about that. The Data Type in the table for the inv_cm# is text and this
can
not be changed. My question is: How can I add a different letter after the
invoice number for each of the invoices that are listed more than one time
in
my query.
Invoices
72680959
72680959
72680959
72690774
72690774
72690774
72690774
72690773
72690773
I need it to look like:
72680959a
72680959b
72680959c
72690774a
72690774b
72690774c
72690774d
72690773a
72690773b
BruceM said:
You're missing the beginning square bracket:
Chr([inv_cm#]+64)
Other than that, is inv_cm# an integer or long field in the query? Can
you
select it as a field by itself? Also, Chr only works with numbers up to
255. I suggested it as a way to convert numbers 1-26 into letters.
Your original question was how to group by invoice number in a query, and
how to number within each group. If the question is now something else,
be
specific.
In the design view in the query, I'm putting the Chr(inv_cm#]+65) in
the
field box.
:
I tried putting in Chr([inv_cm#]+65) and I got this error: #error. All
my
invoices start with 6 or a 7. Not sure what I'm doing wrong.
:
Help has more information about the Chr function. Essentially,
characters
may be identified by a number. To go to the immediate code window,
open any
database and press Ctrl + G. In the Immediate window type:
?Chr(65)
The letter A will appear. Chr(66) = B, etc., then Chr(96) is a.
To see the code for a character, in the immediate window type:
?Asc("A")
This should return 65.
If you have a record with the number 1 in a field, Chr([SomeField] +
64)
will return the letter A. This is part of what John showed in his
example
with the use of Chr(iChr).
A ranking query is one name for a query that starts with this:
1000
1000
1000
1006
1006
1010
1012
1012
1012
and returns this:
1000 1
1000 2
1000 3
1006 1
1006 2
1010 1
1012 1
1012 2
1012 3
It can be used for other types of sorting, such as arranging runners
by
their race times and numbering the records starting with 1. One of
the
assumptions with the example above is that there is a reason for
numbering
the "1000" records 1, 2, and 3. What places them in that order in
the
query? A date? A number field?
I will leave you to John's part of the thread since you indicated
you
are
getting close with his suggestion, which is quite different from
mine.
I have a query that our system generates so we can invoice our
customers.
Somtimes we bill mulitple lines because the customer wants a
breakdown of
the
material that we are billing for and sometimes the material is
listed
more
than once so the line item would be the same. I tried to do the
Chr
function,
but I'm not sure how to use it. And I'm not sure what a ranking
query
is.
:
I have provided SQL for a ranking query, and have asked several
questions
about the data. You have given no indication you have made any
attempt
to
implement the suggestions, and you have not answered the
questions.
If
each
record has the same data there is no basis for ranking. Even if
you
could
assign numbers the "ranking" would be arbitrary and meaningless.
Each record would have the same data, but I wouldnt be adding
new
data
to
the
queary. Do you know the VBA code I could use on this.
:
The only way to do this is manually, UNLESS you have some way
to
distinguish the several records from each other.
Is there a datetime field for each of the invoice records that
is
different for the group? Or some other way you can uniquely
sort
them
in
order.
72661020
72661020
72661020
72661020
Or you could use VBA code to step through an ordered recordset
to
do
this. That would fix the current records, but would not take
care of
records that would be added.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Bob sg wrote:
Could you show me how I should use the chr function in a
query
to
put a
"A","B","C", etc. after the invoice number. There is no
primary
key
in
this
table just to let you know.
Invoice Number
72661020
72661020
72661020
72661020
