Dsum sing single date criterior

[Guest]

Hi all

Sorry about this but I realy cant get my head around the use of DSUM to acheive my goal.

I have a single table whre the user records the duration (in quarter days eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.

the feild on the table are

DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo

Table name is tblDate

the form has a bound control used for this and includes all the above

the form name is frmDate

In the before update event for the control - but I get the same result if I use the same for the form update event

My code is as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double

dblSubTotal = DSum("Duration", "tblDate", "[Date]= " & Forms!frmdate!Date)

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub

The result is 'Invalid use of Null'

Can any one please help - Im pretty new to access and this has me stumped - all i want to do is stop people entering to many tasks with to much time to a single date. should be simple but Im afraid I cant figure it out.

Many thanks in advance

GreenBoy

 

[Ron Weiner]

Try

dblSubTotal = DSum("Duration", "tblDate", "[Date]= #" & Forms!frmdate!Date&
"#")

Ron W

Hi all

Sorry about this but I realy cant get my head around the use of DSUM to acheive my goal.

I have a single table whre the user records the duration (in quarter days

eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.

the feild on the table are

DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo

Table name is tblDate

the form has a bound control used for this and includes all the above

the form name is frmDate

In the before update event for the control - but I get the same result if

I use the same for the form update event

My code is as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double

dblSubTotal = DSum("Duration", "tblDate", "[Date]= " & Forms!frmdate!Date)

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub

The result is 'Invalid use of Null'

Can any one please help - Im pretty new to access and this has me

stumped - all i want to do is stop people entering to many tasks with to
much time to a single date. should be simple but Im afraid I cant figure it
out.

 

[Ron Weiner]

Opp's Shoulda read this more closely. You always want to wrap Dsum() in a
NZ() like:

dblSubTotal = NZ(DSum("Duration", "tblDate", "[Date]= #" &
Forms!frmdate!Date& "#"),0)

This will prevent DSum() from assinging a null to the Double variable
dblSubTotal when there are no records in the table for the date in
Forms!frmdate!Date. in the above example it will assign a ZERO which is
exactly what you want in this case.

Ron W

Try

dblSubTotal = DSum("Duration", "tblDate", "[Date]= #" & Forms!frmdate!Date&
"#")

Ron W

Hi all

Sorry about this but I realy cant get my head around the use of DSUM to acheive my goal.

I have a single table whre the user records the duration (in quarter

days
eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.

the feild on the table are

DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo

Table name is tblDate

the form has a bound control used for this and includes all the above

the form name is frmDate

In the before update event for the control - but I get the same result

if
I use the same for the form update event

My code is as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double

dblSubTotal = DSum("Duration", "tblDate", "[Date]= " & Forms!frmdate!Date)

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub

The result is 'Invalid use of Null'

Can any one please help - Im pretty new to access and this has me

stumped - all i want to do is stop people entering to many tasks with to
much time to a single date. should be simple but Im afraid I cant figure it

out.

Many thanks in advance

GreenBoy

 

[John Spencer (MVP)]

Also, note that fieldname in criteria statement should be MyDate not Date.

Opp's Shoulda read this more closely. You always want to wrap Dsum() in a
NZ() like:

dblSubTotal = NZ(DSum("Duration", "tblDate", "[Date]= #" &
Forms!frmdate!Date& "#"),0)

This will prevent DSum() from assinging a null to the Double variable
dblSubTotal when there are no records in the table for the date in
Forms!frmdate!Date. in the above example it will assign a ZERO which is
exactly what you want in this case.

Ron W

Try

dblSubTotal = DSum("Duration", "tblDate", "[Date]= #" & Forms!frmdate!Date&
"#")

Ron W

Hi all

Sorry about this but I realy cant get my head around the use of DSUM to acheive my goal.

I have a single table whre the user records the duration (in quarter

days
eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.

the feild on the table are

DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo

Table name is tblDate

the form has a bound control used for this and includes all the above

the form name is frmDate

In the before update event for the control - but I get the same result

if
I use the same for the form update event

My code is as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double

dblSubTotal = DSum("Duration", "tblDate", "[Date]= " & Forms!frmdate!Date)

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub

The result is 'Invalid use of Null'

Can any one please help - Im pretty new to access and this has me

stumped - all i want to do is stop people entering to many tasks with to
much time to a single date. should be simple but Im afraid I cant figure it

out.

Many thanks in advance

GreenBoy

 

[GreenBoy]

Guys

Many thanks - couple of pointers

This doesnt stop me adding any new data for the table for a date.

eg records 1 to 3 record task of 0.25 of a day (say 1/6/03) for 3 seperate
tasks.

records 4 tp 20 are for following days.

then if I try to add a record for 1/6/03 for say 0.5 days then it should
cancel and give me the message - this is still not happening.

I like the NZ function that is very handy

Also can you explain the + #" code ? Im sorry but I ddont understand this.

Many thanks for the help already

GreenBoy

Also, note that fieldname in criteria statement should be MyDate not Date.

Opp's Shoulda read this more closely. You always want to wrap Dsum() in a
NZ() like:

dblSubTotal = NZ(DSum("Duration", "tblDate", "[Date]= #" &
Forms!frmdate!Date& "#"),0)

This will prevent DSum() from assinging a null to the Double variable
dblSubTotal when there are no records in the table for the date in
Forms!frmdate!Date. in the above example it will assign a ZERO which is
exactly what you want in this case.

Ron W

Try

dblSubTotal = DSum("Duration", "tblDate", "[Date]= #" & Forms!frmdate!Date&
"#")

Ron W

Hi all

Sorry about this but I realy cant get my head around the use of DSUM to
acheive my goal.

I have a single table whre the user records the duration (in quarter days
eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.

the feild on the table are

DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo

Table name is tblDate

the form has a bound control used for this and includes all the above

the form name is frmDate

In the before update event for the control - but I get the same

result
if

I use the same for the form update event

My code is as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double

dblSubTotal = DSum("Duration", "tblDate", "[Date]= " & Forms!frmdate!Date)

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub

The result is 'Invalid use of Null'

Can any one please help - Im pretty new to access and this has me
stumped - all i want to do is stop people entering to many tasks with to
much time to a single date. should be simple but Im afraid I cant

figure
it

out.

Many thanks in advance

GreenBoy

 

[Ron Weiner]

Your Problem is probably in the line

dblTotal = Me!Duration + dblSubTotal

Since this is all firing on the Before Update event the FIELD Me!Duration
has not yet been assigned a value. Get the value from the control
(presumably a textbox) Me.txtDuration.Value which will have the value
entered by the user.

To test this add the following line before the line problem line above.

Debug.Print "Field Duration = ";Me!Duration, "Control Duration = ";
Me.txtDuration.Value , "Subtotal = ";dblSubTotal

Which in your example I believe will print the following in the immediate
window.

Field Duration = 0 Control Duration = .5 Subtotal = .75

So your formula becomes: dblTotal = 0+.75 which is less than 1 and your
cancel code doesn't fire.

As for the #'s, Access Sql wants dates to be surrounded with them.

Ron W

Guys

Many thanks - couple of pointers

This doesnt stop me adding any new data for the table for a date.

eg records 1 to 3 record task of 0.25 of a day (say 1/6/03) for 3 seperate
tasks.

records 4 tp 20 are for following days.

then if I try to add a record for 1/6/03 for say 0.5 days then it should
cancel and give me the message - this is still not happening.

I like the NZ function that is very handy

Also can you explain the + #" code ? Im sorry but I ddont understand this.

Many thanks for the help already

GreenBoy

Also, note that fieldname in criteria statement should be MyDate not Date.

in

a

NZ() like:

dblSubTotal = NZ(DSum("Duration", "tblDate", "[Date]= #" &
Forms!frmdate!Date& "#"),0)

This will prevent DSum() from assinging a null to the Double variable
dblSubTotal when there are no records in the table for the date in
Forms!frmdate!Date. in the above example it will assign a ZERO which is
exactly what you want in this case.

Ron W

Try

dblSubTotal = DSum("Duration", "tblDate", "[Date]= #" &
Forms!frmdate!Date&
"#")

Ron W

Hi all

Sorry about this but I realy cant get my head around the use of

DSUM

to

acheive my goal.

I have a single table whre the user records the duration (in quarter
days
eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.

the feild on the table are

DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo

Table name is tblDate

the form has a bound control used for this and includes all the above

the form name is frmDate

In the before update event for the control - but I get the same result
if
I use the same for the form update event

My code is as follows

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double

dblSubTotal = DSum("Duration", "tblDate", "[Date]= " &
Forms!frmdate!Date)

dblTotal = Me!Duration + dblSubTotal

If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub

The result is 'Invalid use of Null'

Can any one please help - Im pretty new to access and this has me
stumped - all i want to do is stop people entering to many tasks

with

to

 

[Guest]

Ron

Thnks for the reply - but unfortunately the result I get is quite the oposite

Field Duration = 0.75 Control Duration = 0.75 Subtotal = 0

I thought it would as I had put a break point in the code at the line dblTotal = Me!Duration + dblSubTotal so I could check on the items in the watch window, and then stepped the rest of the code - it rly does seem that the Dsum formula is not actualy calculating the sum of the duration feild for all items that occur on a specified date.

I'm going to continue with this, but any further thoughts welcome.

GreenBoy

PS - Happy New Year.

----- Ron Weiner wrote: -----

Your Problem is probably in the line

dblTotal = Me!Duration + dblSubTotal

Since this is all firing on the Before Update event the FIELD Me!Duration
has not yet been assigned a value. Get the value from the control
(presumably a textbox) Me.txtDuration.Value which will have the value
entered by the user.

To test this add the following line before the line problem line above.

Debug.Print "Field Duration = ";Me!Duration, "Control Duration = ";
Me.txtDuration.Value , "Subtotal = ";dblSubTotal

Which in your example I believe will print the following in the immediate
window.

Field Duration = 0 Control Duration = .5 Subtotal = .75

So your formula becomes: dblTotal = 0+.75 which is less than 1 and your
cancel code doesn't fire.

As for the #'s, Access Sql wants dates to be surrounded with them.

Ron W

Guys

Many thanks - couple of pointers
This doesnt stop me adding any new data for the table for a date.
eg records 1 to 3 record task of 0.25 of a day (say 1/6/03) for 3 seperate tasks.
records 4 tp 20 are for following days.
then if I try to add a record for 1/6/03 for say 0.5 days then it should

cancel and give me the message - this is still not happening.

I like the NZ function that is very handy
Also can you explain the + #" code ? Im sorry but I ddont understand this.
Many thanks for the help already
GreenBoy

Also, note that fieldname in criteria statement should be MyDate not Date.

in

a

NZ() like:
dblSubTotal = NZ(DSum("Duration", "tblDate", "[Date]= #" &>>> Forms!frmdate!Date& "#"),0)
This will prevent DSum() from assinging a null to the Double variable
dblSubTotal when there are no records in the table for the date in
Forms!frmdate!Date. in the above example it will assign a ZERO which is
exactly what you want in this case.
Ron W
Try
dblSubTotal = DSum("Duration", "tblDate", "[Date]= #" &>>> Forms!frmdate!Date&>>>> "#")
Ron W
Hi all
Sorry about this but I realy cant get my head around the use of

DSUM

to

acheive my goal.
I have a single table whre the user records the duration (in quarter
days
eg 0.25, 0.50, 0.5 or 1.00) spent on a task against a date.
the feild on the table are
DateID Auto number
MyDate Date
Duration Number (Double)
Task Text
Note Memo
Table name is tblDate
the form has a bound control used for this and includes all the above
the form name is frmDate
In the before update event for the control - but I get the same result
if
I use the same for the form update event
My code is as follows
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblSubTotal As Double, dblTotal As Double
dblSubTotal = DSum("Duration", "tblDate", "[Date]= " &>>> Forms!frmdate!Date)
dblTotal = Me!Duration + dblSubTotal
If dblTotal > 1 Then
Cancel = True
MsgBox "Entry cancelled"
End If
End Sub
The result is 'Invalid use of Null'
Can any one please help - Im pretty new to access and this has me
stumped - all i want to do is stop people entering to many tasks

with

to