Search code examples
ms-accessselecthardcode

Access SELECT Statement and Hard coded Value

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?

Solution

  • 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()