Having trouble with Dates?

[gvirden]

Not those dates.

I want to specify a certain number of days as my count. Lets say 7 days.

Sum, starting with the most recent date since NOW, which matches my criterion (allocation = 100), the weight and divide that by the count. Of course the columns used in the recordset are date(date), allocation(integer), and weight(integer).

The result would be something like--
The 7 day average for A1 at 100 = 2,425

Any help would be greatly appreciated.
Thanks

[Clunietp]

I find that if you put on deodorant before a date, the woman will not complain as much...



Oh yeah, VB....

Use the DATEDIFF function to determine the amount of days passed between the value in your recordset and today. Then you can apply that value to your formula.

Cologne is usually a nice feature, but not a replacement.

Tom

[gvirden]

Tom,

Any chance you could elaborate a little on that comment or maybe steer me toward an example?... on the VB part, that is !

[Clunietp]

gvirden

This code uses the Northwind Access 2000 Database to calculate something, in this case it is:

[The number of days passed since the order] TIMES [OrderID] giving a result. Of course, the value doesn't mean anything, but you can substitute OrderID with whatever field you like.

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Dim lngDaysPassed As Long
    Dim dblAmount As Double
    
    Set cn = New Connection
    
       cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWind2k.mdb"
    
    Set rs = cn.Execute("Select * from Orders", , adCmdText)
    
    Do Until rs.EOF = True
        lngDaysPassed = DateDiff("d", rs.Fields("OrderDate"), Now)
        dblAmount = lngDaysPassed * CLng(rs.Fields("OrderID").Value)
        Debug.Print dblAmount
        rs.MoveNext
    Loop

HTH

Tom

[gvirden]

Tom,

I really appreciate all your time and help. It seems there is no question with VB you can't solve. My problem is I get so frustrated and confused I can't seem to explain my problems clearly. Anyway, after studying your solution to a problem I didn't know that I had (which I'm sure I do have) it enlightened me considerably in solving the problem that I thought I had. Are you still with me? Anyway, as you stated earlier (well..sort of, ironically), I stink when it comes to VB (hopefully not so with females). Real smell is what I had before finding this board!!

OK, back to work. I want the feed records for pond A1. Give me A1 feed records by decending date that have an allocation value = 100 (that means they were fed to satiation that day if your interested). I could select the last day the pond ate at 100% for a good indicator of what they may eat today. But I want an average of several days (x in the code) for a better indicator and for other reasons.

Dim db As Database
Dim rs As Recordset
Dim x As Integer
Dim intrecs As Integer
Dim intweight As Integer
Set db = Workspaces(0).OpenDatabase("c:\My documents\sfdatafile\sfdatafirst.mdb")
SQLString = "SELECT FeedFed.Pond, FeedFed.Weight, FeedFed.Date, Feedfed.Allocation " & _
"From FeedFed " & _
"Where (((FeedFed.Pond)='" & A1& "')" & _
"AND ((FeedFed.Allocation)='" & 100 & "'))" & _
"ORDER BY FeedFed.Date DESC"
Set rs = db.OpenRecordset(SQLString)
With rs
.MoveLast
intrecs = .RecordCount
lblFeedDays = intrecs
x = 7
If x > intrecs Then
MsgBox ("Not enough records to average. Choose a number less than " & x & ".")
End If
End With
For z = 0 To x
intweight = rs.Fields("weight") + intweight
Next z
lblave.Caption = intweight / x
End Sub

Damn thing didn't work right. Every record it counted had a value of 100 (those values are not in the weight column) for some reason. Please help.

[Clunietp]

Why are you not repositioning your record pointer to the first record (using .MoveFirst), and why don't you loop through all the records that meet your criteria?

It does not appear that you are calculating the average over a period of days (where period of days = different record), your code is staying on the same record and keep adding that same value, so you won't ever get the average.

If you want me to take a look at your code, email it to me, with your database file, and explain to me this pond thingy a bit more, and I'm sure we could get something working once I understand your problem better.

Tom
[email protected]

[Gerald]

Gvirden,

Tom has probably already helped you out, but I thought I'd throw in my thoughts on the problem because the project you are working on sounds very interesting to me. I may not have the complete picture, but it would seem that what you want could be accomplished by a single select statement.

sSQL = _
"SELECT Avg(Weight) AS TheAverage FROM FeedFed " & _
"WHERE (Pond = 'A1' AND Allocation = 100) " _
"AND (Date BETWEEN #" & BegPeriod & "# AND #" & EndPeriod & "#)"

The user could enter the value for the "BegPeriod" variable and then the "EndPeriod" variable could be computed by adding the number of days you would like-such as 7 to get a weeklong period.

Happy Holidays,
Gerald


[This message has been edited by Gerald (edited 12-23-1999).]