If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF statement
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 |
#2
|
|||
|
|||
IF statement
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 Florida Analyst wrote: 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 |
#3
|
|||
|
|||
IF statement
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 ON T1.Age = T2.MinAge AND T1.Age T2.MaxAge Florida Analyst wrote: 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 -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
IF statement
Florida Analyst wrote:
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" |
#5
|
|||
|
|||
IF statement
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). "John Spencer" wrote: 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 Florida Analyst wrote: 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 |
#6
|
|||
|
|||
IF statement
Thank you, great reference!
"Bob Barrows [MVP]" wrote: Florida Analyst wrote: 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" |
#7
|
|||
|
|||
IF statement
Thank you, Great reference!
"Dale_Fye via AccessMonster.com" wrote: 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 ON T1.Age = T2.MinAge AND T1.Age T2.MaxAge Florida Analyst wrote: 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 -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
IF statement
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 Florida Analyst wrote: 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). "John Spencer" wrote: 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 Florida Analyst wrote: 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 |
Thread Tools | |
Display Modes | |
|
|