Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-05-2007, 06:39 AM   #1
DarkAngel
Registered User
 
Join Date: Jul 2007
Location: Leeds, UK
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
DarkAngel is on a distinguished road
Using IIF function in query?

I have a field in a query that I want to select the value from a field, but if the value is null, then to select the value from the same named field in a different table.

Basically the whole point is to use the given description unless one doesnt exist, where the default one will be inserted instead.

Cheers,

DarkAngel is offline   Reply With Quote
Old 07-05-2007, 07:03 AM   #2
DarkAngel
Registered User
 
Join Date: Jul 2007
Location: Leeds, UK
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
DarkAngel is on a distinguished road
IIF((isnull [curr value table].[parm description]), [refererence Parms].[parm description], [curr value table].[parm description]) AS [parm description]

That's what I've tried to use but says it has a syntax error, any ideas?
DarkAngel is offline   Reply With Quote
Old 07-05-2007, 08:28 AM   #3
Squidinker
Registered User
 
Join Date: Jun 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Squidinker is on a distinguished road
Just a quick reply DarkAngel - it's not exactly what you need but I don't quite have the time at the moment to tailor it for you. It should be the right avenue though.


The following is from Wazz in reply a thread of mine earlier, so credit to him of course:
Quote:
Private Sub chkFilterForNulls_AfterUpdate()

With Me
If .RecordSource = "qryMyFirstQueryWithoutCriteria" Then
.RecordSource = "qryMySecondQueryWithCriteria"
Else
.RecordSource = "qryMyFirstQueryWithoutCriteria"
End If
End With

End Sub
This would be a VB routine but Wazz would be your man to explain it better.

Squidinker is offline   Reply With Quote
Old 07-05-2007, 08:48 AM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
to do it in a query, you need to put both tables in the query, linked by whatever pk/fk is appropriate

then you can add a field to the query saying in effect

iif(isnull(table1.fieldname),table2.whateverfield, table1.fieldname)

ie if the field in table1 is null, then use a field from table2, else use the field from table 1.

but you need both tables to be available to the query.
gemma-the-husky is offline   Reply With Quote
Old 07-05-2007, 09:01 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,480
Thanks: 15
Thanked 4,147 Times in 4,078 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 pbaldy is a splendid one to behold
In your original code:

IIF((isnull [curr value table].[parm description])...

Note the opening parenthesis for the IsNull function is out of place. In any case, the Nz() function would be simpler.
__________________
Paul
Microsoft Access MVP 2007-2019

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 07-06-2007, 02:39 AM   #6
DarkAngel
Registered User
 
Join Date: Jul 2007
Location: Leeds, UK
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
DarkAngel is on a distinguished road
Thankyou for help guys, turned out the the blank values were zero length strings as well as null values, so had to use IIF with an OR clause in, but works 100% now.

parm description: IIf(IsNull([curr value table].[parm description]) Or [curr value table].[parm description]="",[reference Parms].[parm description],[curr value table].[parm description])

Cheers,

DarkAngel 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
Large Query Aggregate function error twiggy Queries 1 09-15-2006 07:34 AM
Using Checkboxes to select multiple fields in an iif query youngerpants Queries 6 12-07-2005 02:46 AM
Bizarre problem with a function in a query.. proben930 General 3 01-26-2005 09:00 PM
Function result used in a Query DavidW Queries 8 09-18-2003 09:41 AM
empty fields causing problems Christos Queries 2 04-27-2003 06:31 PM




All times are GMT -8. The time now is 04:38 PM.


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

Featured Forum post


Sponsored Links


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