Parameter Criteria from a forms Text box - 2003 - not working

[Billp]

Hi,

I take a start date and an end date from a forms two text boxes.
If the query is run without the criteria all is well.


Between [Forms]![frmDialogWorksBooksReport]![txtStartDate] And
[Forms]![frmDialogWorksBooksReport]![txtEndDate]

Adding the criteria causes a dialogue box to open and ask for data - start
and end dates e.g enter value for
[Forms]![frmDialogWorksBooksReport]![txtStartDate]

If I run it without the forms refrenence just with an easy 'Enter date here"
- checks out OK.

Yet in a similar form where this is used the query takes the information
form the form.

Any reasons why it doesn't work in this application - Office access 2003.

Thanks in advance

 

[Allen Browne]

When Access finds a name such as:
[Forms]![frmDialogWorksBooksReport]![txtStartDate]
in a query, it passes the reference to a built-in routine called the
Expression Service (ES.) If there is a form of that name, and it's open in a
suitable view (not design), and it contains a control with that name, and
it's a control that has a value (not a label or button or ...), then the ES
reads the value from the control on the form and passes it back to the query
parser.

If any of those conditions are not met, the ES notifies the query parser
that it did not find a value, and so Access pops up a dialog asking the user
to supply the value for the parameter.

So, if you are getting the parameter dialog when you don't expect to, check:
a) Is the form open?
b) Is that it's name exactly?
c) Does that form have a control of that name?
d) Is the control of a suitable type to have a value?
e) Does the query contain declared parameters that are misspelled?
f) Are there misspelled names in the query's Filter or OrderBy properties,
or in those properties of the underlying table?

There are other things that could go wrong too. If the text box is unbound,
set its Format property to General Date or similar, so it can only accept a
suitable data type. If you clear this box with code, set it to Null (not a
zero-length string i.e. "".) And also declare the parameters in your query
so JET interprets the values correctly.

 

[Brian]

1. Just to be sure, are the form name and both controls named EXACTLY as in
the query?
2. Is the form open when the query runs? If not, it would behave exactly as
you describe. That is, while the query would work when run from a button
click on the form (guaranteeing that the form is, indeed open at the moment),
the query would not work if run by double-clicking the query itself (unless
the form happens to be open at that moment).

 

[Doctor]

The reason for your issue is that the form that contains your Dates must be
open with information in it when you run the query.

 

[John Spencer]

Two must common causes of the problem you are experiencing.
One - the form MUST be open
Two - the references must be spelled accurately. For instance, you have
frmDialogWorksBooksReport which perhaps should be frmDialogWorkBooksReport.
Note the absence/presence of the "s" after "Work" in the name of the form.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Billp]

Thanks too all for your kind input.

I checked everything and made sure that all the bases were covered.
Still I could not get the thing to work.

Then I remade the query under a different name.
Relinked it to the form - and it worked.

Somewhere I read that microsoft can get sort of hard wired and a little
change will be overlooked even though it should work.
The original query was corrupt - I'm not sure, however reconstructing it
allowed the criteria to work.

Thanks again