Problem sorting numeric / non-numeric quote numbers

[Dominic Hamblin]

I have been asked to create a database for our sales team, the way they
currently display there quote numbers is with 4 numbers followed by 2-3
initials and then finally the date. e.g. 0012dh99. I am have a lot of trouble
trying to sort this can anyone give some advice as i wish to have an easier
quote numbering system in the db but cant sort it to begin giving the first
old quote its new number.

 

[Allen Browne]

With the leading zeros and fixed number of digits/characters, that should
sort okay.

You might consider breaking this into 3 fields though:
- a number field for the number;
- a Text field for the initials.
- a number field for the year.
You can then manipuate this data easily. This approach meets the design goal
of making every field atomic (i.e. each field contains only 1 piece of data,
not 3 different pieces of information.)

For display purposes (e.g. to show the number on a report the way the sales
team wants it printed), you could set the Control Source of a text box to
something like this:
=Format([NumPart], "0000") & [Initials] & Format(Year([QuoteDate]), "00")

If you are asked to assign the next available number for the person and year
when a person enters a new quote, you could do that in the BeforeUpdate
event of your form:

Private Sub Form_BeforeUpdate
Dim strWhere As String
If IsNull(Me.Initials) Or IsNull(Me.QuoteDate) Then
Cancel = True
MsgBox "Initials and quote date required"
ElseIf Me.NewRecord Then
strWhere = "([Initials] = """ & Me.Initials & """) AND
(Year([QuoteDate]) = " & Year(Me.QuoteDate) & ")"
Me.NumPart = Nz(DMax("NumPart", "QuoteTable", strWhere),0) + 1
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Wayne Morgan]

How do you want it sorted? By the year first, then the number, then the
initials or something else? Do the numbers get larger year to year or do
they start over each year? Is the "date" always two digits for the year?

 

[Dominic Hamblin]

I would like to sort it by the year firstly and then by the numbers upfront
because every year they start at 0001 so i when i sort i end up getting 0001
then the first set of initials in alphabetical order then the date 00 when
the 1st date inputed should show up as 0001/xx/94

 

[Wayne Morgan]

Sorting by the year first without using 4 digits will be difficult, but not
impossible. Allen's suggestion of 3 fields, sorting as needed on each field
is a good one. You could then concatenate them together for display
purposes.

By your description, the leading numbers and initials should sort correctly
if the year was removed and you want to sort by the year first. Without
breaking this into more than one field in the table, you will need to use
calculated fields in the query to break it into more than one field then
sort on those fields. Since these fields will be for sorting only, you could
uncheck the "Show" box for these fields in the query design grid. Remove and
sorting you have on the field itself. Let the calculated fields to the
sorting, the field itself though is the one you will display (the "Show" box
will be checked).

For the year field:
YearSort:IIf(Right([FieldName], 2) > 50, Right([FieldName], 2) + 1900,
Right([FieldName], 2) + 2000)

Set Sort to Ascending. If you have years older than 1951 then you will need
to adjust the ">50" portion to the oldest year you have. Without you
supplying 4 digits, an assumption has to be made as to when to assume "19"
and "20" as the first two digits of the year.

For the rest of the sort:
RestSort:Left([FieldName], Len([FieldName])-2)

Set Sort to Ascending. This removes the right two numbers from the end.
Since it uses the Len() function, it will allow for 2 or 3 initials.

Place these two calculated fields in the query with the first one to the
left of the second one. Access will sort the fields in the order listed,
from left to right.

 

[Dominic Hamblin]

Thanks wayne i will give that a try. thanks for the time taken to respond

Sorting by the year first without using 4 digits will be difficult, but not
impossible. Allen's suggestion of 3 fields, sorting as needed on each field
is a good one. You could then concatenate them together for display
purposes.

By your description, the leading numbers and initials should sort correctly
if the year was removed and you want to sort by the year first. Without
breaking this into more than one field in the table, you will need to use
calculated fields in the query to break it into more than one field then
sort on those fields. Since these fields will be for sorting only, you could
uncheck the "Show" box for these fields in the query design grid. Remove and
sorting you have on the field itself. Let the calculated fields to the
sorting, the field itself though is the one you will display (the "Show" box
will be checked).

For the year field:
YearSort:IIf(Right([FieldName], 2) > 50, Right([FieldName], 2) + 1900,
Right([FieldName], 2) + 2000)

Set Sort to Ascending. If you have years older than 1951 then you will need
to adjust the ">50" portion to the oldest year you have. Without you
supplying 4 digits, an assumption has to be made as to when to assume "19"
and "20" as the first two digits of the year.

For the rest of the sort:
RestSort:Left([FieldName], Len([FieldName])-2)

Set Sort to Ascending. This removes the right two numbers from the end.
Since it uses the Len() function, it will allow for 2 or 3 initials.

Place these two calculated fields in the query with the first one to the
left of the second one. Access will sort the fields in the order listed,
from left to right.

--
Wayne Morgan
MS Access MVP

I would like to sort it by the year firstly and then by the numbers upfront
because every year they start at 0001 so i when i sort i end up getting
0001
then the first set of initials in alphabetical order then the date 00 when
the 1st date inputed should show up as 0001/xx/94