Date and Time Fields

[Guest]

Im working on a lesson registration DB...
I have posted a question regarding blocking out past dates and times, so
that nobody could book a lesson for a past date / time combination.

I was given the suggestion that instead of using separate date and time
fields on my table, I should be running a combined Date / Time field.

Is this my best bet? How should I set this up to best fit Access' abilities?
Should I stick with 2 separate fields?

Any advice here would be great

 

[Rick B]

I don't know if it really matters. Access is capable of storing a date and
time in one field. You can still pull out jsut the date, or just the time
to use in calculations, reports, etc.

I believe (though I could be wrong) that every date stored in Access
includes a time. It is set to midnight if you simply enter a date.

Rick B

 

[Joseph Meehan]

A date is just another form of time. Access does not have separate date
and time field types. If you are storing a lesson that is scheduled for
3:30 PM on July 4, 2006 it is only one event or piece of information not
two. I see no reason, normally to divide it up.

Use the various display options to meet your needs.

 

[Guest]

Sounds good

Will the data input for a combined Date / Time field become more difficult?

I plan on using the Calendar feature for selecting a date. If the fields are
combined in the table, can I split the field input into 2 separate text boxes
on a form?

ie. Have the date selected by clicking on the calendar, and the time entered
into a text box below it?

 

[Albert D. Kallal]

As a general programming rule, you will find it MUCH easer to have the field
as a date time.

For example, to find any collisions in a booking for a board room, or AV
room, you can go:

requestStartDateTime <= EndDateTime
and
requestEndDateTime >= StartDateTime
and
requestbookingRoomID = BookingRoomID

Note the above thus would find if the room is available. And, that could be
from 9:00 am to 2:30 pm on Thursday.

Now, go and try and write the above query with dates + time...it is quite
messy.

You would have conditions for date, and then have conditions for time.

The above query shows how incredible simple it is to find a collision. If
the above query returns any records..then you can't book the room for that
date and time slot...

strWhere = "above sql where"

if dcount("*","tblBookings",strWhere) > 0 then
msgbox "sorry, room is already booked"
end if

So, combining time + date is usually a good idea.

A great example of this concept is why not use 3 fields to represent the
date? (MonthField, DayField, YearField). However, we have functions that can
extract the month, or day, or year as we need. So, to find all people that
have a birthday this month, we can go:

select * from tblCustomers where month(BirthDate) = 4

Now, the above would be just as simple if we had used 3 fields...but then
try and write a query that spans two months, or includes the middle of one
month to another month..then you got to start dealing with 3 fields in the
condition..and that gets messy, as you now have to have coditions for year,
month and day.

So, it is kind of nice to have "one" number that represents a date....

 

[Guest]

Albert...
Thanks
That sure summed up what I was looking for.

I have posted a thread today called "Email Field".... you dont by any chance
have any input there do you??

thanks again

Cheers