PDA

View Full Version : DCount problem with null on calander form


samohtwerdna
04-13-2006, 12:50 PM
Hello all,

I'm :banghead: over this.

I have a calander form that populates with the job's that are scheduled for a day - I wrote a function to calulate the total of money value on any day by borrowing from a query. The problem I am having is that when there are no jobs scheduled for a particular day my label is populated with the previous day's total and for the life of me I can't figure out how to test for a null value and send a total of "" or 0 to my label??

here is my function:

Private Sub Form_Current()
'**Purpose: Populate the various text fields with dates relative to selection

Dim datFirst As Date
Dim datTemp As Date
Dim strDay As String
Dim strTemp As String
Dim intCount As Integer
Dim intCounter As Integer
Dim intDayCount As Integer
Dim ctl As Control
Dim intTemp As Integer
Dim strTemp2 As String
Dim datTemp2 As Date
Dim i As Long
Dim tmpText As String
Dim tmpText2 As String
Dim tmpTextTotal As String
Dim eventID(10000) As String
Dim eventDate(10000) As Date
Dim eventMoney(10000) As String
Dim eventJob(10000) As String
Dim eventTotal As String
Dim ESDvariable As Date
Dim events As Long
Dim myset As Object
Dim rstMonth As Object
Dim sql As String
Dim strMoneyTotal As String
Dim Criteria As String
Dim condition As Integer

'load events table data into array
events = 0

'Read in qry_Calendar to Array
Set myset = CurrentDb.OpenRecordset("SELECT tblMainForm.*, " & _
"tblMainForm.ESD FROM tblMainForm " & _
"WHERE (((tblMainForm.ESD) Is Not Null)) " & _
"ORDER BY tblMainForm.ESD;", DB_OPEN_SNAPSHOT)

Criteria = "[dateProcessed] <> Null"
myset.FindFirst Criteria

Do Until myset.NoMatch
events = events + 1
If IsNull(myset![tblMainForm.ESD]) Then
eventID(events) = myset![entryNum]
eventDate(events) = myset![tblMainForm.ESD]
eventJob(events) = myset![orderNumber]
eventMoney(events) = myset![Money]
Else
For i = 1 To Int(DateDiff("d", myset![tblMainForm.ESD], _
myset![tblMainForm.ESD])) + 1
eventID(events + i - 1) = myset![entryNum]
eventDate(events + i - 1) = DateAdd("d", i - 1, myset![tblMainForm.ESD])
eventJob(events + i - 1) = myset![orderNumber]
eventMoney(events + i - 1) = myset![Money]
Next i
events = events + i - 1
End If
myset.FindNext Criteria
Loop

'Read in qry_Calendar2 to Array
Set myset = CurrentDb.OpenRecordset("SELECT tblMainForm.* " & _
"FROM tblMainForm " & _
"WHERE (((tblMainForm.dateProcessed) Is Null) " & _
"And ((tblMainForm.ESD) Is Not Null)) " & _
"ORDER BY tblMainForm.dateProcessed, tblMainForm.ESD;", _
DB_OPEN_SNAPSHOT)
Criteria = "[ESD] <> Null"
myset.FindFirst Criteria
Do Until myset.NoMatch
events = events + 1
eventID(events) = myset![entryNum]
eventDate(events) = myset![ESD]
eventJob(events) = myset![orderNumber]
eventMoney(events) = myset![Money]
events = events + i - 1
myset.FindNext Criteria
Loop

'Clear all pre-existing fields
intCounter = 1
Do
Me("label" & intCounter).Caption = ""
Me("label" & intCounter).ControlTipText = ""
Me("label" & intCounter).ForeColor = 0
Me("label" & intCounter).Visible = False
Me("text" & intCounter).Caption = ""
Me("text" & intCounter).Visible = False
Me("text" & intCounter).BackColor = 16777215
Me("id" & intCounter).Caption = ""
intCounter = intCounter + 1
Loop Until intCounter = 38

'Set date
datFirst = strMonth & ". 1/" & intYear

'Find weekday of first day of month (integer format if possible)
strDay = Format(datFirst, "ddd")

'Set initial counter depending on day of week the corresponding month starts on
Select Case strDay
Case Is = "Mon"
intCounter = 1
Case Is = "Tue"
intCounter = 2
Case Is = "Wed"
intCounter = 3
Case Is = "Thu"
intCounter = 4
Case Is = "Fri"
intCounter = 5
Case Is = "Sat"
intCounter = 6
Case Is = "Sun"
intCounter = 7
End Select

'Find number of days in selected month by datediff from 1st day of next month
'in selected year
datTemp = DateAdd("m", 1, datFirst)
intDayCount = DateDiff("d", datFirst, datTemp)

'Populate calendar with date's
intCount = intCounter
strTemp = ""
Do
Me("label" & intCount).Caption = intCount - intCounter + 1
Me("label" & intCount).ControlTipText = strMonth & " " & _
intCount - intCounter + 1 & ", " & intYear
Me("label" & intCount).Visible = True
Me("text" & intCount).Visible = True

tmpText = ""
tmpText2 = ""

'Fixed Events
If intCount - intCounter + 1 = 4 And strMonth = "July" Then
tmpText = Chr(13) + Chr(10) + "Independance Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 25 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Xmas Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 26 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Boxing Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 1 And strMonth = "January" Then
tmpText = Chr(13) + Chr(10) + "New Years Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 4 And strMonth = "July" Then
tmpText = Chr(13) + Chr(10) + "Independance Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 25 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Xmas Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 26 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Boxing Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 1 And strMonth = "January" Then
tmpText = Chr(13) + Chr(10) + "New Years Day"
Me("text" & intCount).BackColor = 16777215
End If

For i = 1 To events
If intCount - intCounter + 1 = Int(Format(eventDate(i), "d")) _
And strMonth = Format(eventDate(i), "mmmm") _
And intYear = Int(Format(eventDate(i), "yyyy")) _
Then
'---------------------------------------------------------
'total Money for each day
'---------------------------------------------------------
ESDvariable = eventDate(i)


sql = "SELECT Sum(tblMainForm.Money) AS SumOfMoney, tblMainForm.ESD "
sql = sql + "From tblMainForm "
sql = sql + "GROUP BY tblMainForm.ESD "
sql = sql + "HAVING (((tblMainForm.ESD) = #" & ESDvariable & "#)) "
sql = sql + "ORDER BY tblMainForm.ESD;"



Set rstMonth = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

If Nz(DCount("*", "tblMainform", "[tblMainForm.ESD] = #" & _
eventDate(i) & "#"), 0) = 0 Then
strMoneyTotal = 0
Else
strMoneyTotal = DLookup("[SumOfMoney]", "qrySumTest", _
"[tblMainForm.ESD] = #" & ESDvariable & "#")
End If

'strMoneyTotal = Nz(DLookup("[SumOfMoney]", "qrySumTest", _
"[tblMainForm.ESD] = #" & ESDvariable & "#"), 0)

'--------------------------------------------------------
'end Andy's addition
'--------------------------------------------------------

If strMoneyTotal > 0 Then
strMoneyTotal = Format(strMoneyTotal, "$##,###.00")
Else
strMoneyTotal = ""
End If

eventMoney(i) = Format(eventMoney(i), "$##,###.00")
tmpText = tmpText + Chr(13) + Chr(10) & " #" + eventJob(i) & _
" at " + eventMoney(i)
tmpText2 = eventID(i) + " or [ID] = " + tmpText2
Me("text" & intCount).BackColor = 16777215 '16763080 14474460
tmpTextTotal = strMoneyTotal
'If tmpTextTotal > 3000 Then Me("lblTotal" & intCount).BackColor = 255
End If
Next i

If Len(tmpText2) > 0 Then tmpText2 = Left(tmpText2, Len(tmpText2) - 11)
Me("text" & intCount).Caption = tmpText
Me("id" & intCount).Caption = tmpText2
Me("lblTotal" & intCount).Caption = tmpTextTotal

If Int(Format(Date, "d")) = intCount - intCounter + 1 And _
strMonth = Format(Date, "mmmm") And _
intYear = Format(Date, "yyyy") Then

Me("text" & intCount).BackColor = 8454143 '16765650
End If

intCount = intCount + 1
strTemp = ""
Loop Until intCount = intCounter + intDayCount
End Sub


Any Ideas??

Edited 21-Apr-06 by GeekGirlau. Reason - insert line breaks in code
samohtwerdna
04-14-2006, 08:08 AM
Stepping through the code I saw some interesting things.

First I set a watch on:
strMoneyTotal = DLookup("[SumOfMoney]", "qrySumTest", "[tblMainForm.ESD] = #" & ESDvariable & "#")

and

Nz(DCount("*", "tblMainform", "[tblMainForm.ESD] = #" & eventDate(i) & "#"), 0) = 0

the first watch returned a value of <You canceled the previous operation.> right away - and the second watch goes back and forth between true and false.

stepping through the For i = 1 To events loop - the first two times I never meet the condition - on the third go around I meet the condition when I get to the event date of #4/3/2006#

I also noticed that there was no eventDate of null or 0 but #12:00:00 AM# - which maybe why the Nz wasn't catching anything around the DLookup???

But still the error is always on
strMoneyTotal = DLookup("[SumOfMoney]", "qrySumTest", "[tblMainForm.ESD] = #" & ESDvariable & "#")
saying I canceled the previous operation

Any ideas??
samohtwerdna
04-17-2006, 07:10 AM
OK - I figured out a few of my problems and now have a almost working calander schedule.

The one thing I am missing is a total for each week.

If on my form I set the control source for the week total text box to =Sum([txtTotal1]+[txtTotal2]...) the result is not a summed total but each total concantenated so that it looks something like $2,345.00 $144.00 $345.00 you get the picture.

So I'm trying to figure out how to total the money value for any given week with the VBA rather than the code.

Any Ideas??

here is the current sub

Private Sub Form_Current()
'**Purpose: Populate the various text fields with dates relative to selection

Dim datFirst As Date
Dim datTemp As Date
Dim strDay As String
Dim strTemp As String
Dim intCount As Integer
Dim intCounter As Integer
Dim intDayCount As Integer
Dim ctl As Control
Dim intTemp As Integer
Dim strTemp2 As String
Dim datTemp2 As Date
Dim i As Long
Dim tmpText As String
Dim tmpText2 As String
Dim tmpTextTotal As String
Dim tmpWeekTotal As String
Dim eventID(10000) As String
Dim eventDate(10000) As Date
Dim eventMoney(10000) As String
Dim eventJob(10000) As String
Dim eventTotal As String
Dim ESDvariable As Date
Dim events As Long
Dim myset As Object
Dim rstMonth As Object
Dim sql As String
Dim strMoneyTotal As String
Dim Criteria As String
Dim condition As Integer
Dim t1 As String, t2 As String, t3 As String

'load events table data into array
events = 0

'Read in qry_Calendar to Array
Set myset = CurrentDb.OpenRecordset("SELECT tblMainForm.*, tblMainForm.ESD " & _
"FROM tblMainForm " & _
"WHERE (((tblMainForm.ESD) Is Not Null)) " & _
"ORDER BY tblMainForm.ESD;", DB_OPEN_SNAPSHOT)
Criteria = "[dateProcessed] <> Null"
myset.FindFirst Criteria
Do Until myset.NoMatch
events = events + 1
If IsNull(myset![tblMainForm.ESD]) Then
eventID(events) = myset![entryNum]
eventDate(events) = myset![tblMainForm.ESD]
eventJob(events) = myset![orderNumber]
eventMoney(events) = myset![Money]
Else
For i = 1 To Int(DateDiff("d", myset![tblMainForm.ESD], _
myset![tblMainForm.ESD])) + 1
eventID(events + i - 1) = myset![entryNum]
eventDate(events + i - 1) = DateAdd("d", i - 1, myset![tblMainForm.ESD])
eventJob(events + i - 1) = myset![orderNumber]
eventMoney(events + i - 1) = myset![Money]
Next i
events = events + i - 1
End If
myset.FindNext Criteria
Loop

'Read in qry_Calendar2 to Array
Set myset = CurrentDb.OpenRecordset("SELECT tblMainForm.* FROM tblMainForm " & _
"WHERE (((tblMainForm.dateProcessed) Is Null) And " & _
"((tblMainForm.ESD) Is Not Null)) " & _
"ORDER BY tblMainForm.dateProcessed, tblMainForm.ESD;", _
DB_OPEN_SNAPSHOT)
Criteria = "[ESD] <> Null"
myset.FindFirst Criteria
Do Until myset.NoMatch
events = events + 1
eventID(events) = myset![entryNum]
eventDate(events) = myset![ESD]
eventJob(events) = myset![orderNumber]
eventMoney(events) = myset![Money]
events = events + i - 1
myset.FindNext Criteria
Loop

'Clear all pre-existing fields
intCounter = 1
Do
Me("label" & intCounter).Caption = ""
Me("label" & intCounter).ControlTipText = ""
Me("label" & intCounter).ForeColor = 0
Me("label" & intCounter).Visible = False
Me("lblTotal" & intCounter).Caption = ""
Me("text" & intCounter).Caption = ""
Me("text" & intCounter).Visible = False
Me("text" & intCounter).BackColor = 16777215
Me("id" & intCounter).Caption = ""
intCounter = intCounter + 1
Loop Until intCounter = 38

'Set date
datFirst = strMonth & ". 1/" & intYear

'Find weekday of first day of month (integer format if possible)
strDay = Format(datFirst, "ddd")

'Set initial counter depending on day of week the corresponding month starts on
Select Case strDay
Case Is = "Mon"
intCounter = 1
Case Is = "Tue"
intCounter = 2
Case Is = "Wed"
intCounter = 3
Case Is = "Thu"
intCounter = 4
Case Is = "Fri"
intCounter = 5
Case Is = "Sat"
intCounter = 6
Case Is = "Sun"
intCounter = 7
End Select

'Find number of days in selected month by datediff from 1st day of next month
'in selected year
datTemp = DateAdd("m", 1, datFirst)
intDayCount = DateDiff("d", datFirst, datTemp)

'Populate calendar with date's
intCount = intCounter
strTemp = ""
Do
Me("label" & intCount).Caption = intCount - intCounter + 1
Me("label" & intCount).ControlTipText = strMonth & " " & _
intCount - intCounter + 1 & ", " & intYear
Me("label" & intCount).Visible = True
Me("text" & intCount).Visible = True

tmpText = ""
tmpText2 = ""
tmpTextTotal = ""
tmpWeekTotal = ""

'Fixed Events
If intCount - intCounter + 1 = 4 And strMonth = "July" Then
tmpText = Chr(13) + Chr(10) + "Independance Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 25 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Xmas Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 26 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Boxing Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 1 And strMonth = "January" Then
tmpText = Chr(13) + Chr(10) + "New Years Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 4 And strMonth = "July" Then
tmpText = Chr(13) + Chr(10) + "Independance Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 25 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Xmas Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 26 And strMonth = "December" Then
tmpText = Chr(13) + Chr(10) + "Boxing Day"
Me("text" & intCount).BackColor = 16777215
End If

If intCount - intCounter + 1 = 1 And strMonth = "January" Then
tmpText = Chr(13) + Chr(10) + "New Years Day"
Me("text" & intCount).BackColor = 16777215
End If

For i = 1 To events
If intCount - intCounter + 1 = Int(Format(eventDate(i), "d")) _
And strMonth = Format(eventDate(i), "mmmm") _
And intYear = Int(Format(eventDate(i), "yyyy")) _
Then
'---------------------------------------------------------
'total Money for each day
'---------------------------------------------------------
ESDvariable = eventDate(i)

sql = "SELECT Sum(tblMainForm.Money) AS SumOfMoney, tblMainForm.ESD "
sql = sql + "From tblMainForm "
sql = sql + "GROUP BY tblMainForm.ESD "
sql = sql + "HAVING (((tblMainForm.ESD) = #" & ESDvariable & "#)) "
sql = sql + "ORDER BY tblMainForm.ESD;"

Set rstMonth = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

If Nz(DCount("*", "tblMainform", "[tblMainForm.ESD] = #" & _
eventDate(i) & "#"), 0) = 0 Then
strMoneyTotal = 0
Else
strMoneyTotal = Nz(DLookup("[SumOfMoney]", "qrySumTest", _
"[tblMainForm.ESD] = #" & ESDvariable & "#"), 0)
End If

If strMoneyTotal > 0 Then
strMoneyTotal = Format(strMoneyTotal, "$##,###.00")
Else
strMoneyTotal = ""
End If

'--------------------------------------------------------
'end Andy's addition
'--------------------------------------------------------

eventMoney(i) = Format(eventMoney(i), "$##,###.00")
tmpText = tmpText + Chr(13) + Chr(10) & " #" + eventJob(i) & _
" at " + eventMoney(i)
tmpText2 = eventID(i) + " or [ID] = " + tmpText2
Me("text" & intCount).BackColor = 16777215 '16763080 14474460
tmpTextTotal = strMoneyTotal
End If
Next i

If Len(tmpText2) > 0 Then tmpText2 = Left(tmpText2, Len(tmpText2) - 11)
Me("text" & intCount).Caption = tmpText
Me("id" & intCount).Caption = tmpText2
Me("txtTotal" & intCount).Value = tmpTextTotal

If Int(Format(Date, "d")) = intCount - intCounter + 1 And _
strMonth = Format(Date, "mmmm") And _
intYear = Format(Date, "yyyy") Then
Me("text" & intCount).BackColor = 8454143 '16765650
End If

intCount = intCount + 1
strTemp = ""
Loop Until intCount = intCounter + intDayCount
End Sub

Edited 21-Apr-06 by GeekGirlau. Reason: insert line breaks in code
geekgirlau
04-21-2006, 05:27 AM
Hi samohtwerdna,

I've edited your post to add line breaks in the code - it's very difficult to read if you have to keep scrolling to the right.