IF Function Help

[goju]

Ok i know just one question a day. (Have searched)

I have a query return a result in one field if someone is 16 and under it returns
a 0 in the second field if someone is 16 and over it returns a -1

what i need is the query to report a J or S as its finding.

So what i thought of was.

So when in field one someone is 16 and under it retuns 0 then it sees this and

says something like " IF(0="J", IF(-1="S"))

not sure where to put the IF function or if it is correct.

 

[Mile-O]

If is a VBA Keyword - you can't use it in expressions. You can, howeverm, use the Immediate If function. It is IIf.

 

[neileg]

In Access, 0 is false and -1 is true (don't know why). So your IIF statement would look like this:

IIF([MyField], "S","J")

 

[goju]

cheers neil,

sorry newbie

would this be in the deafukt value or in the query criteria

 

[Brianwarnock]

Why not just code IIF([agefield]<16,"J","S") in the original query ?

Brian

 

[goju]

I have this in the original update query (under 16)

>DateAdd("yyyy",-16,Date())

And this in a second update query(Over16)

<=DateAdd("yyyy",-16,Date())

Two fields One junior and one senior, it retuns either -1 or 0 wich identifies junior or senior.

tried the above it just returns mismatch errors. never mind i will keep searching.

ta for your help

 

[ColinEssex]

I have just tested your DateAdd() functions in a Date-Of-Birth field criteria of a query and they work fine.

Col

 

[Mile-O]

Personally, I wouldn't use the DateAdd() function with the date of birth - there's scope for error.

I'd use a custom age calculation based on the date of birth to make sure it's exact and use the custom age field in my query as the calculation.

Public Function CalculateAge(ByVal dteDOB As Date, Optional SpecDate As Variant) As Integer
    
    On Error GoTo Err_CalculateAge
    
    Dim dteBase As Date
    Dim intCurrent As Date
    Dim intEstAge As Integer
    
    ' Determine if SpecDate parameter has been passed
    If IsMissing(SpecDate) Then
        ' If not, use current date
        dteBase = Date
    Else
        ' Otherwise use the SpecDate parameter
        dteBase = SpecDate
    End If
    
    ' Create an estimated age by getting the number of years
    ' between the date of birth and the secondary date
    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
    ' Create the exact age from evaluating the month and day based
    ' on the year
    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
    CalculateAge = intEstAge + (dteBase < intCurrent)
    
    Exit Function
    
Err_CalculateAge:
    CalculateAge = -1
    
End Function

 

[goju]

Colin,

but i need them to return J for junior and S for senior, at the mo the return -1 and 0.

did you get them to return J and S ??