Form requires parameter

[bigbear]

I have a simple payroll database. There is a query which SELECTS and
SUMS several fields (eg tax and salary) to shows their total values
since the beginning of the year. This operates from a table hic stores
the details fo each fortnighthtly pay for each person. The query works
fine and has a WHERE clause which reads
WHERE paydate > "#1/01/2008#".
When I run the query no parameter is requested but when I attach it to
a form and then run the form it always asks for the paydate parameter.
If I type in 1/01/2008 the form is fine.
How can I make the form run with the value stored in the query?
Thanks in advance.

 

[Bob Larson]

Typically, in these situations, you have either referenced something with
the wrong spelling, although it could be referencing the field from some
other location. How are you opening the query? Are you using code and, if
so, what is it?

--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

 

[bigbear]

No code used at thisstage. The query as described runs properly from
the normal toolbar. If I then create a new form, bind it to the query,
add a couple of fieds from the query and view the form I get asked for
the parameter.

 

[boblarson]

What is the exact entire SQL of the query and what are the table field names?


--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[bigbear]

Thanks Bob for you help with this.

The SQL is

SELECT tb_Pays.fk_StaffNum, Sum(tb_Pays.Salary) AS SumOfSalary,
Sum(tb_Pays.OSAllow) AS SumOfOSAllow, Sum(tb_Pays.EndEmpPay) AS
SumOfEndEmpPay, Sum(tb_Pays.Tax) AS SumOfTax, Sum(tb_Pays.EOE) AS
SumOfEOE, Sum(tb_Pays.Packaged) AS SumOfPackaged, Sum(tb_Pays.Rent) AS
SumOfRent, Sum(tb_Pays.Medical) AS SumOfMedical,
Sum(tb_Pays.Insurance) AS SumOfInsurance, Sum(tb_Pays.EmployeeSup) AS
SumOfEmployeeSup, Sum(tb_Pays.EmployerSup) AS SumOfEmployerSup
FROM tb_Pays
WHERE (((tb_Pays.PayDate)>#1/1/2008#))
GROUP BY tb_Pays.fk_StaffNum;

There is only one table used in this: tb_Pays and the fields all seem
OK as they were selected from the query builder. As I said, the query
runs properly until it nis attached to a form.

Bear

 

[John W. Vinson]

I have a simple payroll database. There is a query which SELECTS and
SUMS several fields (eg tax and salary) to shows their total values
since the beginning of the year. This operates from a table hic stores
the details fo each fortnighthtly pay for each person. The query works
fine and has a WHERE clause which reads
WHERE paydate > "#1/01/2008#".
When I run the query no parameter is requested but when I attach it to
a form and then run the form it always asks for the paydate parameter.
If I type in 1/01/2008 the form is fine.
How can I make the form run with the value stored in the query?
Thanks in advance.

Well, don't use both quotes (text string delimiters) AND octothorpes (date
delimiters). In fact if you want the form to display values from this year,
don't use a literal criterion at all - use a criterion on paydate of

= DateSerial(Year(Date()), 1, 1)

to always use January 1 of the current year.

The fact that the form is asking for paydate as a parameter suggests that the
Paydate field does not exist in the Form's Recordsource. Does it?

 

[John W. Vinson]

Thanks Bob for you help with this.

The SQL is

SELECT tb_Pays.fk_StaffNum, Sum(tb_Pays.Salary) AS SumOfSalary,
Sum(tb_Pays.OSAllow) AS SumOfOSAllow, Sum(tb_Pays.EndEmpPay) AS
SumOfEndEmpPay, Sum(tb_Pays.Tax) AS SumOfTax, Sum(tb_Pays.EOE) AS
SumOfEOE, Sum(tb_Pays.Packaged) AS SumOfPackaged, Sum(tb_Pays.Rent) AS
SumOfRent, Sum(tb_Pays.Medical) AS SumOfMedical,
Sum(tb_Pays.Insurance) AS SumOfInsurance, Sum(tb_Pays.EmployeeSup) AS
SumOfEmployeeSup, Sum(tb_Pays.EmployerSup) AS SumOfEmployerSup
FROM tb_Pays
WHERE (((tb_Pays.PayDate)>#1/1/2008#))
GROUP BY tb_Pays.fk_StaffNum;

There is only one table used in this: tb_Pays and the fields all seem
OK as they were selected from the query builder. As I said, the query
runs properly until it nis attached to a form.

Bear

Are you trying to display the PayDate in a control on the form, or to sort by
it in the Form's Sorting and Grouping property? If so you will get this
message, since the PayDate isn't included in your SELECT clause, only as a
criterion.

 

[bigbear]

Many thanks again - all solved. I had based this form on another form
and failed to remove the existing sort condition which included the
PayDate field. Felt very dumb when I realised!

And thanks also for the advice re using the function for 1st of
current year.

Bear