Need help with sql and form

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

What error message are you seeing?

Paul wrote:
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

--
Message posted via http://www.accessmonster.com

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!





"MichaelRay via AccessMonster.com" u41420@uwe wrote in message
news:8060a6f698f2b@uwe...
What error message are you seeing?

Paul wrote:
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

--
Message posted via http://www.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.


Paul wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200802/1

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

"MichaelRay via AccessMonster.com" u41420@uwe wrote in message
news:8076aeffc7b1c@uwe...
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.


Paul wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200802/1