Query records in a table between dates.

[sboyd]

I have an SQL table that has records with a date field. I want to use
an InfoPath form to query the database table for records between two
dates. I know how to filter on one date but I haven't figured out how
to query between two dates. I tried adding two date fields under
MyFields but so far, I haven't had any luck.

Does anyone have a solution?

Thanks
sboyd

 

[S.Y.M. Wong-A-Ton]

Have you already seen this article
http://www.bizsupportonline.net/infopath2003/filter-repeating-table-date-range-rules.htm

If you're using SQL Server instead of Access, you can create a View (instead
of a Query) to be able to do the filtering.

 

[sboyd]

Have you already seen this article?http://www.bizsupportonline.net/infopath2003/filter-repeating-table-d...

If you're using SQL Server instead of Access, you can create a View (instead
of a Query) to be able to do the filtering.
---
S.Y.M. Wong-A-Tonhttp://www.bizsupportonline.net/infopath2007/(InfoPath 2007 articles,
tutorials, and solutions)







- Show quoted text -

I need the user to be able to enter the two dates and then have the
form to return that data. So would a view work in this case and if
so, what would be my process. I haven't had to query a series of
items before.

 

[S.Y.M. Wong-A-Ton]

Yes, a view is basically the same as a query. In this case, you'd use it to
be able to filter on dates using the date format yyyy-mm-dd. The process
you'd have to follow is outlined in the article; it does not differ between
Access and SQL Server. The only difference is that you'd be using a view.

Yes, your users can enter dates. Do you see the my:startDate and my:endDate
in Figure 1? You use these fields to enter your dates, and then use their
values to filter the rows that are returned from the view.

I'm not sure what your level of experience is with InfoPath or SQL Server,
so cannot provide more info unless you let me know where you got stuck.

 

[sboyd]

Yes, a view is basically the same as a query. In this case, you'd use it to
be able to filter on dates using the date format yyyy-mm-dd. The process
you'd have to follow is outlined in the article; it does not differ between
Access and SQL Server. The only difference is that you'd be using a view.

Yes, your users can enter dates. Do you see the my:startDate and my:endDate
in Figure 1? You use these fields to enter your dates, and then use their
values to filter the rows that are returned from the view.

I'm not sure what your level of experience is with InfoPath or SQL Server,
so cannot provide more info unless you let me know where you got stuck.

---
S.Y.M. Wong-A-Tonhttp://www.bizsupportonline.net/infopath2007/(InfoPath 2007 articles,
tutorials, and solutions)






- Show quoted text -

I've gotten all of information on the form and the appropriate rules
set. I'm actually dealing with two tables so I may have it set up
incorrectly. I added the table and added a repeating section for the
parent table and a repeating table within the section for the child
table. My start and end dates are within the table area but outside
the repeating section area. My current problem is the form is trying
to pull all records in the database as soon as you preview the form
before you enter the start and end dates.

Does the start and end dates need to be outside the table? My
infopath form is locking up every time it is opened.

Thanks
Sboyd

 

[S.Y.M. Wong-A-Ton]

Did you add a "Query using a data connection" rule for when the form opens?
To prevent all records from being retrieved when the form opens, you have to
skip steps 23, 24, and 25 in the article. This way the form will open, but
not automatically display all records. You'll also have to add an extra
button on the form, so that the user can run the query and retrieve the
records once they've entered the dates. I'm not sure whether this will give
you the performance you require, but give it a go and let me know how it
works. In the worst case scenario, you'd have to write code to do the
filtering on the server (using ADO.NET and a stored procedure) before the
records are returned and shown in InfoPath.