Its been a few years since I needed to do anything on the application I made for work...But then clients ask for new stuff occasionally and this is proving more of a challenge than I thought it would.
Simply put I have to code dates according to day of the week and apply a different code for public holidays. Its the last that is proving interesting to say the least.
I have a table set up with two fields Name of Holiday and Date of Holiday. The date is formatted short date.
I am using an SQL statement to check a date input on a form against the dates in the table:
Code:
strSQL = "SELECT tblCode.PublicHoliday, tblCode.HolidayDate " & _
"FROM tblCode " & _
"WHERE tblCode.HolidayDate = #" & Format(Me.txtDt, ShortDate) & "#"
The immediate window shows this is producing the following SQL which reads correctly:
Code:
SELECT tblCode.PublicHoliday, tblCode.HolidayDate FROM tblCode WHERE tblCode.HolidayDate = #3/06/2019#
However when I run the code it produces wrong result being coded as any other day not a public holiday.
When I copy and paste the SQL statement from the immediate window into a query then view the query in design view I see the date format has been changed to American date format being 6/3/19 rather than our date format being 3/6/19. Interesting this doesn't produce an error on 19/4/19 it codes correctly.
The computer region is set to Australia, the languages are set to Australia, the short date format is stipulated at table, at form and in query. What am I missing or is there a better way to write this?