please help with IIF statement on access query

[tnguyen315]

Hello all,
I'm using access 2010, I would like to compare 2 dates to get the status...

Date1, Date1_Inserted, Date2, Date2_Inserted.

If Date1 = blank and Date2= blank then group1
If Date1 > Date2 then group1
If Date1< Date2 then group2
If Date1 <> Blank and Date2 = blank then group1
If Date1= Blank and Date2 <> blank then group2
If Date1 = Date2 then need to compare Date1_Inserted and Date2_Inserted...if Date1_Inserted > Date2_Inserted then group1
If Date1 = Date2 then need to compare Date1_Inserted and Date2_Inserted...if Date1_Inserted < Date2_Inserted then group2

I would like to compare Date1 and Date2 and get result like this...
Below is the result

Date1 Date1_Inserted Date2 Date2_Inserted Result
12/17/2011 12/17/2011 8:54:34 AM 12/17/2011 12/17/2011 8:43:04 AM Group1
9/28/2011 9/28/2011 4:40:14 PM 10/3/2011 10/4/2011 11:48:23 AM Group2
9/23/2011 9/23/2011 11:33:40 AM 9/27/2011 9/28/2011 10:43:16 AM Group2
Group1
9/11/2011 9/7/2011 11:44:13 AM Group1
11/4/2011 11/4/2011 12:27:05 PM Group2

Please help, I'm very appreciated.
I'm trying to work on it but I couldn't get any right results.
Thank you very much.

12/28/2011 12/28/2011 1:40:14 PM 10/3/2011 10/4/2011 11:48:23 AM Group1

 

[SkipVought]

hi,

What have you tried so far?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

also keep in mind that each if has a THEN and an ELSE.

In other words...
[tt]
If expression then TRUE RESULT else FALSE RESULT
[/tt]
So when expression is TRUE then the TRUE RESULT is returned, otherwise the FALSE RESULT is returned.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

probably be easier to build a udf and use it in a query

Public Function getGroup(dtm1 As Variant, dtm2 As Variant, dtm1Insert As Variant, dtm2Insert As Variant) As String
  getGroup = "Group 1"
  If Nz(dtm1, 0) < Nz(dtm2, 0) Then
    getGroup = "Group 2"
  ElseIf Not IsNull(dtm1) And Not IsNull(dtm2) And (dtm1 = dtm2) Then
    If dtm1Insert < dtm2Insert Then
      getGroup = "Group 2"
    End If
  End If
End Function

verification

Public Sub TestIt()
 'date 1 and 2 null
 Debug.Print getGroup(Null, Null, #1/1/2012#, #1/2/2012#)
 'date1 not null, date 2 null
 Debug.Print getGroup(#1/1/2012#, Null, #1/1/2012#, #1/2/2012#)
 'date1 null, date 2 not null
 Debug.Print getGroup(Null, #1/1/2012#, #1/1/2012#, #1/2/2012#)
 'date1 > date 2
 Debug.Print getGroup(#1/2/2012#, #1/1/2012#, #1/1/2012#, #1/2/2012#)
  'date1 < date 2
 Debug.Print getGroup(#1/2/2012#, #1/3/2012#, #1/1/2012#, #1/2/2012#)
  'date1 = date 2, date 1 insert > date 2 insert
 Debug.Print getGroup(#1/1/2012#, #1/1/2012#, #1/3/2012#, #1/2/2012#)
  'date1 = date 2, date 1 insert < date 2 insert
 Debug.Print getGroup(#1/1/2012#, #1/1/2012#, #1/1/2012#, #1/2/2012#)
'Group 1
'Group 1
'Group 2
'Group 1
'Group 2
'Group 1
'Group 2
End Sub