capture day from date field

antonyx

antonyx

Arsenal Supporter
Local time
Today, 21:17
Joined
Jan 7, 2005
Messages
556
i was told that i didnt need to store the string value day.. eg.. monday.. tuesday.. because access can retreive the day based on the date..

so...

i have a textbox on my form that displays the date as follows..

23/12/2006

i am basically creating a html email and at the moment i use the following..

Code: 
Option Compare Database

Private Sub Command254_Click()
'References: Oulook Library
Dim strEmail, strSubject As String, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strEmail = Me.txtbookeremail

strBody = "<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01//EN' 'http://www.w3.org/TR/html4/strict.dtd'><html><head>" & _
"<meta http-equiv='Content-Language' content='en-gb'><meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'></head><body>" & _
"<p>" & Me.txtjobdate & "</p></body></html>"


strSubject = "London Heathrow Cars - Booking Confirmation"
With objEmail
    .To = strEmail
    .Subject = strSubject
    .HTMLBody = strBody
    '.Send 'Will cause warning message
    .Display
End With

Set objEmail = Nothing
End Sub

the email displays the date as it stands in the textbox..

is there a standard method in access i can use to convert..

23/12/2006 to Saturday 23rd December 2006??
 
there are many many posts on this.. but i cant seem to find one that converts the date to the format i want it..

i changed the format of the textbox to long date and it displays as follows..

23 December 2006

also on one of my previous posts.. there was some formating i was given to add the 'rd' and 'st' to the date.. here is that code

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

however, this was coded based on me having a single value for jobday.. eg.. 21, 04.. etc..

now the date is altogether in one the above code wont suffice..

if i change the format of the textbox to long date.. and it displays like so..

23 December 2006

isnt there anything i can place in the input mask to display it as

Saturday 23rd December 2006
 
On my PC, long date produces:

Monday, November 13, 2006

so I think the actual format is coming from your regional settings. You should be able to use a custom format with the Format() function to achieve your goal.
 
ok...

so are you saying that i will need an actual snippet of code on my form that alters the format of the date..??

if this is the case.. i am unable to write such a script..
 
No, I'm saying a custom format. Instead of:

Format(FieldName, "Long Date")

try

Format(FieldName, "dddd mmmm dd"", ""yyyy")

which on my PC produced the desired result.
 
yes.. that did it.. the only thing now is the

th and st and rd are missing..

is there any way to combine the above snippet i used in my previous post with this one so that it can add the appropriate rd' or nd.. to the day number?
 
You'd probably have to do it manually:

Declare variables:
Code: 
dim datelastdigit, ending, thedate
Figure out if it needs the 'st', 'nd', 'rd', or 'th' ending by first finding the last digit of the Day:
Code: 
datelastdigit = Right(Day([Fieldname]),1)
Then use a case statement to figure out the ending:
Code: 
Select Case datelastdigit
    Case 1
        ending = "st"
    Case 2
        ending = "nd"
    Case 3
        ending = "rd"
    Case Else
        ending = "th"
End Select
Then combine it all into one variable:
Code: 
thedate = Format([FieldName], "dddd") & " " & Format([FieldName], "d") & ending & " " & Format([FieldName], "mmmm yyyy")
(there's probably an easier way to combine it, though)
 
Last edited:
thank you very much it worked like a charm
 
One assumes you corrected the Select/Case statement to return correct values?
 
Unless I'm missing something, which wouldn't be unusual:

11st?

12nd?

13rd?
 
By gum, you're right!

Stupid english language...

Code: 
dim dayofdate
dayofdate = Day([FieldName])
Select Case dayofdate
    Case 11 To 13
        ending = "th"
    Case Else
        datelastdigit = Right(dayofdate, 1)
        Select Case datelastdigit
            Case 1
                ending = "st"
            Case 2
                ending = "nd"
            Case 3
                ending = "rd"
            Case Else
                ending = "th"
        End Select
End Select
 
Last edited:
You must log in or register to reply here.

Similar threads

T
Attache to email from attachment Field
Replies
2
Views
637
theinviter
T
J
Error 438 - Object doesn't support this property or method
Replies
8
Views
1,550
JohnPapa
J
Sam Summers
sending an email from a form in access 2019 to outlook 365
2
Replies
23
Views
3,899
Sam Summers
Sam Summers
C
Invalid Use of Null
Replies
18
Views
2,485
chuckcoleman
C
Sam Summers
Modal form not closing after code has run
Replies
8
Views
1,335
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom