Domain functions in Query

[hceonetman]

I have two current queries, one with employee info including sick time balance, the second with sick leave taken, linked one-to-many by payId. I'd like to combine these in a query that will show the employee's current balance. I used the expression builder to create:

Sick_Bal: [Earned Sick Time]![Earned Sick Hours]-DSum("[Used Sick Time]![Hours]","Used Sick Time","[Used Sick Time]![PayID]=[Earned Sick Time]![PayID]&quot

First of all, can domain functions be used within queries? I've tried combinations of changing the brackets and quotes, and received various error messages. As written above it produces "MS Can't find the name 'Earned Sick Time!PayId'" Spelling is correct in the query.
I didn't write the original queries but would like to use them in a new report.

Thanks for your help

 

[Golom]

Yes you can use them but I'm not sure about the "correlated" one that you are attempting. You may get around it using a correlated sub-query like

Sick_Bal: [Earned Sick Time].[Earned Sick Hours]-

(Select SUM([Used Sick Time].[Hours])
 FROM [Used Sick Time] 
 WHERE [Used Sick Time].[PayID]=[Earned Sick Time].[PayID])

 

[dhookom]

Golom's solution is more efficient but to get you straight on the DSum()
Sick_Bal: [Earned Sick Time]![Earned Sick Hours]-DSum("[Hours]","[Used Sick Time]","[PayID]=" & [PayID])


Duane
MS Access MVP
Find out how to get great answers faq219-2884.