Hello There,
I have records in an appointment table. Each appointment has a start time and end time. The person with the appointment must show up between these two times. I have a form where I have an unbound field that contains the current time - Now(). I need to compare the Now value to the Start and the end value. If the now value falls in between the Start and the End Value a specific form opens, if not a different form opens.
How I am attempting to handle this (and am very close) is:
I have and unbound Field ctrlCurrentTime =Now()
I have a second unbound field ctrlStart=Dlookup[starttime] - (Dlookup is based on an appointment ID on the form and underlying table so this works - I've tested it)
I have a third unbound field ctrlEnd =Dlookup[endtime] (tested and works).
I have added an if statement to say IF the start time is less than the current time then 1 else 0
I have another if statement that says IF the END time is greater than the current time then 1 else 0.
Now I have another unbound field that adds the result of the two if statements and holds the value.
Now I have an event that opens the form A if the sum of the two if statements is 2 or Form B if the sum isn't 2
The problem that I am having is with the end time. Event though the end time is greater than the current time is it always displaying as 0.
I read about date/time fields and I think that there is a date value also registered when the time is input in the End date in the underlying table, and because the value was input a day ago, the value is always seen as less than the current time/date.
I don't know if this is making any sense...
Basically I want to be able to compare the current time to a start time and an end time that has been entered in a table on any given day. If the current time is between those two previously entered times, I want a specific form to open else a different form.
Any thoughts, suggestions?????
-----------------------------------------------------
Please disregard this...(Unless you have a better method for accomlishing this)
I figured it out. Instead of using Now() in my unbound box for the comparision, I switched it to Time () and my formulas now work.
Thanks and sorry to have wasted anyones time. Although if this is a better way to approach this I would still like to know
I have records in an appointment table. Each appointment has a start time and end time. The person with the appointment must show up between these two times. I have a form where I have an unbound field that contains the current time - Now(). I need to compare the Now value to the Start and the end value. If the now value falls in between the Start and the End Value a specific form opens, if not a different form opens.
How I am attempting to handle this (and am very close) is:
I have and unbound Field ctrlCurrentTime =Now()
I have a second unbound field ctrlStart=Dlookup[starttime] - (Dlookup is based on an appointment ID on the form and underlying table so this works - I've tested it)
I have a third unbound field ctrlEnd =Dlookup[endtime] (tested and works).
I have added an if statement to say IF the start time is less than the current time then 1 else 0
I have another if statement that says IF the END time is greater than the current time then 1 else 0.
Now I have another unbound field that adds the result of the two if statements and holds the value.
Now I have an event that opens the form A if the sum of the two if statements is 2 or Form B if the sum isn't 2
The problem that I am having is with the end time. Event though the end time is greater than the current time is it always displaying as 0.
I read about date/time fields and I think that there is a date value also registered when the time is input in the End date in the underlying table, and because the value was input a day ago, the value is always seen as less than the current time/date.
I don't know if this is making any sense...
Basically I want to be able to compare the current time to a start time and an end time that has been entered in a table on any given day. If the current time is between those two previously entered times, I want a specific form to open else a different form.
Any thoughts, suggestions?????
-----------------------------------------------------
Please disregard this...(Unless you have a better method for accomlishing this)
I figured it out. Instead of using Now() in my unbound box for the comparision, I switched it to Time () and my formulas now work.
Thanks and sorry to have wasted anyones time. Although if this is a better way to approach this I would still like to know
Last edited:
