Replies

Thanks to both. That was the issue exactly.
Scott

Mark,
I was thinking that similar to the trendline in Excel if the last point of the hypothetical trendline in my table is higher than the starting point the trend would be “Up” and vice versa.
But I can’t simply take the first and last month’s revenue and do a calculation because the months between them would not be figured into the trend.
Excel computes the values for the trendline taking all the months into consideration.

Scott

Francois,
Scary how almost live this forum is. Or I need to be able to type faster.
Thanks to all.

Sorry,
Should have looked before I posted. I just had to add 2 lines (in bold)

Thanks for the solution

Function CreateDealerList()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim strUsername As String
Dim strSQL As String
Dim strDataID As String
Dim strDealer As String

Set db = CurrentDb
strSQL = “SELECT DATA_ID, User, Dealer ” & _
“FROM tblDealers ORDER BY User, Dealer”

DoCmd.SetWarnings False
DoCmd.RunSQL “DELETE * FROM tblDealerList;”
DoCmd.SetWarnings True
Set rs = db.OpenRecordset(strSQL)
Set rst = db.OpenRecordset(“tblDealerList”)

Do While Not rs.EOF
strUsername = rs!User
[indent]strDealer = “”
strDataID = “”[/indent]
Do Until strUsername rs!User
strDealer = strDealer & “|” & rs!Dealer
strDataID = strDataID & “|” & rs!Data_ID
rs.MoveNext
If rs.EOF Then Exit Do
Loop
rst.AddNew
rst!User = strUsername
rst!Data_ID = Mid(strDataID, 2)
rst!Dealer = Mid(strDealer, 2)
rst.Update
If rs.EOF Then Exit Do
rs.MoveNext
Loop

rst.Close
rs.Close
Set rst = Nothing
Set rs = Nothing
Set db = Nothing
End Function

Hans,
Its almost there but something isn’t looping correctly.
Here is the output.

Here is a sample database with your function if you can take a look.

Thanks,
Scott

david,
I tried that too. Still same message.

Scott

Not when it is on the very last record.
Scott

David,
Thanks but it never gets to that point when its on the last record. Its still in the inside loop.
Scott

I don’t understand your description. “the column names” of what?

Hans,
Sorry, I’m speaking ot the field names in the table.

Scott

You have to treat null (blank) values separately. Try

SELECT BranchID, Selected, PreviousSelected
FROM tblBranch
WHERE (SelectedPreviousSelected) Or (Selected Is Not Null And PreviousSelected Is Null) Or (Selected Is Null And PreviousSelected Is Not Null)

Thanks Hans that did the trick
Scott

Hans,
I could add a date field and modify the date anytime the record is updated.
I’m not opposed to a total a new approach since this is a new project.

Scott

Hans,
I can’t thank you enough.
That worked perfectly.

Scott

Hans,
That makes some sense. I’ll see what it looks like.
Thanks,
Scott

Hans,
I included the Function to show you how the cells in the row get their value.
I modified the Function so if there isn’t any sum involved, it won’t place a zero in the cell.

Function SumAll(sCell As String)
    Dim wks As Worksheet
    Dim dSum As Double
    Dim iCount As Integer

    Application.Volatile
    iCount = 0
    dSum = 0
    For Each wks In Worksheets
        dSum = dSum + Application.Sum(wks.Range(sCell))
    Next
        If dSum = 0 Then
            SumAll = ""
        Else
            SumAll = dSum
        End If
    Set wks = Nothing
End Function

Then using your original formula, I can achieve the results I want.

Hans,
I found that example on the web and the problem is a zero value.
The row has 12 columns(months) plus a description in the first column.
As they fill in the template sheet every month, the next column of the row(data i’m looking for) will data in it.
I’m using this function to calculate the values in the cells

Function SumAll(sCell As String)
    Dim wks As Worksheet
    Dim dSum As Double
    Dim iCount As Intege

    Application.Volatile
    iCount = 0
    dSum = 0
    For Each wks In Worksheets
        dSum = dSum + Application.Sum(wks.Range(sCell))
    Next
        SumAll = dSum
    Set wks = Nothing
End Function

Should this be modified to make your formula work?