Using Totals Query As Data source within main query (1 Viewer)

Oreynolds

Member
Local time
Today, 00:19
Joined
Apr 11, 2020
Messages
110
Hi,

I have a table "Suppliers" and a table "SuppliersApprovalDocs". Both a a relationship on the SuppliersID field. Within the SuppliersApprovalDocs table there is a date field [DocExpiryDate] and a chk field for [DocRenewalReceived].

If the DocExpiryDate field = <Date() and the DocRenewalRecieved field = 0 then the document is both out of date and no renewal has been received therefore that supplier has an expired document and no longer meets the company approval requirements and needs to be flagged as 'NOT APPROVED'. The supplier may of course have several expired documents in some cases.

I created a totals query "qrySupplierApprovalTotals" to show the number of documents each supplier has that are effectively not approved.

I then have a 'frmPurchaseOrders" form based on "qryPurchaseOrders". I wanted to bring the totals query "qrySupplierApprovalTotals" above in as a source to this main query so on the PurchaseOrder form I could use conditional formatting to show in RED if that supplier was now no longer approved by simply saying If "Approved
" >0 then format RED otherwise format GREEN.

Trouble is when I bring the totals query in as datasource to main qryPurchaseOrders it then locks the main query for editing. I knew you could not edit a totals query but didn't realise if used as a source in this manner it would do the same thing.

Is there any way round this or am I looking at it in the wrong way?
 

isladogs

CID VIP
Local time
Today, 00:19
Joined
Jan 14, 2017
Messages
14,701
If any part of a query is read only then so is the whole query.
Possible workarounds include:
1. Remove the totals query from your main query and use DLookups instead. If you have a lot of records that will be slow.
2. Use a make table query to populate a 'temp table' from your totals query. Use that in your main query
For repeated use, empty and repopulate the 'temp' table using delete & update queries.
3. Use conditional formatting (CF) based on an expression from your totals query.
4. Add a boolean (yes/no) field Approved to your table. Use an update query to set its value based on your totals query and use that field to set the CF colours,

Hopefully one of those will be suitable for your needs.
 

Oreynolds

Member
Local time
Today, 00:19
Joined
Apr 11, 2020
Messages
110
If any part of a query is read only then so is the whole query.
Possible workarounds include:
1. Remove the totals query from your main query and use DLookups instead. If you have a lot of records that will be slow.
2. Use a make table query to populate a 'temp table' from your totals query. Use that in your main query
For repeated use, empty and repopulate the 'temp' table using delete & update queries.
3. Use conditional formatting (CF) based on an expression from your totals query.
4. Add a boolean (yes/no) field Approved to your table. Use an update query to set its value based on your totals query and use that field to set the CF colours,

Hopefully one of those will be suitable for your needs.

Thanks very much for your response, appreciated. I have opted for option 3 and created the following expression in an unbound text field just to tes the results.

The expression works perfectly however it returns a 0 in the case of no records. Ideally I'd like to know if there was a case of NULL as it would mean that a supplier was on the system that had't had a approval documents logged against them. I can catch this potential issue elsewhere but it would be nice to have it as a safety net. Then I would conditional format as:

DCount = 0 Is GREEN
DCount = NULL Is RED
DCount = >0 Is ORANGE

Any thoughts as with Access usually you are doing everything you can to work round NULL's but its the exact opposite in this case! Thanks


=DCount("SubContApprovalID","qrySupplierSubContractApprovalSubformTEST","ExpDate < (Date()+0) AND RenewalRecieved = 0 AND SupplierID=" & [cboSupplierID])
 

strive4peace

AWF VIP
Local time
Yesterday, 18:19
Joined
Apr 3, 2020
Messages
571
hi Oreynolds,

Null is the absence of a value ... so you can't compare it the same way. The NZ function (null to zero) is handy to set the expression to a VALUE so it can be compared

nz( [myfieldname], 0) = 0 if myfieldname is Null. If you also have 0's in your data, choose something you don't have in your data ... I like to use -99
so maybe nz( [myfieldname], -99) = -99 if myfieldname is Null
 

Oreynolds

Member
Local time
Today, 00:19
Joined
Apr 11, 2020
Messages
110
hi Oreynolds,

Null is the absence of a value ... so you can't compare it the same way. The NZ function (null to zero) is handy to set the expression to a VALUE so it can be compared

nz( [myfieldname], 0) = 0 if myfieldname is Null. If you also have 0's in your data, choose something you don't have in your data ... I like to use -99
so maybe nz( [myfieldname], -99) = -99 if myfieldname is Null

Hi thanks for this.

I use the Nz function widely so am familiar with this to remove a NULL but in this case I actually want a NULL but it seems the DCount still gives a 0 even when no records meet the expression criteria?
 

isladogs

CID VIP
Local time
Today, 00:19
Joined
Jan 14, 2017
Messages
14,701
You are correct. DCount will always have a value but DCount(Nz(FieldName, -99)) won't be zero if there are null values

Anyway, there may be a simple workaround.
If you only want those three colours, set the default colour to RED.
The other two conditions will be covered by the CF
 

Users who are viewing this thread

Top Bottom