Allow date format different than Windows default format.

[k3ll1n]

Hey all,

Wondering if anyone can provide a work around for this issue I'm having.
The DB I've created is for our department at work and once complete, I will place it on a shared drive and provide shortcuts to it.

The issue I'm having is that one of my Appointment Date field is required before it'll show the Available Times for that day. I have tried using Input Masks but no matter what I do, the Date entry is tied to how Windows has it's date formatted in the control panel.

Almost all computers in the office have a date format of 2016.06.15 (Periods as spacers). My form will not accept periods in the date. It spits out an error when opening the cbo dropdown.

If I set the system time format to yyyy-MM-dd in the control panel, everything works. But every morning when the system is turned on, the format resets to periods. I don't see our IT dept making an exception for this and changing all the system defaults.

I'm not sure if there's something I can throw in the VBA code to allow them, or another alternative. I would consider myself still pretty new to Access.
I was assisted with the code below.


The two main functions of my Module are;

Public Function GetExcludedTimes(vDate As Date) As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

Dim excluded As String

strSql = "Select AppointmentTime from tblAppointments where AppointmentDate = #" & vDate & "#"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

If rs.BOF And rs.EOF Then
GetExcludedTimes = "0"
Else

Do Until rs.EOF
excluded = "#" & rs!AppointmentTime & "#" & "," & excluded
excluded = Replace(excluded, "##", "")
rs.MoveNext
Loop
GetExcludedTimes = excluded
End If

MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Public Sub GetCboDates(VarDate As Date)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim StrCrit As String

StrCrit = GetExcludedTimes(VarDate)

If StrCrit = "0" Then
strSql = "select * from tblAppointmentHoursDaily"
Else
strSql = "select * from tblAppointmentHoursDaily where Hours not in (" & StrCrit & ")"
End If

'Screen.ActiveForm.cboAvailability.RowSource = ""
Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource = ""
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

Do Until rs.EOF
'Debug.Print rs!Hours
'Screen.ActiveForm.cboAvailability.AddItem rs!Hours
Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.AddItem rs!Hours
rs.MoveNext
Loop


MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[arnelgp]

ofcourse it will allow period in the form, just do the formatting through your code:

strSql = "Select AppointmentTime from tblAppointments where AppointmentDate = #" & Format(vDate, "mm/dd/yyyy") & "#"

 

[k3ll1n]

ofcourse it will allow period in the form, just do the formatting through your code:

strSql = "Select AppointmentTime from tblAppointments where AppointmentDate = #" & Format(vDate, "mm/dd/yyyy") & "#"

Thanks for the quick reply arnelgp. After changing it to what you suggested, I am still getting the same error. I will attach the image.
I typed in the date of 2016.06.15 and selected via the date picker. Both returned the error. I also tried to change your format to yyyy/dd/mm with no luck. (As my system date shows 2016.06.15)

 

[k3ll1n]

It's the combobox that won't load it's dropdown after having periods in the date textbox.

 

[k3ll1n]

Switching it to "dd\/mm\/yyyy" seems to have done the trick.
I had been shown this prior and thought I knew where to enter it. Your post helped me situate it. Thank you.

 

[arnelgp]

do you still have the input mask on the control? can you delete it first and try again.

 

[k3ll1n]

do you still have the input mask on the control? can you delete it first and try again.

I took the input mask off prior to entering the new format formula. Haven't run into any issues.
Thanks again.