Something wrong with MS sample code (VB) Access 2007 (1 Viewer)

[piedpiper8]

Okay, for the SQL statement you need to use U.S. formatted dates (mm/dd/yyyy). So you should probably encapsulate the date field in the SQL with

Format(startDate,"mm/dd/yyyy")

etc.

Ok this is how the code looks like now: - It didn't work, still blank report.

Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote

If Filter1 <> "" Then strSql = strSql & " Employee = " & quote & Filter1 & quote & " AND "
If Filter2 <> "" Then strSql = strSql & " Company = " & quote & Filter2 & quote & " AND "
If Filter3 <> "" Then strSql = strSql & " Project = " & quote & Filter3 & quote & " AND "
If Filter4 <> "" Then strSql = strSql & " Task = " & quote & Filter4 & quote & " AND "
Dim startDate As Date, endDate As Date
If Len(txtStartDate) > 0 Or Len(txtEndDate) > 0 Then
On Error GoTo InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: If Err.Number > 0 Then
MsgBox ("Bad date.")
Exit Sub
End If
strSql = strSql & " [Date] >=#" & Format(startDate, "mm/dd/yyyy") & "# And [Date] <=#" & endDate & "# AND "
On Error GoTo 0 'resets it to regular error mode
End If
If Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True

 

[boblarson]

You didn't format the end date too. I just did the one because I figured you would automatically do the other one too. Sorry for assuming such. Make sure to format the end date also.

 

[piedpiper8]

You didn't format the end date too. I just did the one because I figured you would automatically do the other one too. Sorry for assuming such. Make sure to format the end date also.

My apologies... should of caught that.

Anyways, everything is working as it should... thank you...

I'm curious though, why doesn't it like dd/mm/yyyy?

 

[boblarson]

It's a Microsoft thing. It started off in the U.S. and they never made it work with regional settings for some reason. Perhaps someone like Pat Hartman, Brent (datAdrenaline) or Leigh Purvis would know.