Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-10-2016, 05:23 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 Defualt Text In Append Query

Good Afternoon All;

Is it possible to have an append query enter some default text in a table should the original text on a form be null?

For Example:

My Query has the current criteria:

Field: Expr1: Forms!frm_Postcharge_main!m_surname_send
Appending to: word_m_name_send (on: tbl_Postcharge_main)

So what I want to do is, if 'Expr1' above is null, automatically append 'No_Value' to tbl_Postcharge_main without prompting the user to enter anything.

Your Help On This Would Greatly Be Appriciated J

Regards
Tor Fey

Tor_Fey is offline   Reply With Quote
Old 05-10-2016, 05:37 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,433
Thanks: 40
Thanked 3,367 Times in 3,262 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Defualt Text In Append Query

the expression would be

nz(myValue,"No Value")

however it is usually better to leave nulls as nulls and use the format properties which for strings would be (you couldn't assign a text 'no value' to a number or date for example).

@;"No Value"

or if you really want it to stand out

@;[Red]"No Value"

see this link for more information

https://msdn.microsoft.com/en-us/lib.../ff836697.aspx
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Tor_Fey (05-17-2016)
Old 05-10-2016, 05:46 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Defualt Text In Append Query

Expr1: IIF(Trim(Forms!frm_Postcharge_main!m_surname_send & "")="","No_Value", Forms!frm_Postcharge_main!m_surname_send)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Tor_Fey (05-17-2016)
Old 05-10-2016, 06:34 AM   #4
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: Defualt Text In Append Query

HI CJ;

Thanks for the reply; is it just a case then of entering: nz(myValue,"No Value") in the criteria field of my append query?

Kind Regards
Tor Fey


Quote:
Originally Posted by CJ_London View Post
the expression would be

nz(myValue,"No Value")

however it is usually better to leave nulls as nulls and use the format properties which for strings would be (you couldn't assign a text 'no value' to a number or date for example).

@;"No Value"

or if you really want it to stand out

@;[Red]"No Value"

see this link for more information

https://msdn.microsoft.com/en-us/lib.../ff836697.aspx

Last edited by Tor_Fey; 05-10-2016 at 06:35 AM. Reason: Spelling
Tor_Fey is offline   Reply With Quote
Old 05-10-2016, 06:44 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,433
Thanks: 40
Thanked 3,367 Times in 3,262 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Defualt Text In Append Query

No, in the field row for the column you want updating, instead of the value

like this

nz(Forms!frm_Postcharge_main!m_surname_send,"No Value")
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Tor_Fey (05-17-2016)
Old 05-10-2016, 06:50 AM   #6
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: Defualt Text In Append Query

Ah, I see, unfortunately this won't work as I only want to show 'No Value' should the fields on my form have no data entered in to them. Most of the time they will; and the code: Expr1: Forms!frm_Postcharge_main!m_surname_send, will then append this code to my table, what I need is some criteria to add the text 'No Value' should the fields on my form have no data entered when this append query is run.

Thanks
Tor Fey



Quote:
Originally Posted by CJ_London View Post
No, in the field row for the column you want updating, instead of the value

like this

nz(Forms!frm_Postcharge_main!m_surname_send,"No Value")
Tor_Fey is offline   Reply With Quote
Old 05-10-2016, 06:54 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,433
Thanks: 40
Thanked 3,367 Times in 3,262 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Defualt Text In Append Query

you don't need expr1 for an append query, but if you really want it

Expr1:nz(Forms!frm_Postcharge_main!m_surname_send, "No Value")


__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Tor_Fey (05-17-2016)
Reply

Tags
append , default text , forms , query , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query - Change text in field MGS Queries 7 02-08-2016 03:30 PM
query producing parameter value -set defualt crossy5575 Queries 9 11-06-2015 07:32 PM
Defualt Checkbox and text SicNeMeSiS Forms 1 12-09-2008 02:07 AM
SUM/APPEND text in a select query meacho Queries 4 01-17-2008 02:14 AM
Format as Text in Append Query Rats Queries 5 08-12-2005 06:22 AM




All times are GMT -8. The time now is 11:43 PM.


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