Sending a Query Sum to a Text Box

[music_al]

Hi,

I have a query which results in a single total representing the number of days absent relating to an employee.

I have embedded the query in my Employee form and I have managed to pass the Total number to a text box using this code.

Absent_Days.Value = [Absent_Days_Query]![SumOfQty_Half_Days]

But, when the query doesnt return any results, I am getting the following Error...

Runtime Error '2113' - The value you entered isnt valid for this field.

I can catch it with an Error Handler, but I want the code to put a 0 (zero) in my text box. I cant seem to get it to work.

Any suggestions ?

Al

 

[Galaxiom]

Try:
IIF(IsError([Absent_Days_Query]![SumOfQty_Half_Days]),Null, [Absent_Days_Query]![SumOfQty_Half_Days])

However you can directly sum a field in the recordset of a form.
In the footer, place a textbox with the control source: =Sum([fieldname])

You may need to add the IsError catch to it too.

 

[neileg]

Probably easier to use DSum() and Nz() than use a query.

 

[music_al]

The Primary Key for my Employee form is "Employee_ID" so as I go to the next record (next employee) the number of absent days updates for each employee. I obviously have a criteria filter in my query for Employee_ID

So how would I Sum the total of just a particular Employee ID in a table ? I thought the best way to do that would be in a query ?

Al

 

[Galaxiom]

Probably easier to use DSum() and Nz() than use a query.

Queries are very efficient and will work directed to an SQL database server. It is a good practice use pure sql wherever possible and resort to functions when the query solution is too complex. The little bit of extra for query is worth it for the future expandability and performance.

 

[music_al]

I tried this but it doesnt work...

Absent_Days.Value = Nz([Absent_Days_Query]![SumOfQty_Half_Days],0)

What is wrong with the syntax ?

 

[Galaxiom]

Nz converts nulls. Nulls are valid values in a record. An error is something else again because there is no record.

 

[music_al]

Ahhh I see.

Is there a way of returning a zero if there is no record ?

Al

 

[Galaxiom]

Just change the Null in the IIf to a zero.

 

[neileg]

Queries are very efficient and will work directed to an SQL database server. It is a good practice use pure sql wherever possible and resort to functions when the query solution is too complex. The little bit of extra for query is worth it for the future expandability and performance.

Mmm...

When all you are expecting is a single value, aggregate functions are much more convenient than queries. Whether they are more efficient than SQL depends very much on the circumstances. It is when the solution is complex that functions start to hurt performance and embedding functions within a query can be a great way of really slowing things down if you're not careful.

I don't agree that using pure SQL is best practice in all cases. Simplicity and clarity are the best basis for developing an application. Scalability may be one of the considerations for your design. Making small apps truly scalable if the likelihood of upsizing is remote is a bit of a waste of effort.

That's my view, anyway!

 

[Galaxiom]

The discussion of a domain aggregate function vs sql is somewhat acedemic in this particular case as the best solution did not involve another query. There is no doubt the efficient solution is simply a Sum function on the form's recordset as I originally suggested.

The DSum would be a comparatively poor choice because it must open another instance of a recordset that is already available in the form. The sql Sum simply acts upon values already in memory. Where the application is interfaced with sql server the comparision becomes even more stark. Access can optimise a query to the server but since the domain aggregates are Access only functions, the entire duplicate recordset must be loaded into Access to return one value.

When comparing sql with domain aggregate functions, the compact syntax of these functions should not be confused with efficiency. Where the information is included in the recordset of the form it is readily accessible by simply refering to the fieldname. This is not only much faster and less memory intensive than opening the second copy of the recordset but is syntactically far simpler than the domain functions once the sql is written.

If the required information can be included in the record source query this is *always* the better choice. Unnecessarily using domain aggregates is poor design and a bad habit. Like so much in Access, the right way is really no harder once fluency has been achieved.

 

[neileg]

Galaxiom, you are entitled to your view. I will let it rest there.