470,624 Members | 2,538 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,624 developers. It's quick & easy.

Access 2003 SQL Conditional SELECT Statement

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?
Jul 6 '10 #1

✓ answered by OldBirdman

Instead of concatination, you can use an IIf() function. IIf(condition, result-when-true, result-when-false). So you might have:
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, IIf(Not IsNull(NetName), NetName, ModelID) AS Name
  2. FROM tblAssets;
  3.  

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

qryAssetsModelID returns all entries where NetName is NULL with the fields labeled appropriately for further use:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAssets.ID, tblAssets.ModelID AS Name, tblAssets.UID
  2. FROM tblAssets
  3. WHERE (((tblAssets.NetName) Is Null) AND ((tblAssets.Status)="In Use" Or (tblAssets.Status)="Open" Or (tblAssets.Status)="Repair" Or (tblAssets.Status)="Stored"));
  4.  
qryAssetsNetName returns all entries where NetName is not NULL with the fields labeled appropriately for further use:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAssets.ID, tblAssets.NetName AS Name, tblAssets.UID
  2. FROM tblAssets
  3. WHERE (((tblAssets.NetName) Is Not Null) AND ((tblAssets.Status)="In Use" Or (tblAssets.Status)="Open" Or (tblAssets.Status)="Repair" Or (tblAssets.Status)="Stored"));
  4.  
qryAssetsByUID uses a UNION SELECT to concatenate the two previous queries and filter by the selection of cboUID in formSupport:
Expand|Select|Wrap|Line Numbers
  1. SELECT qryAssetsModelID.ID, qryAssetsModelID.Name
  2. FROM qryAssetsModelID
  3. WHERE (((qryAssetsModelID.UID)=Forms!formSupport!cboUID));
  4.  
  5. UNION SELECT qryAssetsNetName.ID, qryAssetsNetName.Name
  6. FROM qryAssetsNetName
  7. WHERE (((qryAssetsNetName.UID)=Forms!formSupport!cboUID));
  8.  
But the UNION SELECT portion of qryAssetsByUID isn't returning any entries.

I created seperate queries out of each SELECT statement in qryAssetsByUID, the qryAssetsModelID portion returns entries OK and the qryAssetsNetName isn't.

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

Am I mislabeling an entry? Is there a misspelling? Or have I bumped up against something else?
Jul 6 '10 #2
OldBirdman
675 512MB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, nz([Name],"") & nz([ModelID],"") AS Display FROM tblAssets
Jul 6 '10 #3
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?
Jul 6 '10 #4
OldBirdman
675 512MB
Instead of concatination, you can use an IIf() function. IIf(condition, result-when-true, result-when-false). So you might have:
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, IIf(Not IsNull(NetName), NetName, ModelID) AS Name
  2. FROM tblAssets;
  3.  
Jul 6 '10 #5
Excellent! That worked perfectly. And now I've got a bit of knowledge of SQL conditionals. Thank you very much.
Jul 6 '10 #6
OldBirdman
675 512MB
You're welcome. Glad you learned something.
Jul 6 '10 #7
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblAssets.ID, IIf(Not IsNull(tblAssets.NetName),tblAssets.NetName,tblAssets.ModelID) AS Name, tblAssets.SN
  2. FROM tblAssets
  3. WHERE (((tblAssets.UID)=[Forms]![formSupport]![cboUID]) AND (([Empty]) Is Null)) OR (((tblAssets.UID)=[Empty]) AND (([Empty]) Is Not Null))
  4. ORDER BY tblAssets.ID;
  5.  
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?
Jul 6 '10 #8
OldBirdman
675 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. ...AND (([Empty]) Is Null))
should be:
Expand|Select|Wrap|Line Numbers
  1. ...AND (IsNull([Empty]))
And finally, no...you have not reached any limits.
Jul 6 '10 #9
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAssets.ID, IIf(Not IsNull(tblAssets.NetName),tblAssets.NetName,tblAssets.ModelID) AS Name, tblAssets.SN
  2. FROM tblAssets
  3. WHERE ((tblAssets.UID)=[Forms]![formSupport]![cboUID]) Or (IsNull([Forms]![formSupport]![cboUID]))
  4. 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.
Jul 7 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Thomas Baxter | last post: by
5 posts views Thread by vircalendar via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.