Access 2007

[nidave]

is there anyone who understands VB in Access 2007?

I am using some code in a form to filter a report. (I got the code from a website and have tried to amend it) I don't actually much of it. (I am not a developer)
It needs to filter by date range.

Code: Select all

Option Compare Database
Option Explicit


Private Sub cmdApplyFilter_Click()
    Dim strFilter As String
    Dim blnFilterOn As Boolean
    Dim strCriteria As String
    
    strCriteria = "Criteria: "
    
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString And Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        If Me.txtStartDate > Me.txtEndDate Then
            MsgBox "Start Date Cannot Be Greater Than End Date", vbInformation
            Exit Sub
        End If
    End If
    If Nz(Me.txtStartDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [Course Date] >= #" & Me.txtStartDate & "#"
        strCriteria = strCriteria & vbCrLf & "Begin Date: " & Me.txtStartDate
    End If
    
    If Nz(Me.txtEndDate, vbNullString) <> vbNullString Then
        strFilter = strFilter & " AND [Course Date] <= #" & Me.txtEndDate & "#"
        strCriteria = strCriteria & vbCrLf & "End Date: " & Me.txtEndDate
    End If
    
    If strFilter <> vbNullString Then
        strFilter = Mid(strFilter, 6)
        Me.txtCriteria = strCriteria
        Reports("RPT_TrainingRecordTime").Filter = strFilter
        Reports("RPT_TrainingRecordTime").FilterOn = True
    Else
        MsgBox "Enter some criteria or print the report", vbInformation
    End If
    
    
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close acReport, "RPT_TrainingRecordTime"
    DoCmd.Close acForm, "FRM_TrainingTimeSelectionDate"
    
End Sub

Private Sub cmdRemoveFilter_Click()
        Me.txtCriteria = "Criteria: None"
        Reports("RPT_TrainingRecordTime").Filter = vbNullString
        Reports("RPT_TrainingRecordTime").FilterOn = False
End Sub

Private Sub Form_Open(Cancel As Integer)
 DoCmd.OpenReport "RPT_TrainingRecordTime", acPreview
End Sub
   

The filtering is not working as expected.
I can upload the access file if you want a look at it.

[nidave]

Sorted it - it needs to be in US Date format.

[Alexia]

Sorted it - it needs to be in US Date format.

Stupid Yanks. I can just about understand the mutation of the language over time, but which idiot decided that DAY-MONTH-YEAR (i.e. smallest unit to largest in order) isn't good enough? I mean, when you ask the date, you usually say "the 29th" because usually you know what month it is.

[Nick Harvey]

I've always found it interesting that we like to write the date, over here, as smallest unit, medium unit, largest unit (dd/mm/yy), yet we insist on the time being written completely the opposite way, as largest unit, medium unit, smallest unit (hh:mm:ss).

If I'm including the date in a filename, meeting minutes for example, I always use largest first, yyyy-mm-dd, so the files sort into correct chronological order in the folder.

[Sput]

Sorted it - it needs to be in US Date format.

Stupid Yanks. I can just about understand the mutation of the language over time, but which idiot decided that DAY-MONTH-YEAR (i.e. smallest unit to largest in order) isn't good enough? I mean, when you ask the date, you usually say "the 29th" because usually you know what month it is.

WE usually say it like that, but THEY tend to say "October 30th, 2010", so it makes sense to them. Just like driving on the right!

[Alexia]

I've always found it interesting that we like to write the date, over here, as smallest unit, medium unit, largest unit (dd/mm/yy), yet we insist on the time being written completely the opposite way, as largest unit, medium unit, smallest unit (hh:mm:ss).

If I'm including the date in a filename, meeting minutes for example, I always use largest first, yyyy-mm-dd, so the files sort into correct chronological order in the folder.

In computing I'm sure it makes sense, although the Details view and "Arrange by... Modified" largely negates that need nowadays, as the Staff Documents folder in work shows!

The time situation is indeed interesting; so let's put it another way; the most important comes first - the day and the hour. That puts you very firmly at a specific unambiguous point in time (so long as you have an appreciation of which month it is). Case in point - after staying up to watch a lot of World Series baseball this week, I've had a lie in this morning. When I woke up it was light, and because my glass of cloudy lemonade was in the way of my alarm clock all I could see was the minute display, which was :29. For those five seconds it felt very weird to know it was nearly half past, but I didn't know what it was half past. If however I'd have immediately seen it was 2:-- something, I would have immediately had the feelings of being lazy and guilty I did five seconds later.