IF Statements

[Renae]

I am new to Access. I am trying to do an "IF" statement from the Query but
having trouble. From the discussions I have read, it appears I probably need
to do a "Select Case???" because there is to many variables in the "IF"
statement. I am not sure what a Select Case is.

Here is what I am attempting to do.

if (Age) = 1 & (Size) <= 20 then .05 else
if (Age) = 1 & (Size) <= 25 then .08 else
if (Age) = 1 & (Size) <= 30 then .09 else
if (Age) = 1 & (Size) <= 99 then 1 else
if (Age) = 2 & (Size) <= 5 then 0.00 else
if (Age) = 2 & (Size) <= 10 then .10 else
if (Age) = 2 & (Size) <= 15 then .20 else
if (Age) = 2 & (Size) <= 20 then .40 else
if (Age) = 2 & (Size) <= 25 then .70 else
if (Age) = 2 & (Size) <= 30 then .80 else
if (Age) = 2 & (Size) <= 99 then .90 else
if (Age) = 3 & (Size) <= 10 then .05 else
if (Age) = 3 & (Size) <= 15 then .15 else
if (Age) = 3 & (Size) <= 20 then .30 else
if (Age) = 3 & (Size) <= 25 then .60 else
if (Age) = 3 & (Size) <= 30 then .75 else
if (Age) = 3 & (Size) <= 99 then .85 else
if (Age) = 9 & (Size) <= 5 then 0.00 else
if (Age) = 9 & (Size) <= 10 then 0.00 else
if (Age) = 9 & (Size) <= 15 then .05 else
if (Age) = 9 & (Size) <= 20 then .15 else
if (Age) = 9 & (Size) <= 25 then .30 else
if (Age) = 9 & (Size) <= 30 then .50 else
if (Age) = 9 & (Size) <= 99 then .75 else

I want the calculations to show in a separate field and not replace any
fields.
Please help.
Thanks

 

[KARL DEWEY]

The way I prefer is using a translation table. Post your query SQL. Open in
design view, click on menu VIEW - SQL View, highlight all, copy, and paste in
a post.

 

[Renae]

you lost me at "post your query SQL"

The way I prefer is using a translation table. Post your query SQL. Open in
design view, click on menu VIEW - SQL View, highlight all, copy, and paste in
a post.

 

[KARL DEWEY]

Read the rest of my post to see how to do it.

 

[BruceM]

Just to be clear, "Open in design view" means to open the query in design
view. The next part of Karl's instruction (click View >> SQL on the menu
bar, etc.) is how you find, copy, and paste the SQL code.

 

[Renae]

I went into "query" changed it to SQL view and this is what I see, I am
following correctly so far?

SELECT DISTINCTROW PEECEDUMP0802C.ID, PEECEDUMP0802C.Yards,
PEECEDUMP0802C.Age, PEECEDUMP0802C.[New Bin], Count(*) AS [Count Of
PEECEDUMP0802C]
FROM PEECEDUMP0802C
GROUP BY PEECEDUMP0802C.ID, PEECEDUMP0802C.Yards, PEECEDUMP0802C.Age,
PEECEDUMP0802C.[New Bin];

 

[Renae]

I understand that part now.
Now, the IF statement is giving me an "operator missing" error message when
I pasted the IF statement in it.
any suggestions on how to correclty code the if statement below?

 

[KARL DEWEY]

I used your data to create the translation table named PEECE_Translation.
Age Size Result
1 20 0.05
1 25 0.08
1 30 0.09
1 99 1
2 5 0
2 10 0.1
2 15 0.2
2 20 0.4
2 25 0.7
2 30 0.8
2 99 0.9
3 10 0.05
3 15 0.15
3 20 0.3
3 25 0.6
3 30 0.75
3 99 0.85
9 5 0
9 10 0
9 15 0.05
9 20 0.15
9 25 0.3
9 30 0.5
9 99 0.75

Then I edited your query to this --
SELECT DISTINCTROW PEECEDUMP0802C.ID, PEECEDUMP0802C.Yards,
PEECEDUMP0802C.Age, PEECEDUMP0802C.[New Bin], Count(*) AS [Count Of
PEECEDUMP0802C], PEECEDUMP0802C.Size, PEECE_Translation.Result
FROM PEECEDUMP0802C, PEECE_Translation
WHERE (((PEECE_Translation.Age)=[PEECEDUMP0802C].[Age]) AND
((PEECE_Translation.Size)>=[PEECEDUMP0802C].[Size]))
GROUP BY PEECEDUMP0802C.ID, PEECEDUMP0802C.Yards, PEECEDUMP0802C.Age,
PEECEDUMP0802C.[New Bin], PEECEDUMP0802C.Size, PEECE_Translation.Result;

I made up some test data so it has not been fully tested.
--
KARL DEWEY
Build a little - Test a little

I went into "query" changed it to SQL view and this is what I see, I am
following correctly so far?

SELECT DISTINCTROW PEECEDUMP0802C.ID, PEECEDUMP0802C.Yards,
PEECEDUMP0802C.Age, PEECEDUMP0802C.[New Bin], Count(*) AS [Count Of
PEECEDUMP0802C]
FROM PEECEDUMP0802C
GROUP BY PEECEDUMP0802C.ID, PEECEDUMP0802C.Yards, PEECEDUMP0802C.Age,
PEECEDUMP0802C.[New Bin];

Read the rest of my post to see how to do it.

 

[BruceM]

I would go with Karl's suggestion about the translation table. However, I
will just mention that you would not use an ampersand, and you need to set
somethng equal to the value. Some ElseIf statements would help, but this is
really not the job for an If statement.

If Me.Age = 1 Then
If Me.Size <= 20 Then
[SomeFieldOrVariable] = .05
ElseIf (Size) <= 25 Then
[SomeFieldOrVariable] = .08
End If
ElseIf Me.Age = 2 Then
If Me.Size <= 5 Then
etc.
End If

I was just trying to clarify the instructions when I posted my brief
comments. What I have written here may help you with If statements in the
future, but again I do not think it is the best approach, or anything other
than about a fourth choice as a way to address the problem.

 

[Renae]

ok, thanks for all your help.

I would go with Karl's suggestion about the translation table. However, I
will just mention that you would not use an ampersand, and you need to set
somethng equal to the value. Some ElseIf statements would help, but this is
really not the job for an If statement.

If Me.Age = 1 Then
If Me.Size <= 20 Then
[SomeFieldOrVariable] = .05
ElseIf (Size) <= 25 Then
[SomeFieldOrVariable] = .08
End If
ElseIf Me.Age = 2 Then
If Me.Size <= 5 Then
etc.
End If

I was just trying to clarify the instructions when I posted my brief
comments. What I have written here may help you with If statements in the
future, but again I do not think it is the best approach, or anything other
than about a fourth choice as a way to address the problem.

I understand that part now.
Now, the IF statement is giving me an "operator missing" error message
when
I pasted the IF statement in it.
any suggestions on how to correclty code the if statement below?