if statement based on time and date (1 Viewer)

kobiashi

Registered User.
Local time
Today, 07:42
Joined
May 11, 2018
Messages
258
hi

so i want to write an if statement to import an excel spreadsheet based on a time and a date

so first criteria is if its a week day or a weekend

second criteria is if its before 12:00

i thought it may be something like
Code:
if Now() = WeekDay(1) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
ElseIF Now() = WeekDay(1) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
ElseIf Now() = WeekDay(2) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
ELSEIf Now() = WeekDay(2) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
ElseIf Now() = WeekDay(3) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
ELSEIf Now() = WeekDay(3) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
ElseIf Now() = WeekDay(4) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
ELSEIf Now() = WeekDay(4) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
ElseIf Now() = WeekDay(5) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
ELSEIf Now() = WeekDay(5) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
ElseIf Now() = WeekDay(6) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "Weekend AM Run In!A2:J50"
ELSEIf Now() = WeekDay(6) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "Weekend PM Run In!A2:J50"
ElseIf Now() = WeekDay(7) AND < "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "Weekend AM Run In!A2:J50"
ELSEIf Now() = WeekDay(7) AND > "12:00:00" then
	DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "Weekend PM Run In!A2:J50"
End If
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:42
Joined
Feb 19, 2013
Messages
16,553
since you cover every possibility, I don't see the reason for all those rows

Not sure what you are trying to achieve but your if statement won't work - have you tried to run it? I would expect it to fail on syntax.

now returns a date datatype which is a number - right now it is 43372.5125810185
 

kobiashi

Registered User.
Local time
Today, 07:42
Joined
May 11, 2018
Messages
258
so the if statement didnt work,

so we have an excel sheet, with a list of when all the trains come back into the depot at two points in the day, one in the morning time and one in the evening time,

but on the weekend the format of this sheet changes, so i need a way to differentiate between monday to friday and the weekends, plus i need to differentiate between morning and evening on the week days
 

isladogs

MVP / VIP
Local time
Today, 07:42
Joined
Jan 14, 2017
Messages
18,186
If you try pasting your code into Access, all If & ElseIf lines will be shown in red due to syntax errors as mentioned by CJ_London.
Its also unnecessarily repetitive

This is better & should work

Using the default values for the weekday function in the UK, Sunday =1 and Saturday = 7, so weekdays are 2 to 6

Code:
Dim strTableName As String

strTableName = "YourTableName"

Select Case Weekday(Date)

Case Is = 2, 3, 4, 5, 6
    If Time() < #12:00:00 PM# Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
    Else
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
    End If
Case Else
     If Time() < #12:00:00 PM# Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekEnd AM Run In!A2:J50"
    Else
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekEnd PM Run In!A2:J50"
    End If
End Select

The above assumes you have worksheets named "WeekEnd PM Run In" etc
You may be able to shorten the code still further
 

isladogs

MVP / VIP
Local time
Today, 07:42
Joined
Jan 14, 2017
Messages
18,186
You're welcome
In general, using Select Case is more efficient than If ...Else ...End If.
This is an alternative only using Case statements

Code:
Dim strTableName As String

strTableName = "YourTableName"

Select Case Weekday(Date)

Case Is = 2, 3, 4, 5, 6
    Select Case Time()
    Case Is < #12:00:00 PM# 
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekDay AM Run In!A2:J50"
    Case Else
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekDay PM Run In!A2:J50"
    End Select
Case Else
     Select Case Time()
     Case Is < #12:00:00 PM# 
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekEnd AM Run In!A2:J50"
     Case Else
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, selectFile(), False, "WeekEnd PM Run In!A2:J50"
    End Select
End Select
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:42
Joined
Feb 28, 2001
Messages
27,001
To amplify, the If / Then / ElseIf / ... / Else construct is efficient but after about two or three ElseIf situations, you start to cross over to the point where a SELECT CASE ladder is easier to write - and to read.

Execution-wise, based on single-step debugging, I'm fairly sure that they are implemented in underlying code almost identically. But reading the code to see what it does? The SELECT CASE construct is FAR superior as the number of cases increases.
 

isladogs

MVP / VIP
Local time
Today, 07:42
Joined
Jan 14, 2017
Messages
18,186
Select Case is certainly more readable

I've read that Select case should reduce the number of code steps compared to If..Else..End If.
So in theory it should be slightly faster though I expect any difference will be negligible.

However the Switch function is supposed to be much more efficient than both of these and so should be noticeably faster for complex scenarios.

I can feel another one of my speed comparison tests coming on....:rolleyes::cool:
 

Users who are viewing this thread

Top Bottom