Filter a Report Date field from a form

[DAHMB]

Hi all,

Using Access 2007

I have a report called Sunday School Attendance based on a Query called qryAttendance the query is as follows:

[Sql]
SELECT tblSundaySchoolAttendance.StudentID, tblSundaySchoolAttendance.AttendanceDate, [Students Extended].[File As]
FROM tblSundaySchoolAttendance INNER JOIN [Students Extended] ON tblSundaySchoolAttendance.StudentID=[Students Extended].StudentID;
[/Sql]

I have a form called frmFiler that opens with the report on the reports OnOpen Event. In the form I have a combobox called Filter1 that filters the report by StudentID, this box works. I also have a txtbox Called Filter2 this is formated as a ShortDate and is supposed to filter the AttendanceDate field of the Query. On the form I have a command button called btnApplyFilter that runs the following code from the OnClick Event:

Private Sub btnApplyFilter_Click()
Dim strSQL As String, intCounter As Integer
     'Build SQL String
     For intCounter = 1 To 2
       If Me("Filter" & intCounter) <> "" Then
         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
       End If
     Next
 
     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        Reports![Sunday School Attendance].Filter = strSQL
        Reports![Sunday School Attendance].FilterOn = True
    End If
End Sub
[i]

[/I

The first filter works, no problem. The Date filter keeps returning a Run Time Error 3464 Data Type Mismatch in Criteria Expression error. I have confirmed that the AttendanceDate field on the report and in the query are formated for ShortDate. So I don't understand where the problem lies.

I have been trying to figure this out for days!!!!!
Thank you for all and any help
Dan

[Jim Doherty]

Hi all,

Using Access 2007

I have a report called Sunday School Attendance based on a Query called qryAttendance the query is as follows:

[Sql]
SELECT tblSundaySchoolAttendance.StudentID, tblSundaySchoolAttendance.AttendanceDate, [Students Extended].[File As]
FROM tblSundaySchoolAttendance INNER JOIN [Students Extended] ON tblSundaySchoolAttendance.StudentID=[Students Extended].StudentID;
[/Sql]

I have a form called frmFiler that opens with the report on the reports OnOpen Event. In the form I have a combobox called Filter1 that filters the report by StudentID, this box works. I also have a txtbox Called Filter2 this is formated as a ShortDate and is supposed to filter the AttendanceDate field of the Query. On the form I have a command button called btnApplyFilter that runs the following code from the OnClick Event:

Private Sub btnApplyFilter_Click()
Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next
 
If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![Sunday School Attendance].Filter = strSQL
Reports![Sunday School Attendance].FilterOn = True
End If
End Sub
[i]

[/I

The first filter works, no problem. The Date filter keeps returning a Run Time Error 3464 Data Type Mismatch in Criteria Expression error. I have confirmed that the AttendanceDate field on the report and in the query are formated for ShortDate. So I don't understand where the problem lies.

I have been trying to figure this out for days!!!!!
Thank you for all and any help
Dan

Hi Dan,

You say you're working with a 'date' and yet I see no hash delimiters (#) being used here in the sql build only quote marks char(34) you might want to look at that

Also it might benefit you to add the occasional debug.print statement to your code sequences and view them in the immediate window that way you,ll see your sql build is you assign the value of the strsql variable to the debug.print statement

You might want to also have a look at Neopa's howto article here

http://www.thescripts.com/forum/thread575420.html

Regards

Jim :)

[DAHMB]

Hi Dan,

You say you're working with a 'date' and yet I see no hash delimiters (#) being used here in the sql build only quote marks char(34) you might want to look at that

Also it might benefit you to add the occasional debug.print statement to your code sequences and view them in the immediate window that way you,ll see your sql build is you assign the value of the strsql variable to the debug.print statement

You might want to also have a look at Neopa's howto article here

http://www.thescripts.com/forum/thread575420.html

Regards

Jim :)

Jim,

Thanks for the response. But I am a novice learning as I go and copying a lot of already invented wheels. I found the above code as an example for filtering text. I can not find a sample with dates included. Please point me in the correct direction in more beginners terms. I check the link you included and can not figure out the correct way to apply it here.

Thanks
Dan

[Jim Doherty]

Jim,

Thanks for the response. But I am a novice learning as I go and copying a lot of already invented wheels. I found the above code as an example for filtering text. I can not find a sample with dates included. Please point me in the correct direction in more beginners terms. I check the link you included and can not figure out the correct way to apply it here.

Thanks
Dan

Invented wheels are great if they have something in them to guide you like commented code 'lots do not' and unfortunately lots of beginners copy and paste stuff without understanding what the code is actually doing and then carry on on adding to the pile until we have a magical plate of spaghetti. I'm not saying that is happening in your case because you are wanting to understand the actual process involved here... so lets examine what is happening.

A FOR NEXT loop consisting of only 2 iterations is more or less a waste of time It is easier to type two end if statements and refer to your controls explicitly. That having been said, the principle here (for me) is not so much to be 'method' critical or to organise the program flow essentially... it is in this particular case, a question of knowing what the SQL is that is being built on the fly as the posted code illustrates, in order that we can fully understand if the compiler can deal with the built SQL and execute it.

In short the posted code is looping and grabbing the field name from the tag property of the control it references and then grabbing the 'value' presented by the control concatenating (appending) this to a string and stripping the last 5 characters from that string and rather hoping it will work as a valid SQL statement.

Fact is it does/did/probably excellently on the first control .....BUT.....I dare say the original syntax was built around something that did not include date fields as part of any looping process.

You have grabbed the code innocently without knowing the extent of its interaction with the potential data it might be faced with. I suppose thats the bottom line explanation.

Date fields in Access are wrapped in delimiting characters namely the HASH mark (#) which is CHAR(35) in the ansii character set. The posted code shows char(34) which is quotation marks.

The hash mark is the standard delimiter for dates. This is where your syntax problem lies. Now other systems might not have so much trouble and (can be more forgiving in this 'date' area) but one has to appreciate and understand what Access expects. (If you were using SQL Server I might not be emphasising this so much, various systems have various tolerances... please understand me)

To fully explain the ins and outs would require a book and here I sit with me in danger of reinventing the wheel in that area too. Much of what is understood is already written one simply needs to know where to target that material in order to gain a fuller understanding.

Now I know that you are using Access 2007 from a previous post of yours and that Access 2007 now has report on 'click events' from which you can navigate to screen forms to conduct anything that you wish to do ie: filter a form or go to a form at a specific record... and so on.

I cannot unfortunately replicate fully what you have there (on this box) because I do not personally use Access 2007 and have no need to presently as my scoped need for corporately supporting it is minimal. Suffice it to say this... in order to help you I have attached a zip file that shows where you need to be on this. It should open in Access 2007 ok. I hope it explains it a little to you, but if not get back to me. :))

Don't be put off by the use of commands like TYPEOF or the useage of the listbox they are included merely to try and make my illustrative example fit 'your' code (and at the same time maybe give you an idea of differences in method with different control) as opposed to me writing the solution to this for you.

Remember I do not have exactly what you have there in front of you, so its all guesswork my end so to speak. If you break apart the code and go into help the command useages are fully explained.

Regards

Jim :)

[DAHMB]

Invented wheels are great if they have something in them to guide you like commented code 'lots do not' and unfortunately lots of beginners copy and paste stuff without understanding what the code is actually doing and then carry on on adding to the pile until we have a magical plate of spaghetti. I'm not saying that is happening in your case because you are wanting to understand the actual process involved here... so lets examine what is happening.

A FOR NEXT loop consisting of only 2 iterations is more or less a waste of time It is easier to type two end if statements and refer to your controls explicitly. That having been said, the principle here (for me) is not so much to be 'method' critical or to organise the program flow essentially... it is in this particular case, a question of knowing what the SQL is that is being built on the fly as the posted code illustrates, in order that we can fully understand if the compiler can deal with the built SQL and execute it.

In short the posted code is looping and grabbing the field name from the tag property of the control it references and then grabbing the 'value' presented by the control concatenating (appending) this to a string and stripping the last 5 characters from that string and rather hoping it will work as a valid SQL statement.

Fact is it does/did/probably excellently on the first control .....BUT.....I dare say the original syntax was built around something that did not include date fields as part of any looping process.

You have grabbed the code innocently without knowing the extent of its interaction with the potential data it might be faced with. I suppose thats the bottom line explanation.

Date fields in Access are wrapped in delimiting characters namely the HASH mark (#) which is CHAR(35) in the ansii character set. The posted code shows char(34) which is quotation marks.

The hash mark is the standard delimiter for dates. This is where your syntax problem lies. Now other systems might not have so much trouble and (can be more forgiving in this 'date' area) but one has to appreciate and understand what Access expects. (If you were using SQL Server I might not be emphasising this so much, various systems have various tolerances... please understand me)

To fully explain the ins and outs would require a book and here I sit with me in danger of reinventing the wheel in that area too. Much of what is understood is already written one simply needs to know where to target that material in order to gain a fuller understanding.

Now I know that you are using Access 2007 from a previous post of yours and that Access 2007 now has report on 'click events' from which you can navigate to screen forms to conduct anything that you wish to do ie: filter a form or go to a form at a specific record... and so on.

I cannot unfortunately replicate fully what you have there (on this box) because I do not personally use Access 2007 and have no need to presently as my scoped need for corporately supporting it is minimal. Suffice it to say this... in order to help you I have attached a zip file that shows where you need to be on this. It should open in Access 2007 ok. I hope it explains it a little to you, but if not get back to me. :))

Don't be put off by the use of commands like TYPEOF or the useage of the listbox they are included merely to try and make my illustrative example fit 'your' code (and at the same time maybe give you an idea of differences in method with different control) as opposed to me writing the solution to this for you.

Remember I do not have exactly what you have there in front of you, so its all guesswork my end so to speak. If you break apart the code and go into help the command useages are fully explained.

Regards

Jim :)

Jim,

Thank you very much!! I am still studying what you sent, it is great. I was able to get my filter working perfectly!!!

Thanks
Dan

[Jim Doherty]

Jim,

Thank you very much!! I am still studying what you sent, it is great. I was able to get my filter working perfectly!!!

Thanks
Dan

I am so pleased helped you!

Regards

Jim :)