Add a sentence to the end of every cell in a field? **

[Guest]

I have a sentence I'd like to add to the end of every field in one of my
tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here" but some need " My sentence here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[Guest]

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here." but some need " My sentence here."

Jarrod

 

[Ken Snell [MVP]]

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here." but some need " My sentence here."

Jarrod

I have a sentence I'd like to add to the end of every field in one of my
tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here" but some need " My sentence here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[John Vinson]

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here." but some need " My sentence here."

How do you distinguish a field which contains a sentence from one
which doesn't? Could you post an example?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Ken Snell [MVP]]

Or

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here." but some need " My sentence here."

Jarrod

I have a sentence I'd like to add to the end of every field in one of my
tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here" but some need " My sentence here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[Guest]

The column is called [usewith]. It contains recommendation of products to
use with each particular product/record,or random product info (a skuid/stock
number field is the primary key).

So, some of the fields are NULL, with no recommendation. While others may
contain something like "Sale price coming soon!" or "Adheres to plastics,
metals, vinyls, fiberglass, wood, fabrics, ceramic, glass, leather, paper,
more! Dries fast, flexible, and clear."

So, some of the fields I could update to: [usewith] & "The new sentence."
But, the fields that already contain a sentence would need two spaces before
the new sentence, hence the Iif question.

Jarrod

 

[Guest]

Can you please tell me literally, what this expression means?

Thanks much for the feedback.

Jarrod

Or

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here." but some need " My sentence here."

Jarrod

:

I have a sentence I'd like to add to the end of every field in one of my
tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here" but some need " My sentence here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[Guest]

This works except for one thing. I need an additional space before the "new
sentence" in the instances that it's added to a NULL cell. Unfortunately,
right this sec, I can't figure out where in the expression I need to add
another space.

Jarrod

Or

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here." but some need " My sentence here."

Jarrod

:

I have a sentence I'd like to add to the end of every field in one of my
tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field. So
some of them need this "My sentence here" but some need " My sentence here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[John Vinson]

So, some of the fields I could update to: [usewith] & "The new sentence."
But, the fields that already contain a sentence would need two spaces before
the new sentence, hence the Iif question.

Ok, use IIF:

IIF(IsNull([usewith], "", [usewith] & " ") & "the new sentence."


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Ken Snell [MVP]]

Sure:

IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

This part: Len([Field] & "")>0
is testing the field for both a Null value and an empty string value. By
concatenating the "" (empty string), if the field's value is Null, you get
an empty string. If the length of the result is greater than zero, then the
field is not empty.

This part: [Field] & " "
is what is used as the True result of the IIf function's test. It reuses the
current value of the field and then adds a blank space behind it.

This part: ""
is what is used as the False result of the IIf function's test.
--

Ken Snell
<MS ACCESS MVP>

Can you please tell me literally, what this expression means?

Thanks much for the feedback.

Jarrod

Or

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field.
So
some of them need this "My sentence here." but some need " My sentence
here."

Jarrod

:

I have a sentence I'd like to add to the end of every field in one

of
my

tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field.
So
some of them need this "My sentence here" but some need " My sentence
here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[Ken Snell [MVP]]

ACCESS will not let you "keep" a leading blank space in a field's value;
it'll strip it off. Thus, there is no benefit to adding the blank space.

But if you want to try it anyway, then use this expression:

UpdateTo: IIf(Len([Field] & "")>0,[Field],"") & " " & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

This works except for one thing. I need an additional space before the "new
sentence" in the instances that it's added to a NULL cell. Unfortunately,
right this sec, I can't figure out where in the expression I need to add
another space.

Jarrod

Or

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field.
So
some of them need this "My sentence here." but some need " My sentence
here."

Jarrod

:

I have a sentence I'd like to add to the end of every field in one

of
my

tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field.
So
some of them need this "My sentence here" but some need " My sentence
here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[Ken Snell [MVP]]

Sorry.. my just sent post missed your point... here is an expression that
gives you two blank spaces:

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

This works except for one thing. I need an additional space before the "new
sentence" in the instances that it's added to a NULL cell. Unfortunately,
right this sec, I can't figure out where in the expression I need to add
another space.

Jarrod

Or

UpdateTo: IIf(Len([Field] & "")>0,[Field] & " ","") & "My sentence here."

--

Ken Snell
<MS ACCESS MVP>

UpdateTo: ([Field] + " ") & "My sentence here."


--

Ken Snell
<MS ACCESS MVP>

Just to be clear, I meant this:

"The thing, is that some records already have a sentence in this field.
So
some of them need this "My sentence here." but some need " My sentence
here."

Jarrod

:

I have a sentence I'd like to add to the end of every field in one

of
my

tables.

I know I can use an update query with something like:

Update To: [field] & "My sentence here."

The thing, is that some records already have a sentence in this field.
So
some of them need this "My sentence here" but some need " My sentence
here."

How can I account for this? With an Iif and Else?
How would I write this out and where would I write it(syntax?)?

Jarrod

 

[John Vinson]

ACCESS will not let you "keep" a leading blank space in a field's value;
it'll strip it off.

I think it trims trailing blanks, but leaves leading blanks alone,
Ken. At any rate I've had to put code in one of my databases to keep
users from typing " X123" and then complaining when it didn't match
"X123"!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Ken Snell [MVP]]

Hmmm.... I'll have to revisit this recollection -- it may be faulty. Thanks,
John.