Discussions

Expand all | Collapse all

ToDate function

  • 1.  ToDate function

    Posted 19 days ago

    I was just given the job of modifying a Quickbase database to prepare for a merger next week. I come from the SQL world.

    There is one field they want changed that I need help with. The current code snip-it is:

    ToDate([Date Created])<Date(2021,1,1),("E" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019]))))


    They want the E changed to a P. That part is easy. But they also want the E to stay attached to anything from 1/1/2020 to 6/20/2020 and the P for anything starting 7/1/2020. If I just change the E to a P, it does it for everything for the year.

    I looked at the ToDays, but I'm getting myself confused.

    Can I just do:

    ToDate([Date Created])<Date(7/1/2020,1,1),("E" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019]))))

    ToDate([Date Created])>Date(7/1/2020,1,1),("P" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019]))))

    Or is there a better solution. There is a lot of weird code which I get to muddle through now.



    ------------------------------
    Nate
    ------------------------------


  • 2.  RE: ToDate function

    Posted 19 days ago
    When you get into complex formulas, the syntax gets quirky really quick.

    Your option in an If Statement structure should work they way you wish:
    If(

    ToDate([Date Created])<Date(7/1/2020,1,1),("E" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019])))),

    If(
    ToDate([Date Created])>Date(7/1/2020,1,1),("P" & Right(ToText(Year(ToDate([Date Created]))),2)
    &("-" &([Envoy PN 2019])))),
    null))

    the null part might be "", depending on field type, but you get the idea.



    ------------------------------
    Ryan Stanford
    ------------------------------