Is it possible to query an infopath form between two dates

G

G Dane

Have based an InfoPath form on an AccessDB and want users to query the DB
from the infopath form.

Is it possible to set up a generic 'Between' 'And' statement, to retrieve
records between two dates but which will also work on the infopath form - ie
dates that a user enters on the form?
 
G

G Dane

Apologies, posted the following as a separate question instead of part of the
original thread.

--------------------------------------------

Have replicated the solution at:
http://enterprise-solutions.swits.net/infopath/filter-repeating-table-date-range-rules.htm But no data is being returned!

Am I missing something glaringly obvious here? ie do I need to replicate the
query using different field names within an existing table? ie Will it work
by simply basing the query on an existing table or would I simply be wasting
my time.

In short, can the solution be simply dropped into any AccessDB and just work
with infopath as is.

At root I think my difficulty is being unable to see just where the
'TasksSortedByDate' query is drawing its data from!
 
S

S.Y.M. Wong-A-Ton

But no data is being returned!

Did you create the MS Access table and query from scratch like in the
solution or did you use your own table?
Am I missing something glaringly obvious here? ie do I need to replicate the
query using different field names within an existing table? ie Will it work
by simply basing the query on an existing table or would I simply be wasting
my time.
Click to expand...

You need to create a query for your own table. The "trick" in this solution
is to create a query that will convert the date field in your database table
into a format that will easily lend itself to using the "less than" and
"greater than" comparison operators within InfoPath. Date comparison would
otherwise be very difficult, if not impossible, and you'd have to resort to
code.
In short, can the solution be simply dropped into any AccessDB and just work
with infopath as is.
Click to expand...

No. Almost all of the solutions listed on my website are samples on how you
might be able to get things done in InfoPath. You need to modify them to suit
your own needs.
At root I think my difficulty is being unable to see just where the
'TasksSortedByDate' query is drawing its data from!
Click to expand...

An MS Access query draws its data from an MS Access table. The query was
incorporated in the solution to be able to format the date field of the table
to the format "yyyy-mm-dd". If you compare the fields in the table used in
the solution with the fields of the query used in the solution, you will see
that the query returns the same fields as the table does. The only difference
is that the date field is formatted according to the specified format.
 
G

G Dane

Many thanks,

Have now played further and now understand what's going on and how to apply
it to my own situation.

Your solution works a treat and fully resolves the problem I was dealing
with - many thanks.
 
G

G Dane

One glitch has emerged which escaped my notice:

1. I enter two dates 01/01/1973 and 31/12/1973 inorder to retreive two
records that I know to exist between those two dates.

2. Entering the two dates fails to retrieve the records.

3. I repeat the exercise with the dates 01/01/1972 and 31/12/1973, the query
succeeds and the records are retreived.

5. ie The minimum date range that can be retrieved is a year. I want to
narrow it.

4. I suspect that the "yyyy/mm/dd' format has something to do with this as
the field which displays the retrieved info, displays it in that format. ie I
enter the criteria in dd/mm/yyyy format but the records are displayed in the
repeating table in yyyy/mm/dd' format.
 
S

S.Y.M. Wong-A-Ton

Thank you for telling me about the bug; I will look into it and test it this
weekend. By the way, do you get the same results using a "yyyy-mm-dd" format
instead of a "yyyy/mm/dd" format?
 
G

G Dane

Thanks, have replicated your solution again and - extraordinary - the
"yyyy-mm-dd", format as opposed to the "yyyy/mm/dd" format, does make a
difference.

The glitch now looks to be sorted - with the date filtering as narrow or as
wide as I want it.

Cheers.
 
S

S.Y.M. Wong-A-Ton

Great!
---
S.Y.M. Wong-A-Ton


G Dane said:
Thanks, have replicated your solution again and - extraordinary - the
"yyyy-mm-dd", format as opposed to the "yyyy/mm/dd" format, does make a
difference.

The glitch now looks to be sorted - with the date filtering as narrow or as
wide as I want it.

Cheers.
Click to expand...
 
Top