Access 2003 SQL Conditional SELECT Statement

[mbedford]

Structure:
Table: tblAssets
Fields: ID, UID, ModelID, NetName

I'm trying to populate a query which I'll then use for the source of a Combo Box in a form.

The query will return two fields from three fields in tblAssets: ID (which each asset entry has) and Name, which defaults to NetName (for workstations and printers) and to ModelID (for furniture and non-networked assets) if NetName is empty.

Can this be done in a single query or do I need to use two queries, one which pulls all the NetNames and one which pulls all the ModelIDs where NetName = "" and then INNER JOIN?

[mbedford]

I'm trying the multi-query method right now.

qryAssetsModelI D returns all entries where NetName is NULL with the fields labeled appropriately for further use:

SELECT tblAssets.ID, tblAssets.ModelID AS Name, tblAssets.UID
FROM tblAssets
WHERE (((tblAssets.NetName) Is Null) AND ((tblAssets.Status)="In Use" Or (tblAssets.Status)="Open" Or (tblAssets.Status)="Repair" Or (tblAssets.Status)="Stored"));

qryAssetsNetNam e returns all entries where NetName is not NULL with the fields labeled appropriately for further use:

SELECT tblAssets.ID, tblAssets.NetName AS Name, tblAssets.UID
FROM tblAssets
WHERE (((tblAssets.NetName) Is Not Null) AND ((tblAssets.Status)="In Use" Or (tblAssets.Status)="Open" Or (tblAssets.Status)="Repair" Or (tblAssets.Status)="Stored"));

qryAssetsByUID uses a UNION SELECT to concatenate the two previous queries and filter by the selection of cboUID in formSupport:

SELECT qryAssetsModelID.ID, qryAssetsModelID.Name
FROM qryAssetsModelID
WHERE (((qryAssetsModelID.UID)=Forms!formSupport!cboUID));

UNION SELECT qryAssetsNetName.ID, qryAssetsNetName.Name
FROM qryAssetsNetName
WHERE (((qryAssetsNetName.UID)=Forms!formSupport!cboUID));

But the UNION SELECT portion of qryAssetsByUID isn't returning any entries.

I created seperate queries out of each SELECT statement in qryAssetsByUID, the qryAssetsModelI D portion returns entries OK and the qryAssetsNetNam e isn't.

qryAssetsNetNam e returns the data correctly. formSupport is open and a UID is selected in cboUID that has assets assigned which show in qryAssetsNetNam e.

Am I mislabeling an entry? Is there a misspelling? Or have I bumped up against something else?

[OldBirdman]

The simple way is to concatinate the two fields. If [Name] is null or an empty string when it is not a networked asset and [ModelID] is null or an empty string when it is a networked asset , then

SELECT ID, nz([Name],"") & nz([ModelID],"") AS Display FROM tblAssets

[mbedford]

OldBirdman, the only issue is that ModelID is always filled. Networked assets have ModelID entries in addition to the NetName entry.

The logic is similar to an If... Else. IF NetName Is Not Null, NetName as Name. Else, ModelID as Name.

Can that be translated into a single query?

[OldBirdman]

Instead of concatination, you can use an IIf() function. IIf(condition, result-when-true, result-when-false). So you might have:

SELECT ID, IIf(Not IsNull(NetName), NetName, ModelID) AS Name
FROM tblAssets;

[mbedford]

Excellent! That worked perfectly. And now I've got a bit of knowledge of SQL conditionals. Thank you very much.

[OldBirdman]

You're welcome. Glad you learned something.

[mbedford]

Ok, an addendum question here:

If cboUID is blank, I want all possible records returned.

Based on the instructions here: http://www.databasedev.co.uk/blank_query_parameter.html

I came up with the following code:

SELECT tblAssets.ID, IIf(Not IsNull(tblAssets.NetName),tblAssets.NetName,tblAssets.ModelID) AS Name, tblAssets.SN
FROM tblAssets
WHERE (((tblAssets.UID)=[Forms]![formSupport]![cboUID]) AND (([Empty]) Is Null)) OR (((tblAssets.UID)=[Empty]) AND (([Empty]) Is Not Null))
ORDER BY tblAssets.ID;

Which doesn't work.

Is it because I've already got one set of Criteria going in the UID field?

How do you add the Condition for when cboUID of formSupport is blank, all possible entries are returned?

[OldBirdman]

Null isn't a value. Therefore, it cannot be directly compared with a value, whether numeric, string, or boolean. To test for Null, you use the function IsNull(). This function returns a True/Yes/-1 or a False/No/0. This is necessary with SQL.
For example, the phrase:

...AND (([Empty]) Is Null))

should be:

...AND (IsNull([Empty]))

And finally, no...you have not reached any limits.

[mbedford]

I wondered how that ended up being incorrect as what I'd done is added the info from that link above to the Query Builder view (not my favorite, I read the SQL better) and then viewed the SQL output which was as I posted above.

However, doing as you instructed and finding a few extraneous parens causing missing operator errors, I came up with this code:

SELECT tblAssets.ID, IIf(Not IsNull(tblAssets.NetName),tblAssets.NetName,tblAssets.ModelID) AS Name, tblAssets.SN
FROM tblAssets
WHERE ((tblAssets.UID)=[Forms]![formSupport]![cboUID]) Or (IsNull([Forms]![formSupport]![cboUID]))
ORDER BY tblAssets.ID;

And it works.

Now, using a simple requery command on the Update event on cboUID, I can filter the cboAssetID selection to only show those assets assigned to the user. I'll use a checkbox to allow this filter to be turned off, and I'm still looking into how to apply that. But the base functionality is there. Many thanks to you.