Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-23-2017, 02:26 AM   #1
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Question Add zero as a value with an append query

Morning All;

Is it possible to add the value 0 to a table with an append query within the criteria?

I have an append query as seen in the attachment below, and what i need to do is, if one of the fields in the query has no value or is null then make that value 0.

this does not need to happen on the financialyear field, only the fields showing QTR1, 2, 3, 4.

Is this possible?
Your help as always is appreciated.

Kind Regards
Tor Fey
Attached Images
File Type: png append query.png (14.1 KB, 48 views)

Tor_Fey is offline   Reply With Quote
Old 11-23-2017, 02:30 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,709
Thanks: 138
Thanked 1,535 Times in 1,507 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Add zero as a value with an append query

You'll need to Nz() the values in the underlying query. Then you won't see any nulls.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (11-23-2017)
Old 11-23-2017, 02:42 AM   #3
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Add zero as a value with an append query

Hi Minty;

thanks for the reply;

My qtr field in the underlying query is as follws:

Code:
Qtr1: financialQtr([TF_Ben_Sanction_Date],1)
How do i add the NZ() to the criteria as when i try it; i get the following error:

'The expression you entered has a function containing the wrong number of arguments'

Regards
Tor Fey

Quote:
Originally Posted by Minty View Post
You'll need to Nz() the values in the underlying query. Then you won't see any nulls.

Tor_Fey is offline   Reply With Quote
Old 11-23-2017, 02:51 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,987
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Add zero as a value with an append query

PMFBI

Code:
Qtr1: Nz(financialQtr([TF_Ben_Sanction_Date],1),0)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Tor_Fey (11-23-2017)
Old 11-23-2017, 02:58 AM   #5
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Add zero as a value with an append query

Hi Ridders;

Much appreciated; this has achieved what i needed.

Kind Regards
Tor Fey

Quote:
Originally Posted by ridders View Post
PMFBI

Code:
Qtr1: Nz(financialQtr([TF_Ben_Sanction_Date],1),0)

Tor_Fey is offline   Reply With Quote
Reply

Tags
append , query , table , zero value

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop and Append Query until the Append query no longer produces results. Speiro1 Modules & VBA 3 05-02-2017 12:27 PM
Need to ignore Microsoft Access can't append all the records in the append query DNASok Queries 7 03-21-2013 06:22 AM
Append Query that can append to multiple tables JuniorWoodchuck24 Queries 3 05-25-2011 12:56 AM
Append Query - Append Field keeps changing LB79 Queries 1 07-03-2009 02:09 AM
MS access can't append all the records in the append query japi_s General 3 02-19-2007 06:09 AM




All times are GMT -8. The time now is 01:41 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