I have Table A with Date and Time as separate columns. The time value is entered as 1800 for each record - which translates to 6:00pm.
I have Table B with a transaction that has a transaction time. Depending on that time - I need to retrieve a value from Table A. If the time entered in Table B is equal to or after 6:00pm I get the record with today's date. If the time entered in Table B is before 6:00pm I get the record with the previous day's date.
Table A
Date Time Value
10/12/21 1800 1.01
1013/21 1800 1.30
10/14/21 1800 1.10
Table B
Date Time Value Expected
10/13/21 1909 1.30
10/13/21 1230 1.01
I can't seem to do a < comparison with my Time field to the constant of 1800. I tried to format the time field to be able to compare it to '06:00:00 PM' since that is the text value of 1800 but the result is True no matter what the Time value is.
Any help would be greatly appreciated. Thank you.
