IIF & replace question

[Choli]

Hi Folks,

I had two questions regarding a couple of queries I am trying to create.

1) I have a field call TBR which is a yes/no. What I am trying to have it
is, if another field in the same table has a certain phrase within the field
(can be at the beginning, middle, end & have other words also), the TBR box
will show a check mark.

This is what I have

=IIF([Field to Search for phrase", "Y", "N")

I might have too many comma's listed. When I tried it earlier I couldn't
get it to work, it was almost that I needed to put the word like (to use a
wildcard), but I am not sure where I would put that

My 2nd question is

I am trying to create an update query using a "replace" statement. However
the original data has ( ) around words, which I think is causing it not to
work right.

Here is what I have:

replace ([Field to search for], "This is the Name_Hi (Employee)", "This is
the Replacement Text_Hi (Manager))

The 2 )) at the end, one is to put a ) around the word & the other is to
close out the query

I also tried replace ([field to search for]," this is the name_hi*","This is
the replacement text" & wasn't able to get it to work either

Any help would be much appreciated on what I am missing or if I am using the
wrong commands..

Thanks in Advance

 

[Duane Hookom]

If you want to create an expression that returns true/yes/-1 if a particular
value is included in a field, you can try something like:
=Instr([Field To Search],"text to search for")>0

You are missing a closing quote in this and there is a space after "replace":
replace ([Field to search for], "This is the Name_Hi (Employee)", "This is
the Replacement Text_Hi (Manager))
Try:
Replace([Field to search for], "This is the Name_Hi (Employee)", "This is
the Replacement Text_Hi (Manager)")

 

[Choli]

Hi Duane,

Thanks for the response. The repl ace statement worked, howeverI couldn't
get the instr statement to work. I tried a select & update query. On the
query, I brought down both the field I want to update, and th field to look
for the data. I put the instr on the update to line of the field I am trying
to update.

Also is the "phrase to look for" just looking for it at the beginning & if
it doesn't see it, it skips it or is it looking thru the whole field on the
record

Thnaks

 

[Duane Hookom]

It seemed to me that you only wanted to display the result in your first
question. I don't know where the "select & update query". Maybe your should
try to be a little more explicit with what you have and what you want to do.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thanks for the response. The repl ace statement worked, howeverI couldn't
get the instr statement to work. I tried a select & update query. On the
query, I brought down both the field I want to update, and th field to look
for the data. I put the instr on the update to line of the field I am trying
to update.

Also is the "phrase to look for" just looking for it at the beginning & if
it doesn't see it, it skips it or is it looking thru the whole field on the
record

Thnaks

If you want to create an expression that returns true/yes/-1 if a particular
value is included in a field, you can try something like:
=Instr([Field To Search],"text to search for")>0

You are missing a closing quote in this and there is a space after "replace":
replace ([Field to search for], "This is the Name_Hi (Employee)", "This is
the Replacement Text_Hi (Manager))
Try:
Replace([Field to search for], "This is the Name_Hi (Employee)", "This is
the Replacement Text_Hi (Manager)")

 

[Choli]

What I am trying to do is update a certain field in a query based upon, if
another field has a certain phrase. For example, I have two field (Field 1 &
field 2). So if in the field 2, if it has the phrase "chocolate ice cream"
then field 1 would say Yes & if field 2, didn't have the phrase, then field 1
would say no. However field 2 can have other words, before & after, & can
also have , + / etc

so for example

Field 1 Field 2

Yes Strawberry, chocolate ice cream
Yes Chocolate/Vanilla Ice Cream
Yes Rocky Road+Vanilla, Chocolate Ice Cream
No Rocky Road
No Chocolate
No Vanilla/Strawberry


On my update query, on the "update to" of the field to update I had the
following

iif ([Field 2] "Chocolate Ice Cream" , "Yes", "No"


Overall what I am trying to do is create a pivot form based upon the records
only in Field 1, but not sure how I would accomplish this w/o a query.

Thanks

 

[Duane Hookom]

Is Field 1 a text field or a yes/no field? Yes/No fields actually store -1
for true/yes values and 0 for False/No values.

If the field is a Yes/No field you can use something like:

UPDATE tblNoNameGiven
SET [Field 1] = Instr([Field 2],"Chocolate Ice Cream")>0

I can't believe
iif ([Field 2] "Chocolate Ice Cream" , "Yes", "No"
would come near working since there is a space after the iif and there is no
closing ). If the field to update is yes/no the "Yes" and "No" might work but
should be replace with -1 and 0.

--
Duane Hookom
Microsoft Access MVP

What I am trying to do is update a certain field in a query based upon, if
another field has a certain phrase. For example, I have two field (Field 1 &
field 2). So if in the field 2, if it has the phrase "chocolate ice cream"
then field 1 would say Yes & if field 2, didn't have the phrase, then field 1
would say no. However field 2 can have other words, before & after, & can
also have , + / etc

so for example

Field 1 Field 2

Yes Strawberry, chocolate ice cream
Yes Chocolate/Vanilla Ice Cream
Yes Rocky Road+Vanilla, Chocolate Ice Cream
No Rocky Road
No Chocolate
No Vanilla/Strawberry


On my update query, on the "update to" of the field to update I had the
following

iif ([Field 2] "Chocolate Ice Cream" , "Yes", "No"


Overall what I am trying to do is create a pivot form based upon the records
only in Field 1, but not sure how I would accomplish this w/o a query.

Thanks

It seemed to me that you only wanted to display the result in your first
question. I don't know where the "select & update query". Maybe your should
try to be a little more explicit with what you have and what you want to do.

 

[Marshall Barton]

What I am trying to do is update a certain field in a query based upon, if
another field has a certain phrase. For example, I have two field (Field 1 &
field 2). So if in the field 2, if it has the phrase "chocolate ice cream"
then field 1 would say Yes & if field 2, didn't have the phrase, then field 1
would say no. However field 2 can have other words, before & after, & can
also have , + / etc

so for example

Field 1 Field 2

Yes Chocolate/Vanilla Ice Cream

To get "Yes" for that example, you can use:

IIf([Field 2] Like "*Chocolate*" And [Field 2] Like "*Ice
Cream*" , "Yes", "No")

If Field1 is a Yes/No field (not a Text field), then all you
need is:

[Field 2] Like "*Chocolate*" And [Field 2] Like "*Ice
Cream*"