If Statemet

[Macroman]

Totally new to MS Access but kind of finding my way around I tried something
simple and it is becomming a pain.

I created a simple table as follows

Field 1 = "This is Fred" , "This is George"
Field 2 = "Fred" , "Adam"
Field 3 = 1 , 2
Field 4 = 01/04/05 , 05/05/05

The comma separates the 2 rows of data I entered.

I then created a Query (all using the Design mode not quite comfortable with
SQL yet)
A query was simple enough , just dragged all the fields 1 to 4 down, then I
thought I try to create an extra field using the "Build" option on the
Criteria and wrote the syntax via "Built in Functions", the field name
became "Expr1: [TestMe]" the syntax I built as follows

IIf([TestTable]![Field 1] Like "*" & [TestTable]![Field 2] & "*",1,0)

What I was attempting to do was simply say that if Field 1 is LIKE value in
Field 2 then 1 else 0, what the damn thing is doing is giving me an "Enter
Parameter Value" dialog box, which is what I do not want , I expected to get
a value back in this new field I called "TestMe" with either a "0" or a "1"
for all my rows of data (all be it only 2 rows/records). However when I
enter a 1 in the dialog I get back record 1 and if I type 0 I get record 2
back, anything else I type I get nothing.

Help me to understand what I am doing wrong here.

many thanks

Macroman

[Jana]

Macroman:

I just created a table like yours and ran a query with your IIF
statement and it ran just fine. Sometimes, Access gets weird and
exiting the db and reopening it will sometimes fix that issue.

HTH,
Jana

[Jana]

Macroman:

Just realized what might be wrong!

The whole line should look exactly like this:
TestMe: IIf([TestTable]![Field 1] Like "*" & [TestTable]![Field 2] &
"*",1,0)

My guess is yours looks like this:
Expr1:[TestMe] IIf([TestTable]![Field 1] Like "*" & [TestTable]![Field
2] & "*",1,0)

Delete the Expr1, delete the brackets around TestMe, and put it BEFORE
the colon.

That should do it!

Jana

[Br]

Macroman wrote:

Totally new to MS Access but kind of finding my way around I tried
something simple and it is becomming a pain.

I created a simple table as follows

Field 1 = "This is Fred" , "This is George"
Why do this when you have their name in field 2?
Field 2 = "Fred" , "Adam"
Field 3 = 1 , 2
Field 4 = 01/04/05 , 05/05/05

The comma separates the 2 rows of data I entered.

I then created a Query (all using the Design mode not quite
comfortable with SQL yet)
A query was simple enough , just dragged all the fields 1 to 4 down,
then I thought I try to create an extra field using the "Build"
option on the Criteria and wrote the syntax via "Built in Functions",
the field name became "Expr1: [TestMe]" the syntax I built as follows

IIf([TestTable]![Field 1] Like "*" & [TestTable]![Field 2] & "*",1,0)

What I was attempting to do was simply say that if Field 1 is LIKE
value in Field 2 then 1 else 0,
Why? You should be deriving field1 from the value in field2.

ie. Don't store "This is Fred"... build the string ="This is " &
[Field2] when you need the text.
what the damn thing is doing is
giving me an "Enter Parameter Value" dialog box, which is what I do
not want , I expected to get a value back in this new field I called
"TestMe" with either a "0" or a "1" for all my rows of data (all be
it only 2 rows/records). However when I enter a 1 in the dialog I get
back record 1 and if I type 0 I get record 2 back, anything else I
type I get nothing.
Help me to understand what I am doing wrong here.
If you'd designed your tables correctly I suspect you wouldn't have to
be doing this to start with! :)
IIf([TestTable]![Field 1] Like "*" & [TestTable]![Field 2] & "*",1,0)

You need to put quotes around the resulting string after the LIKE:

Access needs to see something like:

[Field] Like "*value*"

What's you are passing is:

[Field] Like *value*

Access thinks *value* must be a parameter as it doesn't interpret it as
a string.

So it should be:

IIf([TestTable]![Field 1] Like "'*" & [TestTable]![Field 2] & "'*", 1,
0)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response