Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-15-2018, 10:37 AM   #1
kobiashi
Newly Registered User
 
Join Date: May 2018
Posts: 109
Thanks: 15
Thanked 0 Times in 0 Posts
kobiashi is on a distinguished road
insert default value in sql union query

hi

i am trying to insert a default value in my SQL union script, but it doesnt seem to like what im using, please can some one take a look and let me know if im using the wrong method please, so below is the first part of the union query and where im trying to insert the default value:

Code:
SELECT SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MCUDate, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.RSAttention_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.EntryType_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MaximoStatus_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MCUTime, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.ServiceRemoval_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Title, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MaximoNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.WorkType, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists_2.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists_3.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists_4.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles_1.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles_2.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles_3.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Consist,
ISNULL([SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType], 'AfterAMPeak') AS SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType
FROM SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak
UNION ALL
this is what i was using

Code:
ISNULL([SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType], 'AfterAMPeak') AS SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType

im trying to union multiple select queries, im not sure if that makes a difference.

kobiashi is offline   Reply With Quote
Old 09-15-2018, 11:16 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: insert default value in sql union query

IsNull() is a test for Null, not a replacement. I suspect you want the Nz() function. I'd also just alias with a name, not with the query first:

Nz(...) As InServiceEntryType
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-15-2018, 11:26 AM   #3
kobiashi
Newly Registered User
 
Join Date: May 2018
Posts: 109
Thanks: 15
Thanked 0 Times in 0 Posts
kobiashi is on a distinguished road
Re: insert default value in sql union query

Quote:
Originally Posted by pbaldy View Post
IsNull() is a test for Null, not a replacement. I suspect you want the Nz() function. I'd also just alias with a name, not with the query first:

Nz(...) As InServiceEntryType
Awsome, worked a treat, thank you.

kobiashi is offline   Reply With Quote
Old 09-15-2018, 11:28 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: insert default value in sql union query

Happy to help!
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-15-2018, 05:01 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,321
Thanks: 13
Thanked 1,396 Times in 1,330 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: insert default value in sql union query

Quote:
it doesn't seem to like what im using
Telling us the error message or result that you thought was incorrect would have been oh so much more helpful.

for this simple case, the Nz() offered by pbaldy is a better choice (and fixed your syntax problems) but there is nothing inherently wrong with using IsNull() INSIDE an IIf() to substitute a value. HOWEVER, the other problem was with the alias:

as SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InSer viceEntryType

1. You have to provide a way to use the result of the IsNull() so valid syntax would be:
IIf(IsNull(yourfield), "replacementvalue", yourfield) As newname. When you use an IIf() best practice always uses complete syntax so you provide a result for the true condition and also the false condition.
2. You can't qualify the alias with a table or query name. It doesn't make any sense
3. You can't alias a fieldname with it's original name. Although SQL Server does allow this, Access does not)

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert & Union All Select JonyBravo Modules & VBA 1 05-02-2007 02:31 AM
Difficulty combining Insert Into or Select Into with a Union, please help! anon3803 Queries 1 01-23-2007 12:35 PM
[SOLVED] Insert fields into a Union query jonathandavidb Queries 1 10-13-2004 02:52 AM
Insert into sql statement with union query giedrius Queries 4 08-04-2004 12:31 PM
How can I insert link in Excetl to union query in Access Shinken Queries 0 11-24-2002 10:43 AM




All times are GMT -8. The time now is 06:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World