How to handle Dlookup returning no value

[AusDBGuy]

Hi All,
I've been at this for hours and can't get it to work. :banghead:
I have main form and continuous subform with payments, in the subform header is a unbound textbox that totals the payments based on criteria.

Query sums the totals and Dlookup displays in the textbox. All works well until the subform has no records and the text displays the last total from the previous record. In essence it's not calculating because there is no records on the subform and the query returns no records.. Makes sense.

I want this text to display $0.00 if there is no records instead of the inaccurate value.

If there is no records on the subform Dlookup errors with:
"You entered an expression that has no value".

Tried using NZ same thing happens.
Tried doing a Dcount, same thing happens

I thought that a record count could only be Null or zero?

Anyone have any suggestions?

Thanks in Advance

Using the following statement:
Me.txtTotalPaid = Nz(DLookup("[Total]", "qryMembSumPayments", "[mpClientID]=" & [mpClientID]), 0)

 

[bob fitz]

Not sure this will work but you could try:

Me.txtTotalPaid = Nz(DLookup("[Total]", "qryMembSumPayments", "[mpClientID]=" & Nz([mpClientID],0)), 0)

 

[AusDBGuy]

Hi Bob,
Thanks for help. Gave it a try and get the same error.
I have AllowAdditions turned off on the subform so there isn't even the space for a new record on the form hence there would not even be a Null mpClientID.

Also because there is no records at all on the subform the OnCurrent event doesn't trigger as this is what I have used to update it previously.
Thanks Again

 

[Ari]

Hi

try

Me.txtTotalPaid = Nz(DLookup("Total", "qryMembSumPayments", "mpClientID=" & me!mpClientID), 0)

If mpClientID originates in the main form, use the parent.

Me.txtTotalPaid = Nz(DLookup("Total", "qryMembSumPayments", "mpClientID=" & parent!mpClientID), 0)

 

[bob fitz]

Using the following statement:
Me.txtTotalPaid = Nz(DLookup("[Total]", "qryMembSumPayments", "[mpClientID]=" & [mpClientID]), 0)

Where are you using this.

 

[AusDBGuy]

If mpClientID originates in the main form, use the parent.

Me.txtTotalPaid = Nz(DLookup("Total", "qryMembSumPayments", "mpClientID=" & parent!mpClientID), 0)

Hi Ari,
Thanks for helping. I was using mpClientID which was the Foreign key on the subform. This led to the solution to use the primary key on the main form which is the same value obviously. (Amazing how clearer you think after a sleep!) Now because there is always a clClientID on the main form even if there are no records on the subform the formula returns 0.
I guess the problem was since mpClientID was not being shown on the subform when no record was present, query returns no value not even Null because this is the criteria reference in the filter.

THANK YOU!

Where are you using this.

I was using this on the subforms current event until I realised it wasn't firing when no records present.
Now am using it in a module as it is called from more than one place: After mpPayAmnt get updated for example. The code is now:

Forms!frmClient!sfrmMembPayment.Form.txtTotalPaid = Nz(DLookup("[Total]", "qryMembSumPayments", "[mpClientID]=" & Forms!frmClient!clClientID), 0)




Thank you both for your help.