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 & _
"Job Date: " & Me.jobday & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & vbCrLf & _
"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.

Hi -

This function will add the appropriate suffix to any number.

Public Function NumSuffix(MyNum As Variant) As String
'*******************************************
'Purpose: Add suffix to a number
'coded by: raskew
'Inputs: ? NumSuffix(234)
'Output: 234th
'*******************************************
Dim n As Integer
Dim x As Integer
Dim strSuf As String

n = Right(MyNum, 2)
x = n Mod 10
strSuf = Switch(n <> 11 And x = 1, "st", n <> 12 And x = 2, "nd", _
n <> 13 And x = 3, "rd", True, "th")
NumSuffix = LTrim(str(MyNum)) & strSuf

End Function

To use, copy the code to a new module, then modify this line:
"Job Date: " & Me.jobday & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & vbCrLf & _ to read:

"Job Date: " & numsuffix(Me.jobday) & " " & Me.jobmonth & .........

HTH - Bob

thank you very much for that.. i have also received this code from another forum.. just to curious to know whether this will suffice also because it seems to be working

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 & _
"Job Time: " & Me.jobhour & " " & Me.jobminutes & " hrs" & vbCrLf & vbCrLf & _
"Pickup From: " & Me.padd & vbCrLf & vbCrLf & _
"Destination: " & Me.dadd & vbCrLf & 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

Hi -
"Job Date: " & Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th") & ....

That works great. Very clever use of the NZ() and Choose() functions.

Bob