Defualt Text In Append Query (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 01:10
Joined
Feb 8, 2013
Messages
121
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:10
Joined
Feb 19, 2013
Messages
16,553
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/library/office/ff836697.aspx
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:10
Joined
May 7, 2009
Messages
19,169
Expr1: IIF(Trim(Forms!frm_Postcharge_main!m_surname_send & "")="","No_Value", Forms!frm_Postcharge_main!m_surname_send)
 

Tor_Fey

Registered User.
Local time
Today, 01:10
Joined
Feb 8, 2013
Messages
121
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


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/library/office/ff836697.aspx
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 01:10
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 01:10
Joined
Feb 8, 2013
Messages
121
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



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

Super Moderator
Staff member
Local time
Today, 01:10
Joined
Feb 19, 2013
Messages
16,553
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")
 

Users who are viewing this thread

Top Bottom