Defualt Text In Append Query

Tor_Fey · May 10, 2016

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 · May 10, 2016

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 · May 10, 2016

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

Tor_Fey · May 10, 2016

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

CJ_London said:
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

CJ_London · May 10, 2016

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 · May 10, 2016

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

CJ_London said:
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 · May 10, 2016

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")

Defualt Text In Append Query

Tor_Fey

Registered User.

CJ_London

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Tor_Fey

Registered User.

CJ_London

Super Moderator

Tor_Fey

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread