if statement in sql statement, query builder

[54.69.6d.20]

Need some help. This is a sub report that works in the report.



SELECT mt_Inventory.I4206 AS SerialNumber, mt_Inventory.I4201 AS CustomerID, mt_Inventory.I4202 AS Manufacturer, mt_Inventory.I4203 AS [Model Number], mt_Inventory.I4204 AS Description, mt_Inventory.Room, mt_Inventory.Location, mt_Inventory.I4218 AS Dept
FROM mt_Inventory
WITH OWNERACCESS OPTION;

But when I replace mt_Inventory.I4201, with mt_Inventory.IDOverride. It doesn't show on the report. IDOverride is part of the table, HOWEVER, I'm thinking

SELECT mt_Inventory.I4206 AS SerialNumber, mt_Inventory.IDOverride AS CustomerID, mt_Inventory.I4202 AS Manufacturer, mt_Inventory.I4203 AS [Model Number], mt_Inventory.I4204 AS Description, mt_Inventory.Room, mt_Inventory.Location, mt_Inventory.I4218 AS Dept
FROM mt_Inventory
WITH OWNERACCESS OPTION;


IDOverride is part of the table, HOWEVER, I'm thinking the reason why it's not showing that subreport is because there's null values. There are some values under IDOverride, but there not all filled out.

So I thought something like =IIf(IsNull([IDOverride]),[I4201],[IDOverride]) would work in the criteria. But that failed too.

Thanks for the help

[54.69.6d.20]

Update: Tried this and it doesn't work. For some reason the sub report will disappear from the Report if I make any changes. I've attached some screen shots to get a better idea.
SELECT mt_Inventory.I4206 AS SerialNumber, Nz(mt_Inventory.IDOverride, mt_Inventory.I4201) AS CustomerID, mt_Inventory.I4202 AS Manufacturer, mt_Inventory.I4203 AS [Model Number], mt_Inventory.I4204 AS Description, mt_Inventory.Room, mt_Inventory.Location, mt_Inventory.I4218 AS Dept
FROM mt_Inventory
WITH OWNERACCESS OPTION;

[John_G]

Try this -

Create a new query, go to SQL view, and paste in that SQL. Does it work, i.e. give you the expected results?

In your report, how do you limit which records the sub-report prints, since the SQL you have contains no criteria?

John





(Do you need the OWNERACCESS option? It's only used if you are implementing security-enabled workgroups.)

[54.69.6d.20]

Yes, I'm getting the results I'm looking for creating a new query, however, it's still disappearing after inserting the code in the sub-report.

What do you mean by "limit which records the sub-report prints"? LinkMasterFields and LinkChildFields properties??

[54.69.6d.20]

Yes, I'm getting the results I'm looking for creating a new query, however, it's still disappearing after inserting the code in the sub-report.

What do you mean by "limit which records the sub-report prints"? LinkMasterFields and LinkChildFields properties??

Looks like I'll have to change the sub report to show another field, because the customerID that I'm changing is what's linking the report to the subreport. Works now. Thanks for your time