Combine 12 seperate queries into 1

[Guest]

hello all,
I have an access 2002 project that shows projects by a release. The first 3
letters of the release indicate the month (JAN, FEB, MAR....). the kicker is
this is a text field with various characters after the first 3 letters that
have no relevance to a date format.
In my queries, I want to be able to sort the releases like it was a date
field. It is currently sorted as text since it is a text field.
My idea is to have a seperate table that appends each of these release
values in a table (tbl_Release_Sort) and also have a field titled Sort_Order.
Run 12 seperate queries to evaluate the first 3 characters and then update
the Sort_Order field. For example query 1 would select all records in
tbl_Release with JAN as the first 3 characters and update the filed
Sort_Order = "1". And then repeat this for each month of the year.
I could then join this Sort_Order field to my queries to properly sort my
queries.
Is this the best and easiest way? If so, what would the expression be to
evaluate just the first 3 letters of the field release?

 

[Duane Hookom]

You can sort by an expression like:
Mth: (InStr("JanFebMarAprMayJunJulAugSepOctNovDec",Left([Release],3))+2)/3

 

[Guest]

WOW,
This works perfectly. If you have time, I would love to know the syntax and
beginner's explanation of this.

Thank you very much!!!!!

You can sort by an expression like:
Mth: (InStr("JanFebMarAprMayJunJulAugSepOctNovDec",Left([Release],3))+2)/3

--
Duane Hookom
MS Access MVP
--

hello all,
I have an access 2002 project that shows projects by a release. The first
3
letters of the release indicate the month (JAN, FEB, MAR....). the kicker
is
this is a text field with various characters after the first 3 letters
that
have no relevance to a date format.
In my queries, I want to be able to sort the releases like it was a date
field. It is currently sorted as text since it is a text field.
My idea is to have a seperate table that appends each of these release
values in a table (tbl_Release_Sort) and also have a field titled
Sort_Order.
Run 12 seperate queries to evaluate the first 3 characters and then update
the Sort_Order field. For example query 1 would select all records in
tbl_Release with JAN as the first 3 characters and update the filed
Sort_Order = "1". And then repeat this for each month of the year.
I could then join this Sort_Order field to my queries to properly sort my
queries.
Is this the best and easiest way? If so, what would the expression be to
evaluate just the first 3 letters of the field release?

 

[Duane Hookom]

Starting from about the middle:

Left([Release],3) will return the first three characters of your Release
field. These should be Jan or Feb or Mar ... Dec.

Instr("BigString","LittleString") finds where the LittleString is found in
the BigString. In this case, where "Feb" is found in "JanFebMar...".

Therefore a month like Feb would return the value 4.

Add 2 to 4 and get 6. Divide 6 by three to get 2 which is the month number.

--
Duane Hookom
MS Access MVP
--

WOW,
This works perfectly. If you have time, I would love to know the syntax
and
beginner's explanation of this.

Thank you very much!!!!!

You can sort by an expression like:
Mth:
(InStr("JanFebMarAprMayJunJulAugSepOctNovDec",Left([Release],3))+2)/3

--
Duane Hookom
MS Access MVP
--

hello all,
I have an access 2002 project that shows projects by a release. The
first
3
letters of the release indicate the month (JAN, FEB, MAR....). the
kicker
is
this is a text field with various characters after the first 3 letters
that
have no relevance to a date format.
In my queries, I want to be able to sort the releases like it was a
date
field. It is currently sorted as text since it is a text field.
My idea is to have a seperate table that appends each of these release
values in a table (tbl_Release_Sort) and also have a field titled
Sort_Order.
Run 12 seperate queries to evaluate the first 3 characters and then
update
the Sort_Order field. For example query 1 would select all records in
tbl_Release with JAN as the first 3 characters and update the filed
Sort_Order = "1". And then repeat this for each month of the year.
I could then join this Sort_Order field to my queries to properly sort
my
queries.
Is this the best and easiest way? If so, what would the expression be
to
evaluate just the first 3 letters of the field release?

 

[John Vinson]

Mth: (InStr("JanFebMarAprMayJunJulAugSepOctNovDec",Left([Release],3))+2)/3

VERRRY clever Duane... <pickpocketing that from you for future use>

John W. Vinson[MVP]

 

[Duane Hookom]

I suppose this might also work:
Mth: InStr("xxJanFebMarAprMayJunJulAugSepOctNovDec",Left([Release],3))/3

Feel free to steal my expressions, especially this one {:-{P
--
Duane Hookom
MS Access MVP
--

Mth: (InStr("JanFebMarAprMayJunJulAugSepOctNovDec",Left([Release],3))+2)/3

VERRRY clever Duane... <pickpocketing that from you for future use>

John W. Vinson[MVP]