Query Help (1 Viewer)

[Acropolis]

I have a DB that tracks fieldworks we do.

Some jobs are chargeable some are not. When the jobs are closed down you select if it is chargeable or not, if it is then it marks the check box as true (-1) and shows a field for who to recharge the job to, which is then selected from a combo box, which is from a table just for who to recharge the jobs to, it is 2 fields, ID and the name, and looks at the name.

This then sets the field in the tblFieldworks for who the job is recharged to, this field is a lookup.

This works fine no problem.

I now need to create a report to display this information. If I only want to show those that are not chargeable no problem, or those that are chargeable no problem.

But if I want to show both and who they are charged to if they are chargeable I have a problem, as those that are not chargeable the RechargeToWhoID field is blank and the querry only returns those records which are not blank and ignores the rest.

This happens in other places as well, and normally I put the below expression in changed to suit needs, but in this case it isn't working for some reason.

Everytime I try and get it to run it throws an error at me : "Cannot find field RechargeToWhoID"

This is strange as it is able to find it for the first part of the IIF as it puts a "N/A" in the field where it needs to be, but doesnt return the other part.

i have checked and double checked the name of the field and this is fine, I have typed it, copied and pasted it and also selected it from below in the expression builder straight from the table and none of it makes any difference.

I have checked the relationship's table and the relationship is there.

Please help, thanks

RechargeTo: IIf(IsNull([tblFieldWorks]![RechargeToWhoID]),"N/A",DLookUp("[ToWho]","tblChargeable","[ID]=[RechargeToWhoID]"))

 

[StarGrabber]

If you add [tblFieldWorks]! and remove the quotation marks...

RechargeTo: IIf(IsNull([tblFieldWorks]![RechargeToWhoID]),"N/A",DLookUp("[ToWho]","tblChargeable",[ID]=[B][tblFieldWorks]![/B][RechargeToWhoID]))

...the error is gone, but the result of the query isn't correct. Try this:

RechargeTo: IIf(IstNull([tblFieldWorks]![RechargeToWhoID]),"N/A",(SELECT tblChargeable.ToWho FROM tblChargeable WHERE tblChargeable.ID=[tblFieldWorks]![RechargeToWhoID];))

 

[Acropolis]

Thanks for that, it worked, but has created another problem now.

When I look at the query and run it, it works fine. But when i put that field into the report it comes up with another error message now:

"Multi-level GROUP BY clause is not allowed in a subquery."

The final code for the query comes out as this:

SELECT tblFieldWorks.FieldWorksID, tblClients.ClientName, tblSites.SiteRef, tblSites.SiteName, tblSiteType.SiteType, tblFieldJobType.FieldJobType, tblFieldWorks.CompleteDate, tblOboxFailReason.FailReason, tblFieldWorks.Chargeable, tblFieldWorks.WorksDone, tblFieldWorks.RechargeAmount, IIf(IsNull([tblFieldWorks]![RechargeToWhoID]),"N/A",(SELECT tblChargeable.ToWho FROM tblChargeable WHERE tblChargeable.ID=[tblFieldWorks]![RechargeToWhoID];)) AS RechargeTo
FROM tblSiteType INNER JOIN ((tblClients INNER JOIN tblSites ON tblClients.ClientID = tblSites.ClientID) INNER JOIN (tblFieldJobType INNER JOIN (tblOboxFailReason INNER JOIN tblFieldWorks ON tblOboxFailReason.ID = tblFieldWorks.OboxFailReasonID) ON tblFieldJobType.FieldJobTypeID = tblFieldWorks.FieldJObTypeID) ON tblSites.SiteID = tblFieldWorks.SiteID) ON tblSiteType.ID = tblSites.SiteTypeID
WHERE (((tblFieldWorks.CompleteDate)>=[TempVars]![tmpFromDate] And (tblFieldWorks.CompleteDate)<=[TempVars]![tmpToDate]) AND ((tblFieldWorks.Complete)=True) AND ((tblClients.ClientID) Like [TempVars]![tmpClientID]))
ORDER BY tblFieldWorks.FieldWorksID;

I havent added any grouping or sorting within the report itself. Any ideas?

 

[StarGrabber]

I've re-built your query (but without the where-condition) and it runs - at least with the test data I've inserted. If you want me to go further, I would need your database with some sample data.