No record yet for dsum process (1 Viewer)

DKoehne

Registered User.
Local time
Today, 09:11
Joined
Apr 10, 2017
Messages
49
Hello. I have a database that will have the following scenario: a form will check to see if a record exists in a table as part of a calculation nz(dsum function). When no record exists yet (and this will happen) I was getting an error. I have built a workaround with a trigger. The trigger creates a record with a zero amount to be used in the calculation. Later I would delete the zero record, but this seems ridiculous. My questions is: what other options should I be looking at, what is a better practice when no record exists and you are including it in a calculation? Looking for ideas here... Thanks.
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
2,793
Regardless if the record exists or not, DLookup returns either Null or a value; you don't need a domain aggregate function for this. Not clear what you mean by including a record in a calculation. So maybe
SomeVariable = Nz(DLookup("somefield", "sometable", "somecriteria"),0)
then do what makes sense with that - either it is 0 or greater than 0. Not enough info in your post to know what that might be.
 

Cronk

Registered User.
Local time
Tomorrow, 02:11
Joined
Jul 4, 2013
Messages
2,359
@DKoehne, so it seems to me that you are using the value of nz(dsum(...) to get the value of a corresponding record in some lookup table and if it is not there, to add a new record to your lookup table with the lookup value of 0, presumably to edit to the nz(dsum(..) value later.

If so, I would have the code inform the user that the corresponding record in the lookup table does not exist and prompt for the corresponding value is for the nz(dsim(..) and add the record with the right value rather than 0
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:11
Joined
Feb 19, 2013
Messages
12,093
why not use nz(dsum function,0)
 

arnelgp

error reading drive A:
Local time
Tomorrow, 00:11
Joined
May 7, 2009
Messages
9,336
to check if there is any record in a table, use DCount("1", "tableName").
it will return 0 or number of records in that table. ex:

Code:
SomeVariable = 0
If dCount("1", "sometable") > 0 Then _
    SomeVariable = Nz(DSum("somefield", "sometable", "somecriteria"),0)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom