Need help with sql and form

[Paul]

I am trying to get an average from SumOfPalletCount and rs.recordCount but I
get errors no matter how I try and do this...Is there a better way to do
this?

*********What I currently have in a qrytest:***********

PARAMETERS [DaySelected] DateTime;

SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS SumOfPalletCount

FROM RecyHistory

GROUP BY RecyHistory.DateRec

HAVING
(((DatePart("w",[RecyHistory].[DateRec]))=DatePart("w",[DaySelected])));



******And Form coding:*********

Private Sub DateRec_GotFocus()

Dim qdf As DAO.QueryDef

Dim rs As DAO.Recordset

If Not IsDate(Me.DateRec) Then

MsgBox "This value must be a date!"

Me.DateRec.SetFocus

Exit Sub

End If

Set qdf = CurrentDb.QueryDefs("Qrytest")

qdf.Parameters(0) = CDate(Me.DateRec)

Set rs = qdf.OpenRecordset

If rs.EOF Then

Me.Text4 = 0

Else

Me.Text4 = SumOfPalletCount / rs.RecordCount
<------Here is were I get messed up

End If

rs.Close

Set rs = Nothing

qdf.Close

Set qdf = Nothing

 

[MichaelRay via AccessMonster.com]

What error message are you seeing?

I am trying to get an average from SumOfPalletCount and rs.recordCount but I
get errors no matter how I try and do this...Is there a better way to do
this?

*********What I currently have in a qrytest:***********

PARAMETERS [DaySelected] DateTime;

SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS SumOfPalletCount

FROM RecyHistory

GROUP BY RecyHistory.DateRec

HAVING
(((DatePart("w",[RecyHistory].[DateRec]))=DatePart("w",[DaySelected])));

******And Form coding:*********

Private Sub DateRec_GotFocus()

Dim qdf As DAO.QueryDef

Dim rs As DAO.Recordset

If Not IsDate(Me.DateRec) Then

MsgBox "This value must be a date!"

Me.DateRec.SetFocus

Exit Sub

End If

Set qdf = CurrentDb.QueryDefs("Qrytest")

qdf.Parameters(0) = CDate(Me.DateRec)

Set rs = qdf.OpenRecordset

If rs.EOF Then

Me.Text4 = 0

Else

Me.Text4 = SumOfPalletCount / rs.RecordCount
<------Here is were I get messed up

End If

rs.Close

Set rs = Nothing

qdf.Close

Set qdf = Nothing

 

[Paul]

The Error is this: "Compile Error - Sub or Funtion not Defined"

I originally did not give the correct code that is causing the error. I
want to get the total from Sum(SumOfPalletTotal) and divide it from
rs.recordCount to get the average.
Thanks for your help!

What error message are you seeing?

I am trying to get an average from SumOfPalletCount and rs.recordCount but
I
get errors no matter how I try and do this...Is there a better way to do
this?

*********What I currently have in a qrytest:***********

PARAMETERS [DaySelected] DateTime;

SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS
SumOfPalletCount

FROM RecyHistory

GROUP BY RecyHistory.DateRec

HAVING
(((DatePart("w",[RecyHistory].[DateRec]))=DatePart("w",[DaySelected])));

******And Form coding:*********

Private Sub DateRec_GotFocus()

Dim qdf As DAO.QueryDef

Dim rs As DAO.Recordset

If Not IsDate(Me.DateRec) Then

MsgBox "This value must be a date!"

Me.DateRec.SetFocus

Exit Sub

End If

Set qdf = CurrentDb.QueryDefs("Qrytest")

qdf.Parameters(0) = CDate(Me.DateRec)

Set rs = qdf.OpenRecordset

If rs.EOF Then

Me.Text4 = 0

Else

Me.Text4 = SumOfPalletCount / rs.RecordCount
<------Here is were I get messed up

End If

rs.Close

Set rs = Nothing

qdf.Close

Set qdf = Nothing

 

[MichaelRay via AccessMonster.com]

SumOfPalletCount isn't referenced correctly. Since it is the second value
returned in the recordset, it should be referenced as rs(1), since the
recordset is zero-based. Me.Text4 = rs(1)/ rs.RecordCount should solve the
problem.

The Error is this: "Compile Error - Sub or Funtion not Defined"

I originally did not give the correct code that is causing the error. I
want to get the total from Sum(SumOfPalletTotal) and divide it from
rs.recordCount to get the average.
Thanks for your help!

What error message are you seeing?

[quoted text clipped - 59 lines]

 

[Paul]

Thanks for getting back to me

This is what is happening now...
DateRec SumOfPalletCount

Monday, October 29, 2007 6

Monday, November 05, 2007 4

Monday, November 26, 2007 8

Monday, December 10, 2007 9

Monday, December 17, 2007 8

Monday, January 07, 2008 7

Monday, January 14, 2008 9

Monday, January 28, 2008 6

Daterec = 8 SumOfPalletCount = 57

Me.Text4 = rs(1) / rs.RecordCount This is what I am getting for values



Me.text4 = 0.75 rs(1)=6 rs.recordCount=8



rs(1) should be the total of SumOfPalletCount

SumOfPalletCount isn't referenced correctly. Since it is the second value
returned in the recordset, it should be referenced as rs(1), since the
recordset is zero-based. Me.Text4 = rs(1)/ rs.RecordCount should solve
the
problem.

The Error is this: "Compile Error - Sub or Funtion not Defined"

I originally did not give the correct code that is causing the error. I
want to get the total from Sum(SumOfPalletTotal) and divide it from
rs.recordCount to get the average.
Thanks for your help!

What error message are you seeing?

[quoted text clipped - 59 lines]

Set qdf = Nothing