UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Values Of Two Different Date Fields, Access 2016    
 
   
troybos@gmail.co...
post Nov 11 2019, 07:38 PM
Post#1



Posts: 4
Joined: 15-August 18



I'm not sure if this is possible, but I am trying to populate a column in a query based on dates from two different date fields. I have a Startdate1 and a Startdate2 field. For the third field (FinalStartDate) I want to take the date in the Startdate1 field if there is one. If there is no date in the Startdate1 field, I want to use the Startdate2 field. I have tried iif statements and can't seem to get it to work. Any ideas?
Go to the top of the page
 
RJD
post Nov 11 2019, 07:51 PM
Post#2


UtterAccess VIP
Posts: 10,153
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

You might try something like ...

FinalStartDate: NZ([StartDate1],[StartDate2])

This assumes that StartDate1 is actually Null and not a ZLS.

If it might be a ZLS, you might try something like ...

FinalStartDate: IIf(Len([StartDate1])>0,[StartDate1],[StartDate2])

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
troybos@gmail.co...
post Nov 12 2019, 04:07 PM
Post#3



Posts: 4
Joined: 15-August 18



Thanks so much. This worked well. One follow-up question...how can I change this to an integer? I tried StartDate: Nz(Date([InterimStart]),Date([FinalStart])), but it throws an error.
Go to the top of the page
 
RJD
post Nov 12 2019, 05:56 PM
Post#4


UtterAccess VIP
Posts: 10,153
Joined: 25-October 10
From: Gulf South USA


You are very welcome.

QUOTE
...how can I change this to an integer? I tried StartDate: Nz(Date([InterimStart]),Date([FinalStart])), but it throws an error.

Yes, of course, that will throw an error. There is no function Date(x) where x is any value. Date() is the current date.

Not sure what you mean by changing it to an integer. What do you want the final result to look like? Can you give us some examples? Of before and after?

An integer conversion of a date usually means getting the number of days since the internal calendar base date of 30 Dec 1899 (I think that's the date). That doesn't seem to be what you want ...

Looking at your other thread ... do you mean getting just the year?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
sxschech
post Nov 12 2019, 06:34 PM
Post#5



Posts: 16
Joined: 2-October 18



Here is another option using Coalesce function from codekabinett

CODE
Public Function Coalesce(ParamArray arguments()) As Variant
'Displays First non-null value from list.  If used in a
'query and there are 5 fields and the third field has a
'value, that would be displayed.  If next record, has a
'value in the fifth field, that would be displayed.
'For non queries, provide a csv list can mix any data
'type such as text, numbers, dates and will still display
'the first instance in the list
'https://codekabinett.com/rdumps.php?Lang=2&targetDoc=coalesce-function-paramarray-keyword-vba-argument
'20190418
    Dim retVal As Variant
    Dim i As Long
    
    retVal = Null
    
    For i = LBound(arguments) To UBound(arguments)
        If Not IsNull(arguments(i)) Then
            retVal = arguments(i)
            Exit For
        End If
    Next i

    Coalesce = retVal

End Function


FinalStartDate: Coalesce([StartDate1],[StartDate2])
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 05:47 AM