Access SELECT Statement and Hard coded Value

Question

Here is my VB SQL statement that is applied to the rowsource of an unbound list box on Form_Current:

SelectionSQL = "SELECT tbl_Patches_Cisco_SAs_Applicability.PatchID, tbl_Patches_Cisco_SAs_Applicability.OS 
      FROM tbl_Patches_Cisco_SAs_Applicability 
      WHERE (((tbl_Patches_Cisco_SAs_Applicability.PatchID)=[Forms]![Patch_Management_Cisco_Stage2]![Publication_ID]));"

It works great, the only problem is that I want the rowsource to include the hardcoded value of "NA", without having to create an associated record. Is this possible and how? i.e. to somehow add NA to the rowsource?

Yes, I can't just add an "NA" record to the queried table... because every record in that table needs to be very carefully created... the represent a list of cyber assets that are under compliance, so an NA record would be bad. Simply state, the question is "can I include a hardcoded value somehow in the rowsource or SQL statement". I was thinking either SelectionSQL & "NA", or perhaps somehow assigning this sql statement to a record set and then adding NA to the recordset, and somehow assigning that to the row source. let me know. thanks. — gunslingor, Jan 07 '15 at 15:37

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

You can use a UNION query which adds a single row of fabricated values to what you have now.

SELECT a.PatchID, a.OS
FROM tbl_Patches_Cisco_SAs_Applicability AS a
WHERE a.PatchID=[Forms]![Patch_Management_Cisco_Stage2]![Publication_ID]
UNION ALL
SELECT 0 AS PatchID, 'NA' AS OS
FROM Dual;

Dual can be any table or query which returns only one row. I use a custom table for that purpose: CreateDualTable()

edited May 23 '17 at 12:12

Community

1
1

answered Jan 07 '15 at 15:37

HansUp

95,961
11
77
135

I actually gave the wrong query but I get the gist. I've created a new table called "NA" and have assigned it only one field called "NA", with only one value "NA". Here is the statement: Me.OSs_On_Network.RowSource = "SELECT DISTINCT CYBER_ASSETS.OS, CYBER_ASSETS.CPCA_or_equal FROM CYBER_ASSETS WHERE (CYBER_ASSETS.CPCA_or_equal='YES') UNION ALL SELECT 0 AS PatchID, 'NA' AS OS FROM na;" This almost works, but it adds "0" to the list instead of NA. Any ideas? – gunslingor Jan 07 '15 at 16:01
Ah! I guessed you wanted "NA" in the *OS* column. Since I guessed wrong, swap the values in those two columns: `SELECT 'NA' AS PatchID, 0 AS OS` – HansUp Jan 07 '15 at 16:04
This seems to do the trick with this setup let me know if you think it will cause any issues... stating the field name instead of 0: Me.OSs_On_Network.RowSource = "SELECT DISTINCT CYBER_ASSETS.OS, CYBER_ASSETS.CPCA_or_equal FROM CYBER_ASSETS WHERE (CYBER_ASSETS.CPCA_or_equal='YES') UNION ALL SELECT NA AS PatchID, 'NA' AS OS FROM na;" – gunslingor Jan 07 '15 at 16:05
Added the zero, no change in functionality from previous version. Seems to work either way... Final statement: Me.OSs_On_Network.RowSource = "SELECT DISTINCT CYBER_ASSETS.OS, CYBER_ASSETS.CPCA_or_equal FROM CYBER_ASSETS WHERE (CYBER_ASSETS.CPCA_or_equal='YES') UNION ALL SELECT NA AS PatchID, 0 AS OS FROM na;" Thank you so much... let me know if you see any issues. – gunslingor Jan 07 '15 at 16:06
OK, you stored "NA" in your NA table. I don't see a problem. :-) – HansUp Jan 07 '15 at 16:07

score 0 · Answer 2 · answered Jan 07 '15 at 15:41

One possibility would be to use an UNION query:

SelectionSQL = "SELECT tbl_Patches_Cisco_SAs_Applicability.PatchID,
    tbl_Patches_Cisco_SAs_Applicability.OS FROM tbl_Patches_Cisco_SAs_Applicability WHERE
    (((tbl_Patches_Cisco_SAs_Applicability.PatchID)=[Forms]![Patch_Management_Cisco_Stage2]![Publication_ID])) 
    UNION SELECT TOP 1 0, 'NA' FROM tbl_Patches_Cisco_SAs_Applicability"

Access SELECT Statement and Hard coded Value

2 Answers2