George-Bowyer
Registered User.
- Local time
- Today, 04:49
- Joined
- Dec 21, 2012
- Messages
- 178
I live in the UK and use dd/mm/yyyy
I have the following code to select records for that fall within a financial year (1st May - 30th April)
The problem is that my SQL interprets 01/05/2019 as 5th Jan not 1st May.
I have tried formatting the dates to dd-mmm-yyyy as you can see, but whilst debug.print shows 01 May 2019 in the immediate window, it and the msgbox still show 01/05/2019 when the code is run for real and the SQL still interprets it as 5th Jan.
Can anyone help, please?
Thanks
I have the following code to select records for that fall within a financial year (1st May - 30th April)
Code:
Case 2
datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
datStartDate1 = Format(datStartDate1, "dd-mmm-yyyy")
datEndDate1 = Format(datEndDate1, "dd-mmm-yyyy")
MsgBox datStartDate1 & " " & datEndDate1
strSQL = "SELECT Blah blah_
" WHERE (((tblPaymentsDue.fldDateDue)>#" & datStartDate1 & "# And (tblPaymentsDue.fldDateDue)<#" & datEndDate1 & "#))" & _
" ORDER BY tblPaymentsDue.fldDateDue, tblOrganisations.FldOrgName;"
The problem is that my SQL interprets 01/05/2019 as 5th Jan not 1st May.
I have tried formatting the dates to dd-mmm-yyyy as you can see, but whilst debug.print shows 01 May 2019 in the immediate window, it and the msgbox still show 01/05/2019 when the code is run for real and the SQL still interprets it as 5th Jan.
Can anyone help, please?
Thanks