Formating a date in Email from Access

[MichelleB]

Hi i have a script that creates an email for a booking when i click on the artist name which works great. i want to be able to subject the email with the week number of the month. ie in subject it would be WEEK 1 JULY WEEKEND CHECKOFF. with the date info coming from gigdate field

Private Sub artist(Cancel As Integer)
Dim msgTxt As Variant
Dim objOutlook As Outlook.Application
Dim objMailItem As Outlook.MailItem
Dim blnCreated As Boolean
Dim act As String
Dim venue As String
Dim start As String
Dim finish As String
Dim actfee As String
Dim netpay As String
Dim actpayment As String
Dim actcomment As String
Dim gigdate As String
Dim street As String
Dim commision As String


strEmail = "michelle@work.com.au"
strSubject = "Weekend Booking Checkoff "
strBody = "<h2><b>Please confirm your upcoming weekend Booking</b></h2>"
act = Me.artist
gigdate = Format(Me.gigdate, "dddd dd mmmm yyyy")
venue = Me.venuename
start = Format(Me.start, "hh:nn am/pm")
finish = Format(Me.finish, "hh:nn am/pm")
street = Me.street
suburb = Me.suburb
actfee = Format(Me.actfee, "00.00")
commission = Format(Me.commission, "00.00")
netpay = Format(Me.netpay, "00.00")
paid = Me.actpayment



blnCreated = False

Set objOutlook = New Outlook.Application

If IsNull(Me.artist_email) = True Or Me.artist_email = "" Then
DoCmd.Hourglass False
msgTxt = MsgBox("Unable to create an email for " & Me.artist & Chr(13) & "No email address listed in the database.", vbOKOnly + vbInformation, "")
Else
DoCmd.Hourglass True
Set objMailItem = objOutlook.CreateItem(olMailItem)

With objMailItem
.To = Me.artist_email
.CC = strEmail
.Subject = gigdate & " " & strSubject & " - " & act
.HTMLBody = strBody & "<p>" & "<p>" & act & "<br>" & gigdate & "<br>" & venue & "<br>" & street & ", " & suburb & "<br>" & start & " - " & finish & "<br>" & "Act Fee: $" & actfee & "  Less Commission: $" & commission & "  Net Pay: $" & netpay & "<p>" & "Payment Details: " & paid & "<p>" & "Please reply OK to confirm this booking" 


.Save
' .Send
blnCreated = True
End With

End If

If blnCreated Then

Else
msgTxt = MsgBox("Email Failed.", vbOKOnly, "")
End If

DoCmd.Hourglass False
End Sub

the email format generated is as follows

Please confirm your upcoming weekend Booking
NAME OF ACT
Friday 20 July 2012
NAME OF VENUE
ADDRESS OF VENUE
09:30 pm - 01:00 am
Act Fee: $800.00 Less Commission: $80 Net Pay: $720.00
Payment Details: Invoice venue prior - EFT
Please reply OK to confirm this booking

 

[boblarson]

A quick change from this:

.Subject = gigdate & " " & strSubject & " - " & act

to this (should work):

.Subject = "Week " & Format(gigdate, "ww") & " " & Format(gigdate, "mmmm") & " " & strSubject & " - " & act

 

[MichelleB]

thanks for the quick reply, this gives me
Week Friday 20 July Weekend Booking Checkoff - Greg McKew

but does not list the week number as
Week 3 Friday 20 July etc

am i doing something wrong
.Subject = "Week " & Format(gigdate, "ww") & " " & Format(gigdate, "mmmm") & " " & strSubject & " - " & act

 

[boblarson]

thanks for the quick reply, this gives me
Week Friday 20 July Weekend Booking Checkoff - Greg McKew

but does not list the week number as
Week 3 Friday 20 July etc

am i doing something wrong
.Subject = "Week " & Format(gigdate, "ww") & " " & Format(gigdate, "mmmm") & " " & strSubject & " - " & act

Are you sure you used the two w's instead of one? Two W will give you the week number and ONE W will give you the weekday name.

 

[MichelleB]

i need it to look like this

Week 2 July Weekend Checkoff - act name

 

[MichelleB]

sorry i need the week number in the month not the week number in the year, does that make sense

 

[boblarson]

Ah, I see - your gigdate isn't really a date. It is a string with a format already defined.

So, change the code I gave you to this:

.Subject = "Week " & Format(Me.gigdate, "ww") & " " & Format(Me.gigdate, "mmmm") & " " & strSubject & " - " & act

 

[MichelleB]

firstly thankyou so much for you help it is greatly appreciated

when i use this

.Subject = "Week " & Format(Me.gigdate, "ww") & " " & Format(Me.gigdate, "mmmm") & " " & strSubject & " - " & act

i get this

Week 29 July Weekend Booking Checkoff - Greg McKew

i need this

Week 3 July Weekend Booking Checkoff - Greg McKew

 

[boblarson]

Okay, sorry about the mixup. So, paste this function into a standard module (NOT form, report or class module)

Function GetMonthWeek(dteDate As Date) As Integer
Dim intAdjuster As Integer
If Day(dteDate) Mod 7 <> 0 Then
    intAdjuster = 1
End If

GetMonthWeek = (Day(dteDate) \ 7) + intAdjuster
End Function

And then you can modify your code to this;

.Subject = "Week " & GetMonthWeek(Me.gigdate) & " " & Format(Me.gigdate, "mmmm") & " " & strSubject & " - " & act

 

[MichelleB]

Thankyou thankyou thankyou, that worked perfectly. you are wonderful