Iif and Wildcard

[MB]

I've written before and thought I had the answer, but I’m still lost. I have
a points for each of our pipe material:

Cast Iron – Lined = 6
Cast Iron – Unlined = 6
Ductile = 4
PVC = 2
HDPE = 2

My query name is qryScores. I tried the following but no luck.

MaterialScore=IIf([PipeMaterial] Like "Cast*", 6,
IIf([PipeMaterial]="Ductile",4,2))

What am I doing wrong?

I'm desperate!

 

[Douglas J. Steele]

Try:

MaterialScore=IIf(Left([PipeMaterial], 4) = "Cast", 6,
IIf([PipeMaterial]="Ductile",4,2))

 

[Lord Kelvan]

MaterialScore: IIf([PipeMaterial] Like "Cast*", "6",
IIf([PipeMaterial]="Ductile","4","2"))

try that as an expression in your query

 

[MB]

I get 2 for all. (Sigh!)
--
MB

Try:

MaterialScore=IIf(Left([PipeMaterial], 4) = "Cast", 6,
IIf([PipeMaterial]="Ductile",4,2))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've written before and thought I had the answer, but I'm still lost. I
have
a points for each of our pipe material:

Cast Iron - Lined = 6
Cast Iron - Unlined = 6
Ductile = 4
PVC = 2
HDPE = 2

My query name is qryScores. I tried the following but no luck.

MaterialScore=IIf([PipeMaterial] Like "Cast*", 6,
IIf([PipeMaterial]="Ductile",4,2))

What am I doing wrong?

I'm desperate!

 

[MB]

I get a result of 2 for all again.

 

[Lord Kelvan]

can you show us a sample of the data from the qryscores just copy and
paste it into the group as a reply for us to see

Regards
Kelvan

 

[John W. Vinson]

I've written before and thought I had the answer, but I’m still lost. I have
a points for each of our pipe material:

Cast Iron – Lined = 6
Cast Iron – Unlined = 6
Ductile = 4
PVC = 2
HDPE = 2

My query name is qryScores. I tried the following but no luck.

MaterialScore=IIf([PipeMaterial] Like "Cast*", 6,
IIf([PipeMaterial]="Ductile",4,2))

What am I doing wrong?

I'm desperate!

Is the [Pipe Material] field perchance a Lookup Field? If so, it *APPEARS* to
contain the text "Ductile" but it actually does not; it will contain a
concealed numeric ID.

 

[MB]

Sorry it took so long for me to get back to you (it was end of day for me).

Here's are the results of the data after qryscores is run:

PipeMaterial MaterialScore
Cast Iron - lined 2
Cast Iron - lined 2
Ductile 2
Cast Iron - lined 2
Ductile 2
Cast Iron - lined 2
Ductile 2
Cast Iron - lined 2
HDPE 2
Cast Iron - lined 2
Cast Iron - lined 2
Cast Iron - lined 2
PVC 2
Cast Iron - lined 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Cast Iron - lined 2
Ductile 2
Ductile 2
PVC 2
PVC 2
Cast Iron - lined 2
Cast Iron - lined 2
Ductile 2
Ductile 2
Cast Iron - lined 2
Cast Iron - lined 2
Cast Iron - lined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
PVC 2
Cast Iron - lined 2
PVC 2
Cast Iron - lined 2
Ductile 2
PVC 2
PVC 2
Cast Iron - lined 2
Cast Iron - lined 2
Cast Iron - lined 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
PVC 2
Ductile 2
PVC 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Ductile 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
Cast Iron - unlined 2
HDPE 2
Cast Iron - unlined 2
Ductile 2
Cast Iron - unlined 2
2

 

[MB]

Hi John,

Sorry it took so long to get back to you. I checked my table
(tblPipeMaterial) and it is not a lookup, just text. I have only two fields:
PipeMaterialID and PipeMaterial. I would like the query to give the score
because I need to add that result to other scores (water pressure, age of
pipe, etc.). Those queries are working I'm guessing because it's numbers.
For example my table for pressure is: tblPressure with fields of PressureID
and Pressure (a number field). The query gives the pressure a score as
follows:

PressureScore:
IIf([Pressure]<=80,"0",IIf([Pressure]<=100,"2",IIf([Pressure]<=120,"4",IIf([Pressure]<=140,"6",IIf([Pressure]>=141,"8")))))

and it works great. Of course the data is entered into a form. I just
can't get the Pipe Material to do the same.

Please help. I've spent so much time trying to figure this out and written
several times. It's getting crunch time now. I really appreciate your help.

Thank you.
--
MB

I've written before and thought I had the answer, but I’m still lost. I have
a points for each of our pipe material:

Cast Iron – Lined = 6
Cast Iron – Unlined = 6
Ductile = 4
PVC = 2
HDPE = 2

My query name is qryScores. I tried the following but no luck.

MaterialScore=IIf([PipeMaterial] Like "Cast*", 6,
IIf([PipeMaterial]="Ductile",4,2))

What am I doing wrong?

I'm desperate!

Is the [Pipe Material] field perchance a Lookup Field? If so, it *APPEARS* to
contain the text "Ductile" but it actually does not; it will contain a
concealed numeric ID.

 

[MB]

I DID IT! I DID IT! FORGET EARLIER POSTS!

Thank you John, you hit it right on. I didn't check my main table and there
it was a look-up field as a combo box. I re-did it as a list box and entered
my own values rather than the row source being a table.

Thank you ! Thank you! Thank you! I LOVE YOU GUYS!

Mary B
--

I've written before and thought I had the answer, but I’m still lost. I have
a points for each of our pipe material:

Cast Iron – Lined = 6
Cast Iron – Unlined = 6
Ductile = 4
PVC = 2
HDPE = 2

My query name is qryScores. I tried the following but no luck.

MaterialScore=IIf([PipeMaterial] Like "Cast*", 6,
IIf([PipeMaterial]="Ductile",4,2))

What am I doing wrong?

I'm desperate!

Is the [Pipe Material] field perchance a Lookup Field? If so, it *APPEARS* to
contain the text "Ductile" but it actually does not; it will contain a
concealed numeric ID.

 

[John W. Vinson]

I DID IT! I DID IT! FORGET EARLIER POSTS!

Thank you John, you hit it right on. I didn't check my main table and there
it was a look-up field as a combo box. I re-did it as a list box and entered
my own values rather than the row source being a table.

Well... I'm glad that this got you out of your bind, but it's not really a
very good long term solution! Any time you add a new material you'll need to
manually open the form in design view and go in and edit a long list of values
in the row source... a major PITA.

What you might want to consider instead is adding a new field to your
Materials table containing the points for that material. Then you would be
able to retrieve the points with no code, no IIF's, nothing fancy, just a
simple query join.