Returning Days of the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello ther

I hope someone can help, I need some sample code that can get me started. I need to produce a diary for the month to show which vehicles are out on loan. At present we have the data in an excel spreadsheet, which is very simple to input and looks nice, but now we have a form where the operator will enter a start and end date for when the vehicle is out on loan and then I need some code to pick up these dates and somehow at a glance to the user that the vehicle is out. I hope I have explained myself properly

Many Thanks in advanc

Sue
 
At present we have the data in an excel spreadsheet, which is very
simple to input and looks nice, but now we have a form where the
operator will enter a start and end date for when the vehicle is out
on loan and then I need some code to pick up these dates and somehow
at a glance to the user that the vehicle is out.
Click to expand...

This is really an Excel programming question, but it's probably something
like:

Set rng = GetRangeOfDatesOnSheet()

For Each c In rng.Cells
If dtStartDate <= c.Value And c.Value <= dtEndDate Then
c.BackgroundColor = xlYellow

Else
c.BackgroundColor = xlWhite

End If

Next c


Hope that helps


Tim F
 
Hi Ti

Thanks for your reply, but we have now created a database in Access XP, so we really want to see the data highlighted in Access if possible. Would appreciate any help you could give me

Many Thank

Sue
 
Can you give us a bit more info about the process to be used for showing
which vehicles are out on loan? Will this be a report? Will this be done
when a user tries to "schedule" a vehicle and the database must validate
that the vehicle is available? Are you storing in the table the "start date"
and the "end date"? More info, please.

--
Ken Snell
<MS ACCESS MVP>

Sue Mckeating said:
Hello there

I hope someone can help, I need some sample code that can get me started.
Click to expand...
I need to produce a diary for the month to show which vehicles are out on
loan. At present we have the data in an excel spreadsheet, which is very
simple to input and looks nice, but now we have a form where the operator
will enter a start and end date for when the vehicle is out on loan and then
I need some code to pick up these dates and somehow at a glance to the user
that the vehicle is out. I hope I have explained myself properly!
 
The database hold a list of vehicles that can be rented out for a peiod, anything from a day to a year. The Primary key for the table is the fleet number which is a text field. When someone books a vehicle for hire, they enter into a form the start and end date of the rental period. I need to produce a report that will show at a glance for each day of the month which vehicles are out on loan. eg (A bit like what Microsoft Project produces when inputting a task

Heading going across the page would be the days in the month, so they would be 1 through to 28, 30 or 31 depending on how many days are in the month like listed below

Running down the side would be the vehicle name and the data inside the grid would say either out or in (out if on rental or in available for rental
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 et
Vehicle 1 in in out out out in in et
Vehicle 2 out in out ou
Vehicle
et

I hope I have explained myself properly this tim

Many Thanks, I dont know what I would do without these newsgroups

Kind Regard

Sue
 
What you're seeking probably is best done with a Pivot Table query....which,
unfortunately, is not something on which I have experience yet.

Let me send a note to another MVP who is good with these and I'll ask him to
jump into this thread.

--
Ken Snell
<MS ACCESS MVP>

Sue McKeating said:
The database hold a list of vehicles that can be rented out for a peiod,
Click to expand...
anything from a day to a year. The Primary key for the table is the fleet
number which is a text field. When someone books a vehicle for hire, they
enter into a form the start and end date of the rental period. I need to
produce a report that will show at a glance for each day of the month which
vehicles are out on loan. eg (A bit like what Microsoft Project produces
when inputting a task)
Heading going across the page would be the days in the month, so they
Click to expand...
would be 1 through to 28, 30 or 31 depending on how many days are in the
month like listed below.
Running down the side would be the vehicle name and the data inside the
Click to expand...
grid would say either out or in (out if on rental or in available for
rental)
 
Sue,
You might want to look at the example calendar reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. There is at least one
report "Crew Rotation Schedule" that creates bars. You would need to make
some modifications since the column on the left shows ships and the dates
across the top are months rather than days in a month.

If that won't work for you, I would create a table of all possible dates
from 1/1/2004 to a very future date:
tblCalendar
==========
[calDate] DateTime field with one record per day
You can add this table to a query of your rental and add a criteria under
the [calDate] field of
Between [StartDate] And [EndDate]
This will create one row in your resultset for every date a vehicle is out.
Add a caculated column to the query
CalMonth:Format([calDate],"yyyymm")
set its criteria to filter to this month like:
Format(Date(), "yyyymm")
Then change this to a crosstab with FleetNumber as the Row Heading, "Day" &
Day([calDate]) as the Column Heading, and TheValue: "X" as the value and
set it to First.

I much prefer the look of the first suggested report but it does require
some math.
--
Duane Hookom
MS Access MVP


Sue McKeating said:
The database hold a list of vehicles that can be rented out for a peiod,
Click to expand...
anything from a day to a year. The Primary key for the table is the fleet
number which is a text field. When someone books a vehicle for hire, they
enter into a form the start and end date of the rental period. I need to
produce a report that will show at a glance for each day of the month which
vehicles are out on loan. eg (A bit like what Microsoft Project produces
when inputting a task)
Heading going across the page would be the days in the month, so they
Click to expand...
would be 1 through to 28, 30 or 31 depending on how many days are in the
month like listed below.
Running down the side would be the vehicle name and the data inside the
Click to expand...
grid would say either out or in (out if on rental or in available for
rental)
 
Hi Sue,

In Access there are sample DBs provided. The one that
would be applicable to your task would be the 'Resource
Scheduling' DB. It might not be exactly what you want but
it certainly will give you a big head start.

When you open Access it will offer to open an existing
DB , create a new database, or the 'Database Wizard'.
Select the wizard, and you'll find a list of DB templates
which should include the scheduler.

Good luck

Adam
-----Original Message-----
Hello there

I hope someone can help, I need some sample code that can
Click to expand...
get me started. I need to produce a diary for the month
to show which vehicles are out on loan. At present we
have the data in an excel spreadsheet, which is very
simple to input and looks nice, but now we have a form
where the operator will enter a start and end date for
when the vehicle is out on loan and then I need some code
to pick up these dates and somehow at a glance to the user
that the vehicle is out. I hope I have explained myself
properly!
 
Hi Sue,

Please pardon me for jumping in.

Something about your primary key turned on a caution light and reminded me
of a conference room scheduling database that I created years ago. In that
database, I think I used the conference room number, conference start
date/time, and conference end date/time as a multi-column primary key to
avoid scheduling conflicts. You might need to consider the same thing.
Otherwise, you're relying on the data-entry to be error-free instead of
letting the database police itself. The reporting would be a lot easier too
because all you would need to show is the fleet number, date/time out, and
date/time in. Just a thought.

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off


Sue McKeating said:
The database hold a list of vehicles that can be rented out for a peiod,
Click to expand...
anything from a day to a year. The Primary key for the table is the fleet
number which is a text field. When someone books a vehicle for hire, they
enter into a form the start and end date of the rental period. I need to
produce a report that will show at a glance for each day of the month which
vehicles are out on loan. eg (A bit like what Microsoft Project produces
when inputting a task)
Heading going across the page would be the days in the month, so they
Click to expand...
would be 1 through to 28, 30 or 31 depending on how many days are in the
month like listed below.
Running down the side would be the vehicle name and the data inside the
Click to expand...
grid would say either out or in (out if on rental or in available for
rental)
 
I would like to thank you all for your advise, you have all given me something to think about and am going to try them all today and see which one will suit us best

Sue
 
Calvin,
You wrote "the conference room number, conference start date/time, and
conference end date/time as a multi-column primary key to avoid scheduling
conflicts. "
I'm not sure how this would avoid scheduling conflicts since the following
would create a scheduling conflict that would not be caught by any type of
unique index or primary key.
RoomA 1/1/2004 2/1/2004
RoomA 1/5/2004 1/10/2004
 
Hi Duane,

I also wrote, "I think I used" those, but probably used another column in
combination with those three. If only I could remember the other column(s).
Hmmm, that was only about five years ago. <g> But the point is that her
single column will not be a bullet-proof solution in this case and needs to
be thought out more.
 
You made the statement once and then emphasized it again and Sue apparently
attempted to follow your recommendation. I only wanted to point out that it
will not prevent double-booking. I don't believe there would be any
combination of fields in this case that would create a natural unique index
and prevent double-booking.
 
I don't believe there would be any
combination of fields in this case
that would create a natural unique
index and prevent double-booking.
Click to expand...

Well, with all due respect, that's very premature for you to write without
knowing anything at all about her database design. Looks like you're stuck
on my incomplete example though instead of focusing on her issue. As she
wrote, we gave her some good ideas, so I'm not sure why the thread is
ongoing. If Alamo, Budget, Hertz, etc, can create a database to prevent
double booking, so can she [with a well thought out primary key as I
repeatedly keep writing].
 
PMFJI.

Calvin,
You could be correct, however given Duane's example I don't see what combination
of fields would do what you propose. I would like to see your example as I am
always eager to learn.

With all respect,

John
I don't believe there would be any
combination of fields in this case
that would create a natural unique
index and prevent double-booking.
Click to expand...

Well, with all due respect, that's very premature for you to write without
knowing anything at all about her database design. Looks like you're stuck
on my incomplete example though instead of focusing on her issue. As she
wrote, we gave her some good ideas, so I'm not sure why the thread is
ongoing. If Alamo, Budget, Hertz, etc, can create a database to prevent
double booking, so can she [with a well thought out primary key as I
repeatedly keep writing].

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off

Duane Hookom said:
You made the statement once and then emphasized it again and Sue apparently
attempted to follow your recommendation. I only wanted to point out that it
will not prevent double-booking. I don't believe there would be any
combination of fields in this case that would create a natural unique index
and prevent double-booking.

--
Duane Hookom
MS Access MVP


type
Click to expand...
Click to expand...
 
Alamo, Budget, Hertz etc probably don't use Access/Jet as a backend for
their data storage. I'm sure that systems like these (and even Access
systems) use a procedure that makes sure that for a particular vehicle that
the Start of a rental "A" is not prior to the End of rental "B" if the End
of Rental "A" is after the Start of rental "B". These larger systems will
have a business logic tier that would prohibit this from occurring.

Any of Sue's data entry forms could use this logic to prevent overlapping
bookings. In "booking" applications that I have created, I built a function
that accepted the resource (vehicle, room,...), Start, and End and returned
if it overlapped a previous booking.

I think the original question regarded schedule presentation to the users.
Several good examples have been provided. I hope Sue finds one she can use
or replies back of additional assistance.

--
Duane Hookom
Microsoft Access MVP


CSmith said:
I don't believe there would be any
combination of fields in this case
that would create a natural unique
index and prevent double-booking.
Click to expand...

Well, with all due respect, that's very premature for you to write without
knowing anything at all about her database design. Looks like you're stuck
on my incomplete example though instead of focusing on her issue. As she
wrote, we gave her some good ideas, so I'm not sure why the thread is
ongoing. If Alamo, Budget, Hertz, etc, can create a database to prevent
double booking, so can she [with a well thought out primary key as I
repeatedly keep writing].

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off


Duane Hookom said:
You made the statement once and then emphasized it again and Sue apparently
attempted to follow your recommendation. I only wanted to point out that it
will not prevent double-booking. I don't believe there would be any
combination of fields in this case that would create a natural unique index
and prevent double-booking.

--
Duane Hookom
MS Access MVP


needs
to type
Click to expand...
given
me
Click to expand...
Click to expand...
 
Back
Top