Help with a formula query, please
I'm having trouble with a formula query. I can't get the output to match what I expect. Right now, all I want is the field to give a list of all my records that have a User Input Date on or before the...
Forum Discussion
Back to the OP question (my own) after discussion with support, this is a known issue when apps are in DD-MM-YYYY format. Might be something to consider if you ever need to change the format in your app. The issue ID is QBE016513.
The proposed solution/workaround was to convert any date I want to compare into a number in its own field and then run my query based on that number field using GT, GTE, LT, LTE, EX, XEX and etc. The proposed date to number conversion here:
ToNumber(ToText(Year([User Input Date]))&
PadLeft(ToText(Month([User Input Date])),2,"0")&
PadLeft(ToText(Day([User Input Date])),2,"0"))
Though to me, this sort of conversion makes more sense, since it always puts two consecutive dates as two consecutive numbers:
ToDays([User Input Date]-Date(1970, 1, 1))
Bit of a pain, really, to have to have both a Date field and a Number field for each date that I might want to compare later, but since it is really important for my potential users to have DD-MM-YYYY formats, it will be worth it. At least I'm not also dealing with hh:mm:ss!
Is there a way we close these discussions? Or do they just remain forever open to be commented on whenever?
------------------------------
David Halter
------------------------------
The proposed solution/workaround was to convert any date I want to compare into a number in its own field and then run my query based on that number field using GT, GTE, LT, LTE, EX, XEX and etc. The proposed date to number conversion here:
ToNumber(ToText(Year([User Input Date]))&
PadLeft(ToText(Month([User Input Date])),2,"0")&
PadLeft(ToText(Day([User Input Date])),2,"0"))
Though to me, this sort of conversion makes more sense, since it always puts two consecutive dates as two consecutive numbers:
ToDays([User Input Date]-Date(1970, 1, 1))
Bit of a pain, really, to have to have both a Date field and a Number field for each date that I might want to compare later, but since it is really important for my potential users to have DD-MM-YYYY formats, it will be worth it. At least I'm not also dealing with hh:mm:ss!
Is there a way we close these discussions? Or do they just remain forever open to be commented on whenever?
------------------------------
David Halter
------------------------------
HI David ,
Thanks for sharing the update from QuickBase Support. I too use DD-MM . I was trying to work with DATE(YYYY,MM,DD) format and failing (to some degree)
@Mark Shnier , I just found my frustration from David Answer.
------------------------------
Prashant Maheshwari
------------------------------