Passing SUM value from query to textbox

G

Guest

Hello!
Got stuck trying to pass vallue from query to a textbox.
Query is:
SELECT Sum(DateDiff("n",[StartTime],[EndTime])/60) AS TotalTime FROM tblTime
WHERE (((tblTime.ReviewNumber)=[Forms]![frmReview]![txtReviewNumber])) GROUP
BY tblTime.ReviewNumber

It runs when On Activate, On Current or On Load event of frmReview is
invoked and should pass that single value to textbox txtTotalTime on same
form.
I'm getting error message:
"Run-time error '3061': Too few parameters. Expected 1."

This is what I've done so far:

Private Sub TimeTotal()
Dim db As Database
Dim rs As Recordset
Dim strTimeTotal As Variant
Dim SQLStr As String

SQLStr = "SELECT Sum(DateDiff(""n"",[StartTime],[EndTime])/60) AS
TotalTime FROM tblTime WHERE (((tblTime.ReviewNumber)="
SQLStr = SQLStr & [Forms]![frmReview]![txtReviewNumber] & ")) GROUP BY
tblTime.ReviewNumber"

Set db = CurrentDb()
'Set rs = db.OpenRecordset(SQLStr)
If IsNull(db.OpenRecordset(SQLStr)) Then
[Forms]![frmReview]![txtTotalTime] = 0
Else
Set rs = db.OpenRecordset(SQLStr)
[Forms]![frmReview]![txtTotalTime] = rs
End If
[Forms]![frmReview]![txtTotalTime] = strTimeTotal

End Sub

Please help!

Deki PA
 
G

Guest

Ensure that the table name and field names are spelled correctly (and that
they exist). Ensure that the data types are correct, too. StartTime and
EndTime must be Date/Time fields, ReviewNumber must be numerical and
Forms!frmReview!txtReviewNumber must be numerical. If the ReviewNumber is a
text string, you'll need to change the WHERE clause to use string criteria,
not numerical criteria.

Try the following simpler code (assuming ReviewNumber and the text box
values are numerical):

Private Sub TimeTotal()

On Error GoTo ErrHandler

Dim rs As Recordset
Dim SQLStr As String
Dim fOpenedRecSet As Boolean

SQLStr = "SELECT Sum(DateDiff(""n"", StartTime, EndTime)/60) AS
TotalTime FROM tblTime WHERE (ReviewNumber="
SQLStr = SQLStr & Forms!frmReview!txtReviewNumber & ") GROUP BY
ReviewNumber"

Set rs = CurrentDb().OpenRecordset(SQLStr)
fOpenedRecSet = True

If (Not (rs.BOF And rs.EOF)) Then
Forms!frmReview!txtTotalTime.Value = rs.Fields(0).Value
Else
Forms!frmReview!txtTotalTime.Value = 0
End If

CleanUp:

If (fOpenedRecSet) Then
rs.Close
fOpenedRecSet = False
End If

Set rs = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in TimeTotal( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Deki said:
Hello!
Got stuck trying to pass vallue from query to a textbox.
Query is:
SELECT Sum(DateDiff("n",[StartTime],[EndTime])/60) AS TotalTime FROM tblTime
WHERE (((tblTime.ReviewNumber)=[Forms]![frmReview]![txtReviewNumber])) GROUP
BY tblTime.ReviewNumber

It runs when On Activate, On Current or On Load event of frmReview is
invoked and should pass that single value to textbox txtTotalTime on same
form.
I'm getting error message:
"Run-time error '3061': Too few parameters. Expected 1."

This is what I've done so far:

Private Sub TimeTotal()
Dim db As Database
Dim rs As Recordset
Dim strTimeTotal As Variant
Dim SQLStr As String

SQLStr = "SELECT Sum(DateDiff(""n"",[StartTime],[EndTime])/60) AS
TotalTime FROM tblTime WHERE (((tblTime.ReviewNumber)="
SQLStr = SQLStr & [Forms]![frmReview]![txtReviewNumber] & ")) GROUP BY
tblTime.ReviewNumber"

Set db = CurrentDb()
'Set rs = db.OpenRecordset(SQLStr)
If IsNull(db.OpenRecordset(SQLStr)) Then
[Forms]![frmReview]![txtTotalTime] = 0
Else
Set rs = db.OpenRecordset(SQLStr)
[Forms]![frmReview]![txtTotalTime] = rs
End If
[Forms]![frmReview]![txtTotalTime] = strTimeTotal

End Sub

Please help!

Deki PA
Click to expand...
 
G

Guest

It didn't work, but I created a listbox instead of textbox and added select
statement as it's rowsource and it works now! Thank you for your answer, made
me think :)

Deki PA



'69 Camaro said:
Ensure that the table name and field names are spelled correctly (and that
they exist). Ensure that the data types are correct, too. StartTime and
EndTime must be Date/Time fields, ReviewNumber must be numerical and
Forms!frmReview!txtReviewNumber must be numerical. If the ReviewNumber is a
text string, you'll need to change the WHERE clause to use string criteria,
not numerical criteria.

Try the following simpler code (assuming ReviewNumber and the text box
values are numerical):

Private Sub TimeTotal()

On Error GoTo ErrHandler

Dim rs As Recordset
Dim SQLStr As String
Dim fOpenedRecSet As Boolean

SQLStr = "SELECT Sum(DateDiff(""n"", StartTime, EndTime)/60) AS
TotalTime FROM tblTime WHERE (ReviewNumber="
SQLStr = SQLStr & Forms!frmReview!txtReviewNumber & ") GROUP BY
ReviewNumber"

Set rs = CurrentDb().OpenRecordset(SQLStr)
fOpenedRecSet = True

If (Not (rs.BOF And rs.EOF)) Then
Forms!frmReview!txtTotalTime.Value = rs.Fields(0).Value
Else
Forms!frmReview!txtTotalTime.Value = 0
End If

CleanUp:

If (fOpenedRecSet) Then
rs.Close
fOpenedRecSet = False
End If

Set rs = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in TimeTotal( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Deki said:
Hello!
Got stuck trying to pass vallue from query to a textbox.
Query is:
SELECT Sum(DateDiff("n",[StartTime],[EndTime])/60) AS TotalTime FROM tblTime
WHERE (((tblTime.ReviewNumber)=[Forms]![frmReview]![txtReviewNumber])) GROUP
BY tblTime.ReviewNumber

It runs when On Activate, On Current or On Load event of frmReview is
invoked and should pass that single value to textbox txtTotalTime on same
form.
I'm getting error message:
"Run-time error '3061': Too few parameters. Expected 1."

This is what I've done so far:

Private Sub TimeTotal()
Dim db As Database
Dim rs As Recordset
Dim strTimeTotal As Variant
Dim SQLStr As String

SQLStr = "SELECT Sum(DateDiff(""n"",[StartTime],[EndTime])/60) AS
TotalTime FROM tblTime WHERE (((tblTime.ReviewNumber)="
SQLStr = SQLStr & [Forms]![frmReview]![txtReviewNumber] & ")) GROUP BY
tblTime.ReviewNumber"

Set db = CurrentDb()
'Set rs = db.OpenRecordset(SQLStr)
If IsNull(db.OpenRecordset(SQLStr)) Then
[Forms]![frmReview]![txtTotalTime] = 0
Else
Set rs = db.OpenRecordset(SQLStr)
[Forms]![frmReview]![txtTotalTime] = rs
End If
[Forms]![frmReview]![txtTotalTime] = strTimeTotal

End Sub

Please help!

Deki PA
Click to expand...
Click to expand...
 
6

'69 Camaro

You're welcome. Glad it helped. But if you have a text data type instead
of the numerical data type for the ReviewNumber then, as I mentioned, the
WHERE clause should have been changed to the following, nearly identical,
syntax:

SQLStr = "SELECT Sum(DateDiff(""n"", StartTime, EndTime)/60) AS
TotalTime FROM tblTime WHERE (ReviewNumber='"
SQLStr = SQLStr & Forms!frmReview!txtReviewNumber & "') GROUP BY
ReviewNumber"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Deki said:
It didn't work, but I created a listbox instead of textbox and added
select
statement as it's rowsource and it works now! Thank you for your answer,
made
me think :)

Deki PA



'69 Camaro said:
Ensure that the table name and field names are spelled correctly (and
that
they exist). Ensure that the data types are correct, too. StartTime and
EndTime must be Date/Time fields, ReviewNumber must be numerical and
Forms!frmReview!txtReviewNumber must be numerical. If the ReviewNumber
is a
text string, you'll need to change the WHERE clause to use string
criteria,
not numerical criteria.

Try the following simpler code (assuming ReviewNumber and the text box
values are numerical):

Private Sub TimeTotal()

On Error GoTo ErrHandler

Dim rs As Recordset
Dim SQLStr As String
Dim fOpenedRecSet As Boolean

SQLStr = "SELECT Sum(DateDiff(""n"", StartTime, EndTime)/60) AS
TotalTime FROM tblTime WHERE (ReviewNumber="
SQLStr = SQLStr & Forms!frmReview!txtReviewNumber & ") GROUP BY
ReviewNumber"

Set rs = CurrentDb().OpenRecordset(SQLStr)
fOpenedRecSet = True

If (Not (rs.BOF And rs.EOF)) Then
Forms!frmReview!txtTotalTime.Value = rs.Fields(0).Value
Else
Forms!frmReview!txtTotalTime.Value = 0
End If

CleanUp:

If (fOpenedRecSet) Then
rs.Close
fOpenedRecSet = False
End If

Set rs = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in TimeTotal( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Deki said:
Hello!
Got stuck trying to pass vallue from query to a textbox.
Query is:
SELECT Sum(DateDiff("n",[StartTime],[EndTime])/60) AS TotalTime FROM
tblTime
WHERE (((tblTime.ReviewNumber)=[Forms]![frmReview]![txtReviewNumber]))
GROUP
BY tblTime.ReviewNumber

It runs when On Activate, On Current or On Load event of frmReview is
invoked and should pass that single value to textbox txtTotalTime on
same
form.
I'm getting error message:
"Run-time error '3061': Too few parameters. Expected 1."

This is what I've done so far:

Private Sub TimeTotal()
Dim db As Database
Dim rs As Recordset
Dim strTimeTotal As Variant
Dim SQLStr As String

SQLStr = "SELECT Sum(DateDiff(""n"",[StartTime],[EndTime])/60) AS
TotalTime FROM tblTime WHERE (((tblTime.ReviewNumber)="
SQLStr = SQLStr & [Forms]![frmReview]![txtReviewNumber] & ")) GROUP
BY
tblTime.ReviewNumber"

Set db = CurrentDb()
'Set rs = db.OpenRecordset(SQLStr)
If IsNull(db.OpenRecordset(SQLStr)) Then
[Forms]![frmReview]![txtTotalTime] = 0
Else
Set rs = db.OpenRecordset(SQLStr)
[Forms]![frmReview]![txtTotalTime] = rs
End If
[Forms]![frmReview]![txtTotalTime] = strTimeTotal

End Sub

Please help!

Deki PA
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Ferguson

SQLStr = "SELECT Sum(DateDiff(""n"", StartTime, EndTime)/60) " & _
"AS TotalTime FROM tblTime
Click to expand...


Shouldn't that be

SQLStr = "SELECT Sum(DateDiff(""n"", StartTime, EndTime))/60 " & _
"AS TotalTime FROM tblTime


which means one floating point division rather than one for every row in
the table?


Just a thought...


Tim F
 

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

Error 3061: Too Fee Parameters 2
Sum and DateDiff 2
Passing a query parameter 2
Simple: What is the syntax to reference a field value in Vb? 3
Export using strSQL 2
Iterate/loop through a form to copy value from one control to anot 2
Passing field name as a parameter 1
MS Access setting outlook reminders from a DAO recordset 0

Top