Re: IF statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • Re: Basic Question: How do you check to see if gets is a number?
    ... Adam Bourg wrote: ... |# Basic Program to ask for a persons age and out put that in months ... First of all, in ruby you never ... the to_s method of any object will return a string which ruby will always ...
    (comp.lang.ruby)
  • Re: n-Tier/Layer with NULL and persistence
    ... Larry if what you need to check the NULL values in he DB use ... Public MustOverride Property Persist(ByVal key As String) As Object ... the database to the screen and back? ... Public Property Age() As Integer ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Urgent question w/ boxing... please help
    ... private string address; ... Here's a sample of a Type being passed as a parameter into the map: ... public int Age ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Parsing multi-line text
    ... Chunk 01 ... Description: "Some other string" ... Age: 37 ... 03 NAME: Carol -> 32 ...
    (comp.lang.perl.misc)
  • Re: IF statement
    ... 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. ... AgeCate = "Don't Know" ... Public Function agecate(strSize As String) As String ...
    (microsoft.public.access.queries)