Assigning a value in a query if a null is returned (1 Viewer)

theseus

Registered User.
Local time
Today, 05:54
Joined
Aug 6, 2018
Messages
32
I am having trouble with my database. I have a query that has all of the values assigned to certain items in the database. However some items do not have a value assigned to them.



Currently when the query is run, items with no value assigned show as a blank. I would like to replace that blank with a value that I assign. How?


I have tried
Code:
 IIF(IsNull ([Item value]), "180", [Item Value])
in my criteria.That did not seem to do anything. My blanks were still there.
 

theseus

Registered User.
Local time
Today, 05:54
Joined
Aug 6, 2018
Messages
32
Stupid me... Don't put it in the Criteria section. I can see this is going to be one of those days...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:54
Joined
Oct 29, 2018
Messages
21,358
Hi. You could also try using the Nz() function. For example:
Code:
Nz([FieldName],180)
Cheers!
 

June7

AWF VIP
Local time
Today, 01:54
Joined
Mar 9, 2014
Messages
5,425
Is Null vs Nz
Review http://allenbrowne.com/QueryPerfIssue.html

Yes, I do use Nz() in queries.

However, I discovered that Excel will not work with queries using Nz(). I had to redesign an Access query object to not use so that Excel could pull data. Excel VBA does not recognize Nz(). Unless there is some library reference that should be set in Excel.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 28, 2001
Messages
27,001
Unless there is some library reference that should be set in Excel.

My first guess is the MS Access nn.n Object Library.
 

June7

AWF VIP
Local time
Today, 01:54
Joined
Mar 9, 2014
Messages
5,425
Whoops thought you said "Office nn.n" (need to read what I am looking at:)). Yes, have to add Microsoft Access nn.n Object Library.

When I first encountered this issue, I was still new to VBA and it has been years since I had to deal with this particular issue.

Always learning.
 
Last edited:

Users who are viewing this thread

Top Bottom