invalid value error when running a parameter query code

G

Guest

You open a form that has this in it's open event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String

tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"


End Sub


When you open this form, the fr6MonRev form becomes visible and you enter
the three parameters into it (tech, begindate, enddate) at which point the
following function should fill in the textboxes on the first mentioned form
with the results from this function:

Function SixMonRev(techid As String, begindate As String, enddate As String)

Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset



temp = "SELECT tblWorkload.TechID, Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS SumTotalSlides," & _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between " &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID " & _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"

rst.Open temp, CurrentProject.Connection

Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \ rst("NumberDays")


Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset



temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" & _
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid & "'));"

Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")


Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") + rst1("Sum5YrRevDis")) /
(rst("SumQC") + rst1("Sum5YrRev"))) * 100

End Function


So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error 2113: the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"

Any clue what I'm missing? Thanks.
 
D

Douglas J. Steele

You've declared (and instantiated) rst1 as a recordset, but you've never
opened it.

Presumably you meant to include a line

rst1.Open temp1, CurrentProject.Connection


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Julie said:
You open a form that has this in it's open event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String

tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"


End Sub


When you open this form, the fr6MonRev form becomes visible and you enter
the three parameters into it (tech, begindate, enddate) at which point
the
following function should fill in the textboxes on the first mentioned
form
with the results from this function:

Function SixMonRev(techid As String, begindate As String, enddate As
String)

Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset



temp = "SELECT tblWorkload.TechID, Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS SumTotalSlides,"
& _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between " &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID " & _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"

rst.Open temp, CurrentProject.Connection

Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \
rst("NumberDays")


Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset



temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" & _
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid & "'));"

Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")


Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") + rst1("Sum5YrRevDis"))
/
(rst("SumQC") + rst1("Sum5YrRev"))) * 100

End Function


So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error 2113:
the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"

Any clue what I'm missing? Thanks.
Click to expand...
 
G

Guest

Thanks for noticing that. I added that to the code, but the error is
happening still in the same place way before that on this line that I thought
would take the SumTotalGyn value calculated by the query and put it into the
txtSumGyn textbox on the frqry6MonRev form:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")

It's returning "null" for SumTotalGyn, and all the others too.

Douglas J. Steele said:
You've declared (and instantiated) rst1 as a recordset, but you've never
opened it.

Presumably you meant to include a line

rst1.Open temp1, CurrentProject.Connection


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Julie said:
You open a form that has this in it's open event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String

tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"


End Sub


When you open this form, the fr6MonRev form becomes visible and you enter
the three parameters into it (tech, begindate, enddate) at which point
the
following function should fill in the textboxes on the first mentioned
form
with the results from this function:

Function SixMonRev(techid As String, begindate As String, enddate As
String)

Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset



temp = "SELECT tblWorkload.TechID, Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS SumTotalSlides,"
& _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between " &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID " & _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"

rst.Open temp, CurrentProject.Connection

Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \
rst("NumberDays")


Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset



temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" & _
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid & "'));"

Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")


Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") + rst1("Sum5YrRevDis"))
/
(rst("SumQC") + rst1("Sum5YrRev"))) * 100

End Function


So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error 2113:
the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"

Any clue what I'm missing? Thanks.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

In your code, after you've defined the value for temp, put a line

Debug.Print temp

When the code runs, go to the Immediate Window (Ctrl-G), and check what's
printed there. Does it look valid? Copy it, and paste it into a new query
(don't bother selecting any tables when the new query prompts. Just select
View | SQL View from the menu, and paste your SQL there). Does the query run
properly?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Julie said:
Thanks for noticing that. I added that to the code, but the error is
happening still in the same place way before that on this line that I
thought
would take the SumTotalGyn value calculated by the query and put it into
the
txtSumGyn textbox on the frqry6MonRev form:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")

It's returning "null" for SumTotalGyn, and all the others too.

Douglas J. Steele said:
You've declared (and instantiated) rst1 as a recordset, but you've never
opened it.

Presumably you meant to include a line

rst1.Open temp1, CurrentProject.Connection


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Julie said:
You open a form that has this in it's open event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String

tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"


End Sub


When you open this form, the fr6MonRev form becomes visible and you
enter
the three parameters into it (tech, begindate, enddate) at which point
the
following function should fill in the textboxes on the first mentioned
form
with the results from this function:

Function SixMonRev(techid As String, begindate As String, enddate As
String)

Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset



temp = "SELECT tblWorkload.TechID,
Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS
SumTotalSlides,"
& _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between
" &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID "
& _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"

rst.Open temp, CurrentProject.Connection

Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \
rst("NumberDays")


Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset



temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" &
_
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid &
"'));"

Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")


Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") +
rst1("Sum5YrRevDis"))
/
(rst("SumQC") + rst1("Sum5YrRev"))) * 100

End Function


So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error
2113:
the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"

Any clue what I'm missing? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I debug.printed and apparently look harder at the query than I had yesterday
because I discovered it all came down to adding the # sign around the dates
in the select statement. Thanks for the guidance!
Julie

Douglas J. Steele said:
In your code, after you've defined the value for temp, put a line

Debug.Print temp

When the code runs, go to the Immediate Window (Ctrl-G), and check what's
printed there. Does it look valid? Copy it, and paste it into a new query
(don't bother selecting any tables when the new query prompts. Just select
View | SQL View from the menu, and paste your SQL there). Does the query run
properly?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Julie said:
Thanks for noticing that. I added that to the code, but the error is
happening still in the same place way before that on this line that I
thought
would take the SumTotalGyn value calculated by the query and put it into
the
txtSumGyn textbox on the frqry6MonRev form:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")

It's returning "null" for SumTotalGyn, and all the others too.

Douglas J. Steele said:
You've declared (and instantiated) rst1 as a recordset, but you've never
opened it.

Presumably you meant to include a line

rst1.Open temp1, CurrentProject.Connection


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


You open a form that has this in it's open event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String

tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"


End Sub


When you open this form, the fr6MonRev form becomes visible and you
enter
the three parameters into it (tech, begindate, enddate) at which point
the
following function should fill in the textboxes on the first mentioned
form
with the results from this function:

Function SixMonRev(techid As String, begindate As String, enddate As
String)

Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset



temp = "SELECT tblWorkload.TechID,
Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS
SumTotalSlides,"
& _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between
" &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID "
& _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"

rst.Open temp, CurrentProject.Connection

Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \
rst("NumberDays")


Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset



temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" &
_
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid &
"'));"

Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")


Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") +
rst1("Sum5YrRevDis"))
/
(rst("SumQC") + rst1("Sum5YrRev"))) * 100

End Function


So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error
2113:
the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"

Any clue what I'm missing? Thanks.
Click to expand...
Click to expand...
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

VBA error calling parameter query 2
Invalid SQL statement - when trying to run a query with form parameters 2
Error 3061 when running update query from code 3
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value als 3

Top