Query problems

[Jon Rowlan]

Trying to resolve a problem that I reported in the reports section, I am
struggling with queries that contain form variables.

If I use this :

SELECT * FROM ReportTestData WHERE (((ReportTestData.Year) Between
Forms!ExecSalesReport![Reference Year]-Forms!ExecSalesReport![Number Of
Years] And Forms!ExecSalesReport!ReferenceYear));

With the form on screen the query returns no rows.

However, if I close the form so that the query prompts for the answers and I
enter the same details as shown on my form, the query returns all the rows
that I need.

Can someone please tell me where I am going wrong? I'm sure its something
obvious but I just can't see it??

Is it that the query is comparing a field contents, quite literally, with
"Forms!ExecSalesReport!ReferenceYear" and why does this not come up with an
error rather than returning no rows?

thanks,

jON

 

[Marshall Barton]

Trying to resolve a problem that I reported in the reports section, I am
struggling with queries that contain form variables.

If I use this :

SELECT * FROM ReportTestData WHERE (((ReportTestData.Year) Between
Forms!ExecSalesReport![Reference Year]-Forms!ExecSalesReport![Number Of
Years] And Forms!ExecSalesReport!ReferenceYear));

With the form on screen the query returns no rows.

However, if I close the form so that the query prompts for the answers and I
enter the same details as shown on my form, the query returns all the rows
that I need.

Can someone please tell me where I am going wrong? I'm sure its something
obvious but I just can't see it??

Is it that the query is comparing a field contents, quite literally, with
"Forms!ExecSalesReport!ReferenceYear" and why does this not come up with an
error rather than returning no rows?

Well, yes, it is comparing the field to the form value.
That's what you tols it to do. There appears to be no error
because the comparison is legal (but possibly nonsensical).

I suspect the the ReportTestData.Year field in the table is
in a different form or range than the value on the form???
For example, if the field is a Date field (e.g. 12/4/05) but
the textbox has just a number (e.g. 2005) or if they are all
dates the subtraction is useless.

Post back with these details and we'll see if we can sort it
out for you.