Calculation on Main Form

[Guest]

I have a calculation on a Main form where the field trkloads is. I am trying
to subtract out the [contactedwork] (a yes/no field) field from the subform
to get a total trkloads left to fill.

Not getting an error. the result goes up and down as uncheck and recheck the
[contactedwork]field. And refresh. However the answer is incorrect. 7
loads-5 checked = 0 Loads left to fill Answer goes up and down 1 count as
check and uncheck -can it see true maybe if not checked???????




= [trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid =
[dispatchid]

And [contactedwork] = True")

thanks

 

[John Vinson]

I have a calculation on a Main form where the field trkloads is. I am trying
to subtract out the [contactedwork] (a yes/no field) field from the subform
to get a total trkloads left to fill.

Not getting an error. the result goes up and down as uncheck and recheck the
[contactedwork]field. And refresh. However the answer is incorrect. 7
loads-5 checked = 0 Loads left to fill Answer goes up and down 1 count as
check and uncheck -can it see true maybe if not checked???????




= [trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid =
[dispatchid]

And [contactedwork] = True")

thanks

The criterion

"dispatchid = [dispatchid] and...

is probably the problem; it will count the number of records in
dispatchdetailsquery which have the value of dispatchid equal to
itself - that is, every record in the table!

Pull the criterion out of the quoted string so you're using the value
on the form, rather than the name of the field:

= [trkloads]-DCount("[contactedwork]", "dispatchdetailsquery",
"dispatchid = " & [dispatchid] & " And [contactedwork] = True")

John W. Vinson[MVP]

 

[Guest]

Thanks that worked!

I have a calculation on a Main form where the field trkloads is. I am trying
to subtract out the [contactedwork] (a yes/no field) field from the subform
to get a total trkloads left to fill.

Not getting an error. the result goes up and down as uncheck and recheck the
[contactedwork]field. And refresh. However the answer is incorrect. 7
loads-5 checked = 0 Loads left to fill Answer goes up and down 1 count as
check and uncheck -can it see true maybe if not checked???????




= [trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid =
[dispatchid]

And [contactedwork] = True")

thanks

The criterion

"dispatchid = [dispatchid] and...

is probably the problem; it will count the number of records in
dispatchdetailsquery which have the value of dispatchid equal to
itself - that is, every record in the table!

Pull the criterion out of the quoted string so you're using the value
on the form, rather than the name of the field:

= [trkloads]-DCount("[contactedwork]", "dispatchdetailsquery",
"dispatchid = " & [dispatchid] & " And [contactedwork] = True")

John W. Vinson[MVP]

 

[Guest]

One more thing.

I have to press f9 for the calculation on the main form to update. I attached
Refresh
to the afterupdate event of the check box for contactedwork- but still need
to press F9 anyway to get it to refresh on some other better event

thanks so much

I have a calculation on a Main form where the field trkloads is. I am trying
to subtract out the [contactedwork] (a yes/no field) field from the subform
to get a total trkloads left to fill.

Not getting an error. the result goes up and down as uncheck and recheck the
[contactedwork]field. And refresh. However the answer is incorrect. 7
loads-5 checked = 0 Loads left to fill Answer goes up and down 1 count as
check and uncheck -can it see true maybe if not checked???????




= [trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid =
[dispatchid]

And [contactedwork] = True")

thanks

The criterion

"dispatchid = [dispatchid] and...

is probably the problem; it will count the number of records in
dispatchdetailsquery which have the value of dispatchid equal to
itself - that is, every record in the table!

Pull the criterion out of the quoted string so you're using the value
on the form, rather than the name of the field:

= [trkloads]-DCount("[contactedwork]", "dispatchdetailsquery",
"dispatchid = " & [dispatchid] & " And [contactedwork] = True")

John W. Vinson[MVP]