No record yet for dsum process (1 Viewer)

[DKoehne]

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]

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]

@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]

why not use nz(dsum function,0)

 

[arnelgp]

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:

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