Check presense of date/Time field? | IF in DateTime field gives syntax error | Discussions

Expand all | Collapse all

Check presense of date/Time field? | IF in DateTime field gives syntax error

  • 1.  Check presense of date/Time field? | IF in DateTime field gives syntax error

    Posted 01-27-2019 16:53
    Hi there QB-community,

    I'm trying to build something around timekeeping.
    In my design I have 2 date/time fields to get a start DateTime value from the user.
    One field is created as a timestamp the other field is created by manual entry.
    [StartTime_TimeStamp]
    [StartTime_Manual]

    Via a third formula date/time field [StartTimeFinal]  I like to use to most preferred start time in further presentation and reporting purposes.

    So basically i look for an IF function to set the [StartTimeFinal]
    in human logic something like:

    IF [StartTime_Timestamp] exists than [StartTimeFinal] = [StartTime_TimeStamp] else
    IF [StartTime_Manual] exists than [StartTimeFinal] = [StartTime_Manual]

    But using the IF function in a date/time field gives a syntax error "expecting DateTime"

    Who can give me some suggestions ?

    Thanks Jan-Willem






  • 2.  RE: Check presense of date/Time field? | IF in DateTime field gives syntax error

    Posted 01-27-2019 17:34
    Try this

    IF( not isnull([StartTime_Timestamp]), [StartTime_TimeStamp], [StartTime_Manual])


  • 3.  RE: Check presense of date/Time field? | IF in DateTime field gives syntax error

    Posted 01-27-2019 19:35
    Ah thanks 'not isnull' ... everytime again you learn me something new :-)

    about an hour off puzzling brought me to this solution that also seems to work
    If(Day(ToDate([StartTime_TimeStamp]))<32,[StartTime_Manual],[StartTime_TimeStamp]).
    I must admit yours is less coding and conversion thus possibly better/cleaner

    We just finished dinner on my end.. so I continue my quest.. let's see what works best in the end.
    I'll let you know.






  • 4.  RE: Check presense of date/Time field? | IF in DateTime field gives syntax error

    Posted 01-27-2019 19:40
    The goal is to write formulas with the least code yet most understandable for the long term.

    Some poor soul years in the future, perhaps even you, will one day need to adjust this formula or see what logics it�s using so you want to have formula very obvious to someone looking at it for the first time.


  • 5.  RE: Check presense of date/Time field? | IF in DateTime field gives syntax error

    Posted 01-27-2019 23:44
    It works perfect with the 'not isnull' suggestion thanks