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?
9 7843
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?
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
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?
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;
-
Excellent! That worked perfectly. And now I've got a bit of knowledge of SQL conditionals. Thank you very much.
You're welcome. Glad you learned something.
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?
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 finally, no...you have not reached any limits.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Guy Hocking |
last post by:
Hi there,
I have a problem in my ASP/SQL Server application i am developing, i hope
you guys can help.
I have a ASP form with list boxes populated by SQL tables. When a user
selects a value in a list box and submits the form the value is put into a
session variable and the relevant page is displayed (in accordance to one of
the list boxes).
The page is then displayed with the relevant SQL data. So far i have got the
|
by: Thomas Baxter |
last post by:
Is it possible to have a conditional union statement in a stored proc?
Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.
Is the only solution to create a dynamic sql string then call exec on
it?
Any help appreciated.
|
by: web developer |
last post by:
hi
I need to write a stored procedure that takes input parameters,and
according to these parameters the retrieved fields in a select
statement are chosen.
what i need to know is how to make the fields of the select statement
conditional,taking in consideration that it is more than one field
added
for example
SQLStmt="select"
if param1 then
|
by: grunar |
last post by:
After some thought on what I need in a Python ORM (multiple primary
keys, complex joins, case statements etc.), and after having built
these libraries for other un-named languages, I decided to start at the
bottom. What seems to plague many ORM systems is the syntactic
confusion and string-manipulation required to build the SQL Statements.
If you want to do a Left Outer Join, support nested functions, and a
nested conditional clause, you'd...
|
by: brAccess |
last post by:
I recently installed Access 2003 and immediately noticed problems with
applications that work fine in 2000 and XP. I use conditional
formatting on continuous forms for a number of reasons. When these
forms are opened in 2003, Access seems to go into a continuous loop.
Has anyone else experienced this problem? Does anyone know of a
solution short of not using conditional formatting in continuous
forms?
Here is an example:
| |
by: Wayne Aprato |
last post by:
I have a simple database which was originally written in Access 97.
When converted to Access 2000 file format it ran flawlessly in Access
2002. I've just tried to run it in Access 2003 and I am seeing the
following behaviour:
Some of the fields on the continuous main form which is a list of jobs
with their related details "flicker". While this is going on the form
seems to function as it should and isn't locked up. If I click and
hold...
|
by: dddsssdddsss |
last post by:
A comment and a question
To anyone who is using conditional formatting, beware that in Access
2007 the color pallette is not the same as the color pallette in Access
2003. So if you have a particular color you were using as a standard
throughout your application, the only way to continue using that color
is to code the color.
Anyone else notice how forms in Access 2007 display the conditional
formatting much slower than in Access...
|
by: wongray |
last post by:
Hi,
I have an access question.
it is possible to set a formula in access to check the current date - the date field.?
Kindly advise.
Regards
Raymond
|
by: vircalendar via AccessMonster.com |
last post by:
Here's a problem I haven't seen discussed.
I have a continuous form with a conditional format for the background color
of a text box. It had been working fine until I upgraded to Access 2003 SP3.
Now, with no other changes, the color change for condition 1 doesn't work.
Instead, the color displayed is my default color. Conditions 2 and 3 work as
they always have. Has anyone seen this?
--
Message posted via AccessMonster.com
|
by: tiptap |
last post by:
Hey Guys,
I have a huge statement loads of if statements in... and its getting bigger.
On closer inspection there is only 3 difference in the select statement. so I thought I could cut the whole thing down to just 1 select statement if I have a conditional Having.
I've simplified the IF statement down a bit to give you an idea of what im trying to achieve
IF @month <> 0 & @diffFuture = 0 & @showDate <> 0
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |