Calculated field (text) in query: (John Vinson)

[SMatthew]

Happy New Year John!
What I am trying to do is write an application - " To do
list for all employees according to the Priority
Level"(A,B,C.). When the task is completed the Priority
level has to be removed from that task and list has to be
reassigned according to the priority date.

For example:
Name priority date Priority Level
1. Tom 12/31/03 A
2. Tom 01/02/2004 B
3. Tom 01/04/04 C
4. Henry 01/02/04 A
5. Henry 01/05/04 B
So when Tom finishes his 1st task the priority level is
reassigned

Name priority date Priority Level Date Completed
1. Tom 12/31/03 12/31/2003
2. Tom 01/02/2004 A
3. Tom 01/04/04 B
4. Henry 01/02/04 A
5. Henry 01/05/04 B
In order to do this would I be able to do this as a
Calculated field in a query , if so how, If not please
would you be able to give me any suggestions.For now I
would like to handle just alphabet increment from A- Z.

Thanks in advance
SMatthew

-------------------------------------------------------
Subject: Re: Calculated field (text) in query
From: "John Vinson"
<jvinson@STOP_SPAM.WysardOfInfo.com> Sent: 12/31/2003
10:18:04 AM

In Access 2002 would you be able to use a calculated
field in a query and increment Alphabets (A,B,C...)

Please help!

You'ld need to use a custom VBA function. Could you post
some more
details? What exactly are you trying to accomplish? Will
there be more
than 26 records, and if so what do you want to see (AA,
AB, AC, ... or
what)?


..

 

[DanK]

The way I think I would deal with this problem is to use
NUMERICAL priority levels (hang on now, I'm not
finished). Then, you can simply add or subtract to adjust
levels. Then, have an internal CONVERSION from numbers TO
letters. This could be done in one of several ways:

1) Create a TABLE (called tPriorities) that has two
fields: PriorityL and PriorityN, where L is the letter
(from A to ZZZ if you want) and N is the KEY field - it's
numerical priority (1 to whatever). Then use drop down
controls on forms that DISPLAY the letters but have hidden
their numerical equivalancies. Access will use the
numbers and calculate on them, then display the
Alphabetical results for the users.

2) Use Controls that can use multiple columns like list
boxes, etc.

3) Have a conversion on a form or in code where everything
refers to it like: If PN = "1" then PA = 1 or use
constants. Many ways of solving this.

If you really HAVE to stay with using ONLY letters, (and I
highly doubt you HAVE to), maybe there's a way to deal
with it all using their ASCII equivalents, then add and
subtract that way.