Help with Time Blocking

[rbtm2006]

I have a database of customers that have trips at specific times. I have a report that sorts the times. The problem I am having is that I need space between the trips according to 30 min blocks of time. Example. Jane Smith is being picked up at 9:30am. Then the drivers next trip is at 1pm. I would need in essence space at 10, 10:30, 11,11:30, 12, and 12:30 on the report. Does this make since... I need some help!

Thanks

Jeremy

 

[vbaInet]

So what will fill up those empty spaces?

And how will it be spaced out if Jane Smith is being picked up at an odd time like 9:29 a.m.? How will it be spaced?

 

[rbtm2006]

So what will fill up those empty spaces?

And how will it be spaced out if Jane Smith is being picked up at an odd time like 9:29 a.m.? How will it be spaced?

The empty spaces simply need to be printed... so simply empty space.
We currently block in 30 min increments. So anything from 9:00 to 9:29 goes in the 9:00 time slot while anything from 9:30 to 9:59 goes in the 9:30 time slot. The same thru the day.

EXAMPLE of what I want it to look like
9:15 Jane Smith origin place w/c destination place
9:45 Don Smith origin place ambi destination place
[this would be empty for anything at the 10:00 to 10:29 times]
[this would be empty for anything at the 10:30 to 10:59 times]
11:00 Misty Smith origin place w/c destination place
[this would be empty for anything at the 11:30 to 11:59 times]
[this would be empty for anything at the 12:00 to 12:29 times]
12:36 Wendy Smith origin place w/c destination place


EXAMPLE of what it is currently doing.
9:15 Jane Smith origin place w/c destination place
9:45 Don Smith origin place ambi destination place
11:00 Misty Smith origin place w/c destination place
12:36 Wendy Smith origin place w/c destination place

 

[vbaInet]

The empty spaces simply need to be printed... so simply empty space.

Not that simple, but doable

So anything from 9:00 to 9:29 goes in the 9:00 time slot while anything from 9:30 to 9:59 goes in the 9:30 time slot.

Have you already implemented this part?

 

[rbtm2006]

Not that simple, but doable

Have you already implemented this part?

not yet... not sure how to do this... ive been playing but no luck.

 

[vbaInet]

Ok, is that actually a Date/Time field?

 

[rbtm2006]

Ok, is that actually a Date/Time field?

Yes the field name is [time]

 

[vbaInet]

Ok, the field should not be called Time because that's a reserved keyword in Access and it may cause problems.

What is the Data Type of this field?

 

[rbtm2006]

Ok, the field should not be called Time because that's a reserved keyword in Access and it may cause problems.

What is the Data Type of this field?

I changed the file name to [PUTime] and it is date/time type...

 

[vbaInet]

Good! Now, does the field actually contain a Date part to it? Give me some examples of the real raw data - without any formatting.

 

[rbtm2006]

Good! Now, does the field actually contain a Date part to it? Give me some examples of the real raw data - without any formatting.

The raw data we get from our provider has date and time in it. I have build a query that extracts the date and time in two separate fields.

The date in the [TripDate] field (date/time) and the time in the [PUTime] field (date/time). Time Field 2:26:00 PM Date field: 1/18/2012

 

[vbaInet]

Alright. As long as it's a Date/Time field we're good to go.

So in your query, put the following in a new field and see the result. If you're happy with the result we will proceed to the next step:

RoundPUTime: CDate(Format([PUTime], "hh:") & Nz(Switch(DatePart("n", [PUTime]) >= 30, 30), 0))

 

[rbtm2006]

Alright. As long as it's a Date/Time field we're good to go.

So in your query, put the following in a new field and see the result. If you're happy with the result we will proceed to the next step:

RoundPUTime: CDate(Format([PUTime], "hh:") & Nz(Switch(DatePart("n", [PUTime]) >= 30, 30), 0))

That works perfectly to organize the trips to the nearest 30 min... thanks... what are you thinking for the next step?

 

[vbaInet]

Right, the rest can be done in code and you can give it a go:

http://allenbrowne.com/ser-12.html

But I would have to think how it can be done in a query instead (when I've got some time).

 

[rbtm2006]

Ill keep trying it.... dont know how much good ill be... but will try. thanks!

 

[vbaInet]

It's not too difficult. You only need NextRecord and PrintSection methods. MoveLayout is not necessary. Do it in the Print event of the Detail section.

 

[vbaInet]

How are we looking there? I just noticed that I kept this tab open so just checking on your progress. Post a test db, I want to see how it can be done in a query rather than doing it in code in a report.

 

[rbtm2006]

I am so totally lost with this... I have been trying and not getting anywhere... Let me post the DB so you can look at it thus far! I am sorry im not gettnig anywhere with it... I am trying but frustrated!! Thanks

 

[vbaInet]

Try, try and try again.

Here's how to upload the db:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

 

[rbtm2006]

Here is the DB with dummy data... Ill keep trying... I just don't understand the concept it is trying to have me do. OYY! Thanks for the support!
​