How can I assign a letter code to a date?

M

Michael

I have to assign the following numbers to represent a date I have a table
as follows, In need for example for today's date, 02112005 = LBAABLLE

1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
0 L

Any help would be appricated.
Michael
 
B

Brendan Reynolds

Public Function DateToChars(dtmDate As Date) As String

Dim astrCodes(9) As String
Dim strDate As String
Dim lngChar As Long
Dim strOutput As String

astrCodes(0) = "L"
astrCodes(1) = "A"
astrCodes(2) = "B"
astrCodes(3) = "C"
astrCodes(4) = "D"
astrCodes(5) = "E"
astrCodes(6) = "F"
astrCodes(7) = "G"
astrCodes(8) = "H"
astrCodes(9) = "I"
strDate = Format$(dtmDate, "ddmmyyyy")
For lngChar = 1 To Len(strDate)
strOutput = strOutput & astrCodes(Val(Mid$(strDate, lngChar, 1)) Mod
10)
Next lngChar
DateToChars = strOutput

End Function

Example of use, in the Immediate window ...

? datetochars(date())
LBAABLLE
 
M

Michael

Thank you Brendan, I have not used access in awhile and have forgotten what
little I knew.

I have copied and pasted the code you were so kind to create for me into
the visual basic. When I did so the line = strOutput &
astrCodes(Val(Mid$(strDate, lngChar, 1)) Mod 10) was wrapped at the mod 10
point and it was red. I brought the 10 up to the mod and the red went away,
so I hope this is what I should have done.

I then put the "? datetochars(date())" in the default value of a text box.
This did work and I am not sure where I should have put this.

I need to print this code on a form and do not really need to save it as the
number code can always be read from the original date.
Can you please point me in the right direction?
Michael
 
B

Brendan Reynolds

First question, yes, the code was originally on one line, Outlook Express
wrapped it when I posted it to the newsgroup.

Second point, the "?" is a shortcut for 'print' that is used in the VBA
Immediate window (usually just for testing/debugging purposes) to use the
function as the Control Source or Default Value of a text box, replace the
"?" with an "=", like so ...

= DateToChars(Date())

It was an interesting question, it's not often I get the chance to play with
arrays these days, thanks! :)
 
M

Michael

Thanks that did it.. if that was fun for you I just posted another in the
queries as I did not know where else to post it. I think it is a similar
answer as this but it is way beyond my feeble brain,


I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so today
being wed. would be 4403. Is there a simple way to do this?

thank you again for the help.
Michael
 
M

Michael

Hello Brendan,
I just found out that we will not need this, so I did not want you to spend
time on an answer that will not be needed.. thank you for your help.
Michael
 
B

Brendan Reynolds

Well, as it happens, I didn't need to spend much time on it, but thanks for
your consideration, anyhow! :)
 
M

Michael

Did you get it? If so, I can use it as the default and they can change it
when needed... If you did the work and don't mind sharing, I would be happy
to see it.. if for no other reason than to try to understand it
Michael
 
B

Brendan Reynolds

I was referring to my earlier post in this thread, Michael - check out the
DatePart function. But I see someone has answered - in rather more detail!
:) in the queries newsgroup.
 
M

Michael

Thanks Brendan,
Your reply did not arrive until I had posted my reply... then I felt a
little dumb for my reply lol
thank you for your help
Michael
 
Top