Query assistance

D

Duane

Hello,

I am receiving and error when I try to execute the following code;

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Min(Employee.A1H) AS MinOfA1H FROM
Employee WHERE Employee.Group=[Forms]![frmEmployee]![Group] AND
Employee.A1H>0", dbOpenDynaset)
iHours = rst!MinOfA1H
MsgBox iHours

Run Time Error 3061
Too few parameters. Expected 1.

If I replace the =[Forms]![frmEmployee]![Group] with a hard coded =7 then it
works fine. I need for it to be dynamic in the sense that I need the
MinOfA1H of the group the employee is assigned to and that could be from
group 1 to group 7. The group field is an integer field in the table.

If I put a checkmark in the A1 field to assign an employee to the 1st shift
A list for overtime, I want run this query to get the minimum number of
hours worked for all employees in that group.

Thank you in advance.
 
B

Beetle

The reference to your form control needs to be outside of the quotes;

Dim StrSQL As String

strSQL = "SELECT Min(Employee.A1H) AS MinOfA1H FROM Employee" _
" WHERE Employee.Group= " & [Forms]![frmEmployee]![Group] & _
" AND Employee.A1H>0;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

The above assumes the the Group is an integer data type. If it is text you
will need to add additional quotes around it.
 
D

Duane

Yes, the form is open and the group control is populated. I have even tried
to trick it so to speak by using a variable with the value in the control.
I am running the code when I put a checkmark in the A1 Checkbox. I check to
see if the value of the checkbox is -1. If it is - I then run the code.

iGroup = Forms!frmEmployee!Group

If A1 = -1 Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Min(Employee.A1H) AS MinOfA1H FROM
Employee WHERE Employee.Group=iGroup AND Employee.A1H>0", dbOpenDynaset)
iHours = rst!MinOfA1H
MsgBox iHours
End If

I have stepped through the code and the variable receives the value but the
code breaks.


KC-Mass said:
Is the form open and the cntrol populated when you try to run it?


Duane said:
Hello,

I am receiving and error when I try to execute the following code;

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Min(Employee.A1H) AS MinOfA1H FROM
Employee WHERE Employee.Group=[Forms]![frmEmployee]![Group] AND
Employee.A1H>0", dbOpenDynaset)
iHours = rst!MinOfA1H
MsgBox iHours

Run Time Error 3061
Too few parameters. Expected 1.

If I replace the =[Forms]![frmEmployee]![Group] with a hard coded =7 then
it works fine. I need for it to be dynamic in the sense that I need the
MinOfA1H of the group the employee is assigned to and that could be from
group 1 to group 7. The group field is an integer field in the table.

If I put a checkmark in the A1 field to assign an employee to the 1st
shift A list for overtime, I want run this query to get the minimum
number of hours worked for all employees in that group.

Thank you in advance.
Click to expand...
Click to expand...
 
D

Duane

Thank you Bettle. That did the trick.


Beetle said:
The reference to your form control needs to be outside of the quotes;

Dim StrSQL As String

strSQL = "SELECT Min(Employee.A1H) AS MinOfA1H FROM Employee" _
" WHERE Employee.Group= " & [Forms]![frmEmployee]![Group] & _
" AND Employee.A1H>0;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

The above assumes the the Group is an integer data type. If it is text you
will need to add additional quotes around it.
--
_________

Sean Bailey


Duane said:
Hello,

I am receiving and error when I try to execute the following code;

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Min(Employee.A1H) AS MinOfA1H FROM
Employee WHERE Employee.Group=[Forms]![frmEmployee]![Group] AND
Employee.A1H>0", dbOpenDynaset)
iHours = rst!MinOfA1H
MsgBox iHours

Run Time Error 3061
Too few parameters. Expected 1.

If I replace the =[Forms]![frmEmployee]![Group] with a hard coded =7 then
it
works fine. I need for it to be dynamic in the sense that I need the
MinOfA1H of the group the employee is assigned to and that could be from
group 1 to group 7. The group field is an integer field in the table.

If I put a checkmark in the A1 field to assign an employee to the 1st
shift
A list for overtime, I want run this query to get the minimum number of
hours worked for all employees in that group.

Thank you in advance.
Click to expand...
Click to expand...
 
L

Larry Linson

It would also be good to rename the "Group" Field/Control; "Group" is an
access reserved word, and using reserved words for object names can cause
errors and be misleading. As yours is now working, it apparently is not, in
your case causing errors, but not using reserved words is a good habit to
get into.

Larry Linson
Microsoft Office Access MVP

Duane said:
Thank you Bettle. That did the trick.


Beetle said:
The reference to your form control needs to be outside of the quotes;

Dim StrSQL As String

strSQL = "SELECT Min(Employee.A1H) AS MinOfA1H FROM Employee" _
" WHERE Employee.Group= " & [Forms]![frmEmployee]![Group] & _
" AND Employee.A1H>0;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

The above assumes the the Group is an integer data type. If it is text
you
will need to add additional quotes around it.
--
_________

Sean Bailey


Duane said:
Hello,

I am receiving and error when I try to execute the following code;

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Min(Employee.A1H) AS MinOfA1H FROM
Employee WHERE Employee.Group=[Forms]![frmEmployee]![Group] AND
Employee.A1H>0", dbOpenDynaset)
iHours = rst!MinOfA1H
MsgBox iHours

Run Time Error 3061
Too few parameters. Expected 1.

If I replace the =[Forms]![frmEmployee]![Group] with a hard coded =7
then it
works fine. I need for it to be dynamic in the sense that I need the
MinOfA1H of the group the employee is assigned to and that could be from
group 1 to group 7. The group field is an integer field in the table.

If I put a checkmark in the A1 field to assign an employee to the 1st
shift
A list for overtime, I want run this query to get the minimum number of
hours worked for all employees in that group.

Thank you in advance.
Click to expand...
Click to expand...



__________ Information from ESET Smart Security, version of virus
signature database 4001 (20090411) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...



__________ Information from ESET Smart Security, version of virus signature database 4001 (20090411) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
D

Duane

Thank you Larry. I will remember that bit of advice. Sometimes it is just
so difficult to remember everything. Especially as a self taught novice.

Not that it is an excuse because it isn't, I was asked to re-write this
database that was originally done in paradox and I didn't go through the
table and appropriately rename the field(s). I just imported them as is.
Just another important lesson to be added to the book of hard knocks....

Larry Linson said:
It would also be good to rename the "Group" Field/Control; "Group" is an
access reserved word, and using reserved words for object names can cause
errors and be misleading. As yours is now working, it apparently is not,
in your case causing errors, but not using reserved words is a good habit
to get into.

Larry Linson
Microsoft Office Access MVP

Duane said:
Thank you Bettle. That did the trick.


Beetle said:
The reference to your form control needs to be outside of the quotes;

Dim StrSQL As String

strSQL = "SELECT Min(Employee.A1H) AS MinOfA1H FROM Employee" _
" WHERE Employee.Group= " & [Forms]![frmEmployee]![Group] & _
" AND Employee.A1H>0;"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

The above assumes the the Group is an integer data type. If it is text
you
will need to add additional quotes around it.
--
_________

Sean Bailey


:

Hello,

I am receiving and error when I try to execute the following code;

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT Min(Employee.A1H) AS MinOfA1H FROM
Employee WHERE Employee.Group=[Forms]![frmEmployee]![Group] AND
Employee.A1H>0", dbOpenDynaset)
iHours = rst!MinOfA1H
MsgBox iHours

Run Time Error 3061
Too few parameters. Expected 1.

If I replace the =[Forms]![frmEmployee]![Group] with a hard coded =7
then it
works fine. I need for it to be dynamic in the sense that I need the
MinOfA1H of the group the employee is assigned to and that could be
from
group 1 to group 7. The group field is an integer field in the table.

If I put a checkmark in the A1 field to assign an employee to the 1st
shift
A list for overtime, I want run this query to get the minimum number of
hours worked for all employees in that group.

Thank you in advance.
Click to expand...



__________ Information from ESET Smart Security, version of virus
signature database 4001 (20090411) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...



__________ Information from ESET Smart Security, version of virus
signature database 4001 (20090411) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with report recordsource 2
Work Budget Salary vs Real Time Salary 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Help with Sql statement 8
Count number of records by date 2
How to display zero value in blank fields of Cross Tab query 0
Append Crosstab Query to Existing Table 0
Report Group Footer DLookUp 0

Top