Somewhat bizarre...?

[elsanto]

Afternoon all and sundry,

I have a problem which is causing me no little grief.

I have six fields in a form linked to a table.

1. Date In
2. Time In
3. Date Agent
4. Time Agent
5. Date Out
6. Time Out

In the form, I have validation formulas set up on field 4 and 6, which say, for example:

([Date In] & [Time In])>=([Date Out] & [Time Out])

Basically ensuring that staff can't enter the Date/Time Out as before the Date/Time In.
I thought it worked, but now I realize it doesn't.

This data works:
Date In 26/8/3
Time In 09:23
Date Out 26/8/3
Time Out 09:25

But this doesn't:
Date In 26/8/3
Time In 09:23
Date Out 26/8/3
Time Out 10:25

It's as though Access isn't reading the first "h" in "hh:nn". I'm not understanding this at all, and I have staff screaming for a fix. Any ideas?
Cheers one and all,

 

[elsanto]

I should clarify:

When I say it works, I mean the validation formula allows the data to be entered.

When I say it doesn't work, I mean the form won't allow the record to be completed. Displays the appropriate error message and insists on the user entering a different value.

 

[WayneRyan]

elsanto,

It looks like you have broken down your data elements into
text strings. It is far easier to deal with:

DateTimeIn
DateTimeAgent
DateTimeOut

and have them all as Date/Time fields. Then you can use
DateDiff, DatePart and other functions to work with them.

Are they stored in your tables as text?

Wayne

 

[elsanto]

Wayne,

I'd much rather deal with the fields you suggest, but for data enter purposes, the Date and Time fields must be separate.

They're in my table as Date/Time fields with dd/mm/yy format on the Date fields and Short Time on the Time fields.

 

[WayneRyan]

elsanto,

On the form, are the time fields kept in text boxes? When you
do comparisons/calculations why can't you concatenate them and
make a date/time field and use the built-in functions to handle
them?

Wayne

 

[elsanto]

The fields are in separate text boxes on the form, and unfortunately I do need to keep them this way.

 

[WayneRyan]

elsanto,

I don't use the order of your dates: dd/mm/yy

If it was my application, and I had them stored as you do:

Me.TempDate1 = CDate(Me.DateOut & Me.TimeOut)
Me.TempDate2 - CDate(Me.DateIn & Me.TimeIn)

Then I could use the functions on them without "changing"
your underlying data. The best of both worlds!

I hope that your regional settings let you handle the dates
in your format. Haven't you had problems with:

13/8/3 being less than 8/8/3?

Wayne

 

[elsanto]

Wayne,

Figured it out.
My validation formula was slightly out. I had:

=([Date In] & [Time In]) < ([Date Out] & [Time Out])

I should have had:

[Date In]+[Time In] < [Date Out]+[Time Out]

Couple of issues there, but the one that was screwing it all up was the "=" symbol at the start of the formula. I think it tries to evaluate the expression as true or false, and then tries to see if the TimeOut field is true or false (which it can never be).

Anyway, tis all good now.