Filtering by Date in VBA to SQL Server using Data Access Projects

[CanNeverThinkOfaName]

I am trying to use a date field input on a Data Access Project form as a filter for opening a report, but this only works when the date is in American Format ie. mm/dd/yyyy. I am based in the United Kingdom, and would prefer not to have the format of the field on my form set as American, so is there any function in VBA to convert from Bitish to American date format behind the scenes?

If Me.txtJobStartDate = "" Or IsNull(Me.txtJobStartDate) Or Me.txtJobEndDate = "" Or IsNull(Me.txtJobEndDate) Then
MsgBox "Please insert Start and End Date."
Else
stDocName = "rptICTReportsJobCompleted"
stLinkCriteria = "[Job_actioned] = 0 and [job_date_entered] between " & "'" & Me.txtJobStartDate & "' and '" & Me.txtAmericanEndDate.Text & "'"

If stLinkCriteria <> &quot;&quot; Then
sconn = &quot;Driver={SQL Server}; Server=ECONPWRSQL1;&quot; _
& &quot;Database=BTS Project Management;Trusted_Connection=Yes;&quot;

cn.Open sconn

ssql = &quot;SELECT * FROM dbo.[qryICTJobsInternal] where &quot; & stLinkCriteria

rs.Open ssql, cn, adOpenStatic
If rs.RecordCount = 0 Then
MsgBox &quot;These search criteria return no records, please select again.&quot;
Else

DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

End If

rs.Close
cn.Close

End If

End If

 

[MinusM]

What is happening is when you take the date field as text Access converts to US format since it is the Access standard.


As it is the Date field is stored internally as a decimal number.
When manually formatting the date value, using yyyy/mm/dd will avoid confusion both for you and Access. Access accepts only Month Value if the first value is Year. So I have found it wiser to use this format and it has never erred whatever the System format is


stLinkCriteria = &quot;[Job_actioned] = 0 and [job_date_entered] between &quot; & &quot;'&quot; & Me.txtJobStartDate & &quot;' and '&quot; & Format(Me.txtAmericanEndDate,&quot;yyyy/mm/dd&quot & &quot;'&quot;

should do the trick

Best of luck