Code to identify the current day

[JohnLee]

Good day folks,

I'm hoping someone can help me here, I've searched this forum, but can't seem to find what I'm looking for.

I would like to write some code that identifies the current day as in day of the week for instance today is Wednesday, so the code would identify that today is Wednesday.

I have a process that I want to only action if the current day is Friday, if it's not Friday then I shouldn't do anything.

I looked up the day function, but it returns a date and not the day of the week.

Any assistance would be most appreciated.

John

 

[Galaxiom]

See the WeekDay function. It returns a day number.

WeekDayName returns the name.

 

[JohnLee]

Hi GalaxiomAtHome,

Thanks for the pointer, I came up with the following which I think will do the job

Dim MyDate, MyWeekDay
MyDate = Date 'Assign the current date
MyWeekDay = Weekday(MyDate) 'MyWeekDay represents the current day in the week
If MyWeekDay = 6 Then
 
''Code for actions to follow
 
Else
    MsgBox "Not Friday today"
End If
MsgBox MyWeekDay

John

 

[namliam]

Weekday(Date()) will return 6
WeekdayName(weekday(Date())) will return Friday
To be trully independent though, you need to fill in the FirstDayOfWeek parameter. Per default it starts the week on sunday, but there is no guarantee this wont ever change.

i.e. Weekday(Date(), vbFriday) = 1
will guaranteed check if today is Friday

Format(date(), "DDD") returns Fri
Format(date(), "DDDD") returns Friday
Are also possibilities

 

[JohnLee]

Hi Namliam,

Thanks for you suggestion, I will take that into account and keep a copy of you post if I run into problems once I've deployed my process, for the present my code is working, I've done some tests by changing the day of week number and so far so good. My code now looks like this, in case anyone is interested:

Dim ExcelFile As String         'Declare the ExcelFile variable
Dim ExcelWorksheet As String    'Declare the ExcelWorksheet variable
Dim FEDB As String              'Declare the FEDB variable
Dim QueryName As String         'Declare the QueryName variable
Dim objDB As Database           'Declare the objDB variable
Dim MyDate                      'Declare the MyDate variable
Dim MyWeekDay                   'Declare the MyWeekDay variable
MyDate = Date 'Assign the current date
MyWeekDay = Weekday(MyDate) 'MyWeekDay represents the current day in the week
'If today is Friday then
If MyWeekDay = 6 Then
    ExcelFile = "G:\Biasi\WC" & "_" & Format(Date - 4, "ddmmyy") & ".xls" 'Assign the location to export the Biasi excel file to and give the current date
    ExcelWorksheet = "Biasi"                                                'Assign Biasi as the name of the worksheet in the excel file
    FEDB = "H:\John Lee\eFlowStatsFrontEnd.mdb"                             'Assign the name and path of the database to export the table from
    QueryName = "qryExportToExceltblBiasi"                                        'Assign the name of the table to be exported to the Excel file
    Set objDB = OpenDatabase(FEDB)  'Set the objDB to open the eFlowStatsFrontEnd database
    If Dir(ExcelFile) <> "" Then Kill ExcelFile 'If the Excel file already exists, you can delete it here
    'Excute the creation of the Excel file
    objDB.Execute "Select*Into[Excel 8.0;Database=" & ExcelFile & "].[" & ExcelWorksheet & "] From " & "[" & QueryName & "]"
    objDB.Close 'Close the eFlowStatsFrontEnd database
    Set objDB = Nothing 'Set the objDB to nothing
End If

Thanks for your observations

John