473,549 Members | 2,597 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filter a Report Date field from a form

147 New Member
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 tblSundaySchool Attendance.Stud entID, tblSundaySchool Attendance.Atte ndanceDate, [Students Extended].[File As]
FROM tblSundaySchool Attendance INNER JOIN [Students Extended] ON tblSundaySchool Attendance.Stud entID=[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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnApplyFilter_Click()
  2. Dim strSQL As String, intCounter As Integer
  3.      'Build SQL String
  4.      For intCounter = 1 To 2
  5.        If Me("Filter" & intCounter) <> "" Then
  6.          strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
  7.        End If
  8.      Next
  9.  
  10.      If strSQL <> "" Then
  11.         'Strip Last " And "
  12.         strSQL = left(strSQL, (Len(strSQL) - 5))
  13.         'Set the Filter property
  14.         Reports![Sunday School Attendance].Filter = strSQL
  15.         Reports![Sunday School Attendance].FilterOn = True
  16.     End If
  17. End Sub
  18. [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
Feb 4 '08 #1
5 4006
Jim Doherty
897 Recognized Expert Contributor
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 tblSundaySchool Attendance.Stud entID, tblSundaySchool Attendance.Atte ndanceDate, [Students Extended].[File As]
FROM tblSundaySchool Attendance INNER JOIN [Students Extended] ON tblSundaySchool Attendance.Stud entID=[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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnApplyFilter_Click()
  2. Dim strSQL As String, intCounter As Integer
  3. 'Build SQL String
  4. For intCounter = 1 To 2
  5. If Me("Filter" & intCounter) <> "" Then
  6. strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
  7. End If
  8. Next
  9.  
  10. If strSQL <> "" Then
  11. 'Strip Last " And "
  12. strSQL = left(strSQL, (Len(strSQL) - 5))
  13. 'Set the Filter property
  14. Reports![Sunday School Attendance].Filter = strSQL
  15. Reports![Sunday School Attendance].FilterOn = True
  16. End If
  17. End Sub
  18. [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 :)
Feb 4 '08 #2
DAHMB
147 New Member
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
Feb 5 '08 #3
Jim Doherty
897 Recognized Expert Contributor
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 :)
Attached Files
File Type: zip DanFilter.zip (26.7 KB, 186 views)
Feb 5 '08 #4
DAHMB
147 New Member
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
Feb 8 '08 #5
Jim Doherty
897 Recognized Expert Contributor
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 :)
Feb 9 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
18200
by: arthur-e | last post by:
I can filter a query for a report on a field of a subform BUT now.... I'd like to be able to select more than one item in a multi-list box to select all the records. ALSO to use two fields (or more) fields to filter a query based on selections in two or more fields: All the records with NAME = all the names selected in a list box...
0
6459
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the report are based on the same View addressed in the Record Source of both as 'dbo.CLMain_vw'. The View resides in a SQL Server 2000 database. There are...
3
14294
by: Nathan Bloomfield | last post by:
I am having difficulty filtering dates for a report. I have written the following code on a popup form which works with regular date fields , however, I am now trying to apply the same concept to a field which is formatted as date/time i.e. now() DoCmd.OpenReport "rptOpenLabels", acViewPreview, , " Between #" & Format(Me!, "mm/dd/yy") & "#...
3
2896
by: Afton | last post by:
I would like to make a form that filters a report by Supervisor and by starting and ending date. I have the supervisors in a combo box, and that works. However, I do not know how to code to let the user enter a starting and ending date and only show the records between those dates. The code that I have so far does not work for the dates, but...
1
16371
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used VBA to create SQL statements, then apply them. Now that I have been using Access/VBA for awhile, I am wondering if there is a simpler way to do...
2
5515
by: jcf378 | last post by:
hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by Form" in order to search for records based on this form, I would like to be able to enter a value in this "days" control so that I can filter records on...
1
5611
by: Cara Murphy | last post by:
Hi There! Hoping you are able to help me with a filtering scenario: I have created a pop up form (Filter) to filter a report (Open Issues), which currently has a number of combo boxes which apply the filters to the fields in the report. I would like to use this form to filter the report within a date range (Raised Date From and Raised...
3
2625
by: franc sutherland | last post by:
Hello, I have a report which I filter using the me.filter command in the OnOpen event. Me.Filter = "OrderID=" & Forms!variable_form_name! Me.FilterOn = True I want to be able to open that report, filtered, from different forms. How do I carry the name of the current form into the
3
2515
by: Gord | last post by:
I'm trying to filter a report with a date in VB code. If I type an actual date bracketed with the pound symbol (i.e. #3-Jul-08#) I can get the filter to work. I can't seem to get it to work by using a variable. Am I trying to do the impossible? I've tried using a date variable that I know contains a valid date. I've tried bracketing...
0
7464
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7734
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7497
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6065
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5385
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5107
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3512
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1960
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
781
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.