IF statement

[Florida Analyst]

I'm not the best with SQL for Access however I want to create an IF THEN
Scenario, this is how I would write it in the SQL I know (I mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

I tried to use Visual Basic and I created a SELECT CASE, however I believe
this can only be used in a form (I'm probably wrong) and all I need is a
query with this column added to it. Just in case I can use it here is the
code:

Public Function agecate(strSize As String) As String
Select Case strSize
Case 1 To 4
agecate = "1-4"
Case 5 To 9
agecate = "5-9"
Case 10 To 14
agecate = "10-14"
Case Else
agecate = "Don't Know"
End Select
End Function

 

[John Spencer]

In a query you can use IIF.

IIF(Age Between 1 and 4, "1 to 4",IIF(Age between 5 and 9, "5 to 9",IIF(Age
Between 10 and 14, "10 to 14", "Not Important")))

If Age is a text field, then add text delimiter (quote marks" around the values.

You can also use your VBA function in the query, although you need to define
the argument as a variant or a number type if your Age field is a number field.

Public Function AgeCate(iAge) As String

Select Case iAge
Case 1 To 4
AgeCate = "1-4"
Case 5 To 9
AgeCate = "5-9"
Case 10 To 14
AgeCate = "10-14"
Case Else
AgeCate = "Don't Know"
End Select

End Function

Call it like
Field: AgeGroup: AgeCate([Age])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

First,

First, it is not advisable to store the [Age] of anything in a database,
since it changes every day. It is much better to compute the [Age] field
whenever you need it.

You could use the IIF( ) function in your query, but when I have this type of
situation, I generally like to add another table to my database (we'll call
it tbl_Age_Bands) with fields like:

MinAge MaxAge Comment
-1 1 Not Important
1 5 1 through 4
5 10 5 through 9
10 15 10 through 14
15 110 Not Important

Then you can write your query to look something like the following. This
will only give you records where the Age field matches the ranges in the
second table, but can be written in the query designer.

SELECT T1.[Age], T2.Comment
FROM yourTable as T1, tbl_Age_Bands as T2
WHERE T1.[Age] >= T2.MinAge
AND T1.[Age] < T2.MaxAge

Another way to do it would be to use a JOIN clause. This will give you all
the values from T1 and would allow you to identify if there were values in T1
that are not covered by the age ranges in T2. The down side of this method
is the you will have to build this SQL clause in the SQL view, because you
cannot represent non-equi joins in the query designer.

SELECT T1.Age, T2.Comment
FROM yourTable as T1
LEFT JOIN tbl_Age_Bands as T2

I'm not the best with SQL for Access however I want to create an IF THEN
Scenario, this is how I would write it in the SQL I know (I mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

I tried to use Visual Basic and I created a SELECT CASE, however I believe
this can only be used in a form (I'm probably wrong) and all I need is a
query with this column added to it. Just in case I can use it here is the
code:

Public Function agecate(strSize As String) As String
Select Case strSize
Case 1 To 4
agecate = "1-4"
Case 5 To 9
agecate = "5-9"
Case 10 To 14
agecate = "10-14"
Case Else
agecate = "Don't Know"
End Select
End Function

 

[Bob Barrows [MVP]]

I'm not the best with SQL for Access however I want to create an IF
THEN Scenario, this is how I would write it in the SQL I know (I
mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

The equivalent in JetSQL is the "immediate if" statement: iif().
It takes 3 arguments: the test expression, the result if true, and the
result if false. Since the result arguments can also contain expressions,
the iif() statements can be nested:

iif(expr1, result1,iif(..., ..., iif(..., ..., "Not Important")))

To get you started:

iif([Age] Between 1 and 4,"1 through 4", iif(etc.))

Give it a try and if you need further help let us know.

 

[Florida Analyst]

OK great, now you mention having to defining the argument,

could you give an example? The Age field is a numeric field ( i have a
feeling you explained this at the bottom).

In a query you can use IIF.

IIF(Age Between 1 and 4, "1 to 4",IIF(Age between 5 and 9, "5 to 9",IIF(Age
Between 10 and 14, "10 to 14", "Not Important")))

If Age is a text field, then add text delimiter (quote marks" around the values.

You can also use your VBA function in the query, although you need to define
the argument as a variant or a number type if your Age field is a number field.

Public Function AgeCate(iAge) As String

Select Case iAge
Case 1 To 4
AgeCate = "1-4"
Case 5 To 9
AgeCate = "5-9"
Case 10 To 14
AgeCate = "10-14"
Case Else
AgeCate = "Don't Know"
End Select

End Function

Call it like
Field: AgeGroup: AgeCate([Age])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm not the best with SQL for Access however I want to create an IF THEN
Scenario, this is how I would write it in the SQL I know (I mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

I tried to use Visual Basic and I created a SELECT CASE, however I believe
this can only be used in a form (I'm probably wrong) and all I need is a
query with this column added to it. Just in case I can use it here is the
code:

Public Function agecate(strSize As String) As String
Select Case strSize
Case 1 To 4
agecate = "1-4"
Case 5 To 9
agecate = "5-9"
Case 10 To 14
agecate = "10-14"
Case Else
agecate = "Don't Know"
End Select
End Function

 

[Florida Analyst]

Thank you, great reference!

I'm not the best with SQL for Access however I want to create an IF
THEN Scenario, this is how I would write it in the SQL I know (I
mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

The equivalent in JetSQL is the "immediate if" statement: iif().
It takes 3 arguments: the test expression, the result if true, and the
result if false. Since the result arguments can also contain expressions,
the iif() statements can be nested:

iif(expr1, result1,iif(..., ..., iif(..., ..., "Not Important")))

To get you started:

iif([Age] Between 1 and 4,"1 through 4", iif(etc.))

Give it a try and if you need further help let us know.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Florida Analyst]

Thank you, Great reference!

First,

First, it is not advisable to store the [Age] of anything in a database,
since it changes every day. It is much better to compute the [Age] field
whenever you need it.

You could use the IIF( ) function in your query, but when I have this type of
situation, I generally like to add another table to my database (we'll call
it tbl_Age_Bands) with fields like:

MinAge MaxAge Comment
-1 1 Not Important
1 5 1 through 4
5 10 5 through 9
10 15 10 through 14
15 110 Not Important

Then you can write your query to look something like the following. This
will only give you records where the Age field matches the ranges in the
second table, but can be written in the query designer.

SELECT T1.[Age], T2.Comment
FROM yourTable as T1, tbl_Age_Bands as T2
WHERE T1.[Age] >= T2.MinAge
AND T1.[Age] < T2.MaxAge

Another way to do it would be to use a JOIN clause. This will give you all
the values from T1 and would allow you to identify if there were values in T1
that are not covered by the age ranges in T2. The down side of this method
is the you will have to build this SQL clause in the SQL view, because you
cannot represent non-equi joins in the query designer.

SELECT T1.Age, T2.Comment
FROM yourTable as T1
LEFT JOIN tbl_Age_Bands as T2

I'm not the best with SQL for Access however I want to create an IF THEN
Scenario, this is how I would write it in the SQL I know (I mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

I tried to use Visual Basic and I created a SELECT CASE, however I believe
this can only be used in a form (I'm probably wrong) and all I need is a
query with this column added to it. Just in case I can use it here is the
code:

Public Function agecate(strSize As String) As String
Select Case strSize
Case 1 To 4
agecate = "1-4"
Case 5 To 9
agecate = "5-9"
Case 10 To 14
agecate = "10-14"
Case Else
agecate = "Don't Know"
End Select
End Function

 

[John Spencer]

In the function you defined the argument as
StrSize as String

That would work if the Age was a string field and was never null. If Age is
numeric then your function would error.

I didn't define iAge as any specific type, so it defaults to Variant. Variant
allows you to pass in nulls, strings, dates, numbers, etc.

If I were writing the function I would check what was passed in and if it was
not numeric (or could not be treated that way) I would trap that

If IsNumeric(iAge) = False Then
AgeCate = "Unknown"
Else
SELECT Case ...

End If



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

OK great, now you mention having to defining the argument,

could you give an example? The Age field is a numeric field ( i have a
feeling you explained this at the bottom).

In a query you can use IIF.

IIF(Age Between 1 and 4, "1 to 4",IIF(Age between 5 and 9, "5 to 9",IIF(Age
Between 10 and 14, "10 to 14", "Not Important")))

If Age is a text field, then add text delimiter (quote marks" around the values.

You can also use your VBA function in the query, although you need to define
the argument as a variant or a number type if your Age field is a number field.

Public Function AgeCate(iAge) As String

Select Case iAge
Case 1 To 4
AgeCate = "1-4"
Case 5 To 9
AgeCate = "5-9"
Case 10 To 14
AgeCate = "10-14"
Case Else
AgeCate = "Don't Know"
End Select

End Function

Call it like
Field: AgeGroup: AgeCate([Age])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm not the best with SQL for Access however I want to create an IF THEN
Scenario, this is how I would write it in the SQL I know (I mostly use SAS):

(CASE WHEN [Age] is between 1 and 4 then "1 through 4"
WHEN [Age] is between 5 and 9 then "5 through 9"
WHEN [Age] is between 10 and 14 then "10 through 14"
ELSE "Not Important"
END) as [Age Category]

I tried to use Visual Basic and I created a SELECT CASE, however I believe
this can only be used in a form (I'm probably wrong) and all I need is a
query with this column added to it. Just in case I can use it here is the
code:

Public Function agecate(strSize As String) As String
Select Case strSize
Case 1 To 4
agecate = "1-4"
Case 5 To 9
agecate = "5-9"
Case 10 To 14
agecate = "10-14"
Case Else
agecate = "Don't Know"
End Select
End Function