Help with IIf Function

[Rayhogan]

Hi
I am new to this forum and would be pleased if you could advise why the following query will not give me the correct result.
"SELECT tblMember.MemFirstName, tblMember.MemSurname, IIf(IsNull([tblMember]![MemDOB])," No Record",Year(Now()-[tblMember]![MemDOB])) AS Age
FROM tblMember;

Thanking you
Rayhogan

 

[Minty]

This isn't quite correct

IIf(IsNull([tblMember]![MemDOB])," No Record",Year(Now()-[tblMember]![MemDOB])) AS Age

Try

IIf([tblMember].[MemDOB] Is Null," No Record",Year(Now()-[tblMember].[MemDOB])) AS Age

IsNull() is VBA syntax your query wants SQL syntax. Also not sure about the ! in your field references - I've used the more normal . separator

 

[Rayhogan]

Thanks Minty.
No this returns a Year value - for example 8/5/1955 is returned as 1960. The No Records is working OK and I should note that I am using Irish Date format

Thanking you
Rayhogan

 

[jdraw]

Try Date instead of Now. I would also try using the American date format mm/dd/yy

Here is a function to determine Age

'---------------------------------------------------------------------------------------
' Procedure : Age
' Author    : Jack (from awf)
' Date      : 06-09-2012
' Purpose   : This routine determines the Age of a Person given their DOB.
' It accounts for the birthday this year (whether passed or not). A second parameter
' Specdate allows you to work from a different Date than today's date.
'If SpecDate is missing, the routine defaults to today's date.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
    Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
10  On Error GoTo Age_Error

20  If IsMissing(SpecDate) Then
30      dteBase = Date
40  Else
50      dteBase = SpecDate
60  End If
70  intEstAge = DateDiff("yyyy", dteDOB, dteBase)
80  intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
90  Age = intEstAge + (dteBase < intCurrent)

100 On Error GoTo 0
110 Exit Function

Age_Error:

120 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Age of Module AWF_Related"
End Function

 

[Rayhogan]

Thanks Jdraw.
A few point;
(1) I have tried Date() and it returns the same result as Now().
(2) I would be reluctant to convert all date formats to mm/dd/yy at this side of the pond. Could it be that I have some setting incorrect as it appears that my results vary in value i.e. different dates result in varying year difference values?
(3) Not up to Function Module level yet and will take some consideration at my end, but will attempt over the next few days.

Once again thanking you
Rayhogan

 

[jdraw]

Can you show us some specific table and query data?
I am in Canada -we use DD/MM/YY also.
Access is expecting US date format.

I realize hindsight is 20/20, but did you test some Dates and Age calculations before
reaching the " I would be reluctant to convert all date formats"?

You need to do some testing/analysis to determine what Access wants and what workaround "he/she" will accept.

Here is a reference that may help.

 

[Rayhogan]

Jdraw
I now attach some data and I have added MemDOB field from table tblMember

MemFirstName MemSurname Age MemDOB
Michael Butler 1960 08-05-1955
John Cox No Record
Paddy Murray 1968 04-09-1947
Danny O'Connell 1952 15-03-1963

The following is sql for this query;

SELECT tblMember.MemFirstName, tblMember.MemSurname, IIf([tblMember].[MemDOB] Is Null," No Record",Year(Date()-[tblMember].[MemDOB])) AS Age, tblMember.MemDOB
FROM tblMember;

Hope this makes sense and thanking you
Rayhogan

 

[jdraw]

Hmm.
How is memDOB defined in tblMember? What data type?

When I define DOB as a Date datatype and input #15-03-1963#, Access immediately changes it to 3/15/1963.
When I define DOB as a String datatype and input #15-03-1963#, Access does not change it.

Here is a small test

Sub testAgeAWF()
    Dim dob As String
    dob = "15-3-1963"
    Debug.Print CDate(Format(dob, "DD-MM-YYYY"))
    Debug.Print "age is " & DateDiff("YYYY", CDate(Format(dob, "DD-MM-YYYY")), Date)
End Sub

Gives
15/03/1963 <---note dd/mm/yyyyy order -----I think this is because of my regional settings.
age is 52

When I use the Age function in the immediate window, it does not convert the day/month to US format
?Age(#15-3-1963#)
52

CDate() changes a string to a Date
Format changes the format of a string, and returns a string.

Try the routine at your end and see what results.

 

[Rayhogan]

Thanks Jdraw.

(1) MemDOB in tblMember is datatype Date/Time.
(2) ran your code and get:
testAgeAWF
15-03-1963
age is 52

Once again, thanking you
Rayhogan

 

[Rayhogan]

OK - I have decided to allocate default date (#01/01/1900#) to all blank fields and IIF function seems to work.
Thanking all for assistance
Rayhogan