The value of a text box on a form can be used as a parameter in a query.
However, you cannot use text boxes to supply operators for the query.
There are various workarounds. If you drop the combo, you could do some
stuff such as:
Between Nz([Forms]![StatementGBP]![INST_DATE],
[Forms]![StatementGBP]![End_DATE])
And
Nz([Forms]![StatementGBP]![End_DATE],[Forms]![StatementGBP]![INST_DATE])
The way that works is that if the user supplies both dates, it treats it as
between, but if they supply only one, it treats as just that date.
Another alternative is to get the to enter both dates the same if they just
want one date, and then use:
WHERE (([Forms]![StatementGBP]![INST_DATE] Is Null)
OR ([MyDateField] >= [Forms]![StatementGBP]![INST_DATE]))
AND (([Forms]![StatementGBP]![End_DATE] Is Null)
OR ([MyDateField] <= ([Forms]![StatementGBP]![End_DATE]))
This one works like this:
- start date only: all records from this date onwards;
- end date only: all records up to this date;
- both dates: only records between the 2 dates;
- no dates: all records.
To get one date only, use enters the same start and end date.
HOWEVER, this whole approach gets very tedious, inefficient, and difficult
to maintain when there are lots of filtering options. A better option is to
build a Filter string form only the boxes where the user entered something.
There's an example of how to do that in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mark Roughton" <mr*******@qbe-europe.comwrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...
>I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date
The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so for example the result in the unbound
textbox could be =01/01/2006 or >=01/02/2006 or <=01/03/2005.
Upon pressing a Command button the tableview of a form is opened whose
record source is a select query.
If I set the Criteria in the select query to the date in the form's
date combox the results are displayed
e.g. Criteria =[Forms]![StatementGBP]![INST_DATE]
However I'm trying to set the criteria to either the value in the
unbound textbox or the combination of the two combo boxes so that the
results will be for whichever criteria the user specified i.e.
"=01/01/2006" or ">=01/02/2006" or "<=01/03/2005".
The Criteria ought to be " =[Forms]![StatementGBP]![StatusBox]" where
Statusbox is the name of the unbound control
I have also tried "Like (([Forms]![StatementGBP]![ChoiceBox]) &
([Forms]![StatementGBP]![INST_DATE]))" where both of the comboox values
are concatenated.
Whatever I try doesn't seem to work, I have tried various formatting
functions within the select query, all to no avail. Does anyone know
the solution?
