I know a little VBA, but don't know if this is possible. I want to use the calendar to log when taxi journeys are taking place. Where I work, we organise taxi contracts for school students etc. I have a form that has individual month fields (in 'memo' format) where you can type in dates for that month, time of journey for that contract etc. However, it would be much quicker if you could just click on a calendar date, and that date would be added on as a string to the data already in that month field. Is this possible, or even wise? I would also want to keep track of the number of strings added, so that the database could work out the invoice amount for that month automatically. And when the particular month field is selected from the dropdown box, the calendar for that month would also appear.

First of all I think there is a database design issue here. You shouldn't have one record with all the trips in it for the month. You should have one record for each trip. This eliminates your need here, and also speeds invoicing. I would strongly urge against concatenating date strings and then trying to parse them at the end of the month, although that can quite readily be done.



mmcdonal

Yes, I had thought of that. I'm just not sure how to go about it, from a form point of view. I would probably get a bunch of dates in at once, with possibly two (or more) trips per day. That would perhaps preclude using the calendar? I suppose when clicking on a date there could then be an option to select am or pm or something? At some stage the dates and trip information would have to be concatenated, but this would only be for the purpose of a customised report that would be faxed to the taxi contractor.

AAAghghghghhh! Don't know where to start. . .

Offhand, the design would be:

tblDriver
DriverID - PK
etc

tblPassenger
PassID - PK
etc

tblTrip
TripID - PK
DriverID - FK
PassID - FK
TripDate - Date/Time
TripTime - Date/Time
etc

This will allow you to have a single record for each trip.

Then to create your report, just select * from tblTrip WHERE DriverID = etc, join on DriverID, group by date.

Then there is no reason to concatenate fields since they will all be in the report anyway.

Does that help?

mmcdonal

Sorry, I accidentally unsubscribed.

mmcdonal