date - time search in separate fields

[BradW]

Hi,

I have a database with a date field (mmddyyyy) and a time field (text format - 24 hour clock 1 to 2359), the time field is a bit strange because in 24 hour time if the time is 12:10 AM then it is 0010 in military time and this database stores it as 10, no padded zeroes. (I did not design the db).

I need to do crystal reports that search for a date time range such as from 4/7/2003 at 7 PM to 4/8/2003 at 3 AM. I have tried several techniques and keep hitting the wall where there are problems.

Anybody have any good ideas or solutions on how to do this type of a query with these two fields? Thanks in advance for the help.

 

[Madawc]

My advice is to 'go with the flow'. Stop treating time as time, but instead treat it as a code value that allows

Thus in your example, the limit would be date 4/8/2003 with a time-code of 0300. If it were more than two days, test for dates within the limits use just the date, while tests on the limit dates use date plus time code.

This is awkward, and quite possibly someone else knows of a brilliant way to covert your time-codes. But it should work, with a set of four tests
if date > start-date
or date < end-date
or (date = start-date and time-code >= start-time-code)
or (date = end-date and time-code <= end-time-code)

If you need to do the test several places, put it in a formula field, without the 'if' so it becomes a 'boolian' that is =true or =false Madawc Williams
East Anglia
Great Britain