auto format date, eg 1 into 1st.. 23 into 23rd, etc 1

[tonyx666]

ok, basically im sending an email str8 from access.. here is my code.

Option Compare Database

Private Sub emailbutton_Click()
On Error GoTo send_Err

Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String

strSubject = "London Heathrow Cars - Booking Confirmation"

If Len(Me.emailadd & vbNullString) = 0 Then
    MsgBox ("Forgot an email address")
    Me.emailadd.SetFocus
Else
    strToWhom = Me.emailadd
    strMsgBody = "FAO " & Me.title & " " & Me.myname & vbCrLf & vbCrLf & _
    "Thank you for your booking request via our website. Details of the requested transfer and our fare are as follows:" & vbCrLf & vbCrLf & _
    [b]"Job Date: " & Me.jobday & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & vbCrLf & _[/b]
    "From: " & Me.padd & vbCrLf & _
    "Name: " & Me.myname & vbCrLf & _
    "Phone: " & Me.phone & vbCrLf & _
    "Price: £" & Me.price & vbCrLf & _
    ".."

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
End If

send_Err:
If Err.Number = 2501 Then
MsgBox "THIS EMAIL HAS NOT BEEN SENT!", vbInformation, "Notice!"
End If
End Sub

the bold line shows the date of the job.. due to my initial website form requirements, the date is gathered in 3 drop down menus..

day (numbers 1-31)
month (jan-dec)
year (2006,2007)

so a typical record in my database will have one of these items in the three fields jobday, jobmonth and jobyear

when i send out the email using the above code.. the date is displayed like so...

Job Date: 3 March 2006


what i want to do is make sure that the 'rd' or 'th' or 'st' are included after the day number

so i need some kind of rule in this code like the following..

if Me.jobday = 1 then add st
if Me.jobday = 2 then add nd
if Me.jobday = 3 then add rd
if Me.jobday = 4 then add th
...
...
if Me.jobday = 29 then add th


etc,etc

you get the picture..

i realise that there are probably more advanced date features offered by access that can resolve these problems.. but as i do not have too much time and ability, i was wondering if there was a method like the one i suggested above i could incorporate into this system fairly easily.

thanks in advance.

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[PHV]

Typed, untested:
Me.jobday & Nz(Choose(Me.jobday, "st", "nd", "rd"), "th")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tonyx666]

it worked for some of them but 21 was 21th

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[PHV]

Sorry, I'm not a native english speaker, so I just followed the posted directives ...

 

[tonyx666]

ok, well basically numbers 1-20 are fine..

from 20-31 they are all being shown as th..

they should actually be like this though..

21st
22nd
23rd
24th
25th
26th
27th
28th
29th
30th
31st

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[claytonjgordon]

Maybe:

dim temp as int

temp = right([jobday],1)

Me.jobday & Nz(Choose(Number, "st", "nd", "rd"), "th")

I'm not sure how that choose function works, but if you could base it on only the right most number instead of the whole number I think it will work.


Dominus Nihil
(Master of Nothing)

 

[tonyx666]

this code is still displaying 31 as 31th.. not 31st

Option Compare Database

Private Sub emailbutton_Click()
On Error GoTo send_Err

Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim temp As Integer

temp = Right([jobday], 1)

strSubject = "London Heathrow Cars - Booking Confirmation"

If Len(Me.emailadd & vbNullString) = 0 Then
    MsgBox ("Forgot an email address")
    Me.emailadd.SetFocus
Else
    strToWhom = Me.emailadd
    strMsgBody = "FAO " & Me.title & " " & Me.myname & vbCrLf & vbCrLf & _
    "Thank you for your booking request via our website. Details of the requested transfer and our fare are as follows:" & vbCrLf & vbCrLf & _
    "Job Date: " & Me.jobday & Nz(Choose(Me.jobday, "st", "nd", "rd"), "th") & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & vbCrLf & _
    "From: " & Me.padd & vbCrLf & _
    "Name: " & Me.myname & vbCrLf & _
    "Phone: " & Me.phone & vbCrLf & _
    "Price: £" & Me.price & vbCrLf & vbCrLf & _
    "All of our drivers are courteous, experienced and smartly presented. Our vehicles are clean, modern, comfortable and fitted with satellite navigation technology for you peace of mind."

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
End If

send_Err:
If Err.Number = 2501 Then
MsgBox "THIS EMAIL HAS NOT BEEN SENT!", vbInformation, "Notice!"
End If
End Sub

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[claytonjgordon]

Hmmm, that doens't work for 10th - 13th.

But I think that's the right track.


Dominus Nihil
(Master of Nothing)

 

[claytonjgordon]

OK, not working at all. I'll defer to PHV before I confuse you more being wrong. Sorry.


Dominus Nihil
(Master of Nothing)

 

[PHV]

Perhaps this ?
Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tonyx666]

i think this has done the trick, thank you very very much

Option Compare Database

Private Sub emailbutton_Click()
On Error GoTo send_Err

Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim temp As Integer

temp = Right([jobday], 1)

strSubject = "London Heathrow Cars - Booking Confirmation"

If Len(Me.emailadd & vbNullString) = 0 Then
    MsgBox ("Forgot an email address")
    Me.emailadd.SetFocus
Else
    strToWhom = Me.emailadd
    strMsgBody = "FAO " & Me.title & " " & Me.myname & vbCrLf & vbCrLf & _
    "Thank you for your booking request via our website. Details of the requested transfer and our fare are as follows:" & vbCrLf & vbCrLf & _
    "Job Date: " & Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th") & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & vbCrLf & _
    "From: " & Me.padd & vbCrLf & _
    "Name: " & Me.myname & vbCrLf & _
    "Phone: " & Me.phone & vbCrLf & _
    "Price: £" & Me.price & vbCrLf & vbCrLf & _
    "All of our drivers are courteous, experienced and smartly presented. Our vehicles are clean, modern, comfortable and fitted with satellite navigation technology for you peace of mind."

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
End If

send_Err:
If Err.Number = 2501 Then
MsgBox "THIS EMAIL HAS NOT BEEN SENT!", vbInformation, "Notice!"
End If
End Sub

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[tonyx666]

lastly.. i know this question is not entirely relevant to the topic title, but using this email format above.. is there anyway to set the body font to arial and size 10pt

London Heathrow Cars

http://www.LondonHeathrowCars.com

 

[Pampers]

Hi tonyx666,
Please start new thread for new topic, and maybe give some guys a big star.

Pampers
Keeping it simple can be complicated