Weird results (I think) for IIF

  • Thread starter Thread starter stainless
  • Start date Start date
S

stainless

Access 2003

I have a table with fields and columns as follows:

DIED DEATH DATE
------- ---------------------
1926(31.10.) null
null 1 Aug 1871
1857(1.4.) 1 Apr 1857
no details null
null null
1857(1.4.) *

I need to create a formatted DAY, MONTH and YEAR column for each with
the following rules:

if DIED starts with a year
DAY = day in DIED date
MONTH = month (3 alpha) in DIED date
YEAR = year in DIED date
else
if DEATH DATE is null
DAY = ""
MONTH = ""
YEAR = ""
else
IF DEATH DATE is *
DAY = ""
MONTH = ""
YEAR = ""
else
DAY = day in DEATH DATE
MONTH = month (3 alpha) in DEATH DATE
YEAR = year in DEATH DATE

Thus, I would want the following results:

DIED DEATH DATE DAY MONTH YEAR
------- --------------------- ------
----------- ---------
1926(31.10.) null 31 Oct
1926
null 1 Aug 1871 1 Aug
1871
1857(1.4.) 1 Apr 1857 1 Apr
1857
no details null
null null
1857(1.3.) * 1
Mar 1857
null *

I tried the following (probably overcomplex) IIF statement for the DAY
value:

IIf(Isnumeric(Left(DIED,4))=True,DAY(replace(replace(replace(right
(DIED,Len(DIED)-5),".)",""),")",""),".","/") & "/" & Left(DIED,4)),IIf
([DEATH DATE]='*',"",IIf([DEATH DATE] Is Null,"",DAY([DEATH DATE]))))

The first part simply reformats the DIED field into dd/mm/yyyy format.

It all worked fine for values in DIED and DEATH DATE but produced an
error when both were null

The reason I find it strange is that when I replaced the DAY([DEATH
DATE]) part with just a constant for debugging ("X"), the DAY field
was empty and this is really conusing as it has stopped the error but
not output the X character.

Any help welcome

Cheers

Mark
 
Replace(Replace(Replace([Died],".)",""),"(","-")),".","-")

Instead of trying to use a complex IIF statement, I would write a function to
return a date based on the two fields. And use the Year, Month, and Day
functions to get the date parts of the returned value.

The following is UNTESTED VBA code that you could paste into a module and use
the result to populate a field or just get the parts of as needed.

Public Function fReturnDate(fDied As Variant, fDeathDate As Variant)
Dim vReturn As Variant
Dim strDied As String

If Len(fDied & "") = 0 And IsDate(fDeathDate) = False Then
vReturn = Null
Else
strDied = Replace( _
Replace( _
Replace(strDied & "", ".)", ""), "(", "-"), ".", "-")

If IsDate(strDied) Then
vReturn = CDate(strDied)
ElseIf IsDate(fDeathDate) Then
vReturn = CDate(strDied)
Else
vReturn = Null
End If

End If

fReturnDate = vReturn

End Function

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

I have a table with fields and columns as follows:

DIED DEATH DATE
------- ---------------------
1926(31.10.) null
null 1 Aug 1871
1857(1.4.) 1 Apr 1857
no details null
null null
1857(1.4.) *

I need to create a formatted DAY, MONTH and YEAR column for each with
the following rules:

if DIED starts with a year
DAY = day in DIED date
MONTH = month (3 alpha) in DIED date
YEAR = year in DIED date
else
if DEATH DATE is null
DAY = ""
MONTH = ""
YEAR = ""
else
IF DEATH DATE is *
DAY = ""
MONTH = ""
YEAR = ""
else
DAY = day in DEATH DATE
MONTH = month (3 alpha) in DEATH DATE
YEAR = year in DEATH DATE

Thus, I would want the following results:

DIED DEATH DATE DAY MONTH YEAR
------- --------------------- ------
----------- ---------
1926(31.10.) null 31 Oct
1926
null 1 Aug 1871 1 Aug
1871
1857(1.4.) 1 Apr 1857 1 Apr
1857
no details null
null null
1857(1.3.) * 1
Mar 1857
null *

I tried the following (probably overcomplex) IIF statement for the DAY
value:

IIf(Isnumeric(Left(DIED,4))=True,DAY(replace(replace(replace(right
(DIED,Len(DIED)-5),".)",""),")",""),".","/") & "/" & Left(DIED,4)),IIf
([DEATH DATE]='*',"",IIf([DEATH DATE] Is Null,"",DAY([DEATH DATE]))))

The first part simply reformats the DIED field into dd/mm/yyyy format.

It all worked fine for values in DIED and DEATH DATE but produced an
error when both were null

The reason I find it strange is that when I replaced the DAY([DEATH
DATE]) part with just a constant for debugging ("X"), the DAY field
was empty and this is really conusing as it has stopped the error but
not output the X character.

Any help welcome

Cheers

Mark
Click to expand...
 
Thanks for the reply

However, much as I have worked with VB elsewhere, I have never done
this in Access 2003 and am not sure where VB functions are created
(and indeed, once created, how they are called, although I assume that
bit is pretty straightforward, given examples of calls I have found on
the web).

Where and how do I create such a VB function within Access 2003?

Cheers

Mark
 
Where and how do I create such a VB function within Access 2003?
Click to expand...

Use the Modules tab. Create a new module and copy and paste (or type) the VBA
code.

Just be sure that you do NOT save the Module with the same name as the
function within that module - modules, subs and functions all share the same
"namespace" and you'll get an error. Save the module as "Utilities" or
"basReturnDate" or some other distinct name.
 
Open a VBA module
Paste in the code
Save the module with a name other than the name of the function

In your query, you would have a calculated fields like

Field: TheYear: Year(fReturnDate([Died],[Death Date]))

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