dates in reports

[gryffin]

hello what im trying to do is create a timetable via a report like this:

           monday         tuesday        wedensday
morning
evening
night

in the database i have stored wether its morning evening or night, and i have the date stored, and i can use =Format([date],"dddd") to get the day from this date however im finding it hard to pace the correct day in the correct day area

i tried using a textbox under monday and having

test=Format([date],"dddd") =If test = "monday" and slot = "Morning" then ...insert date, customer name etc...
End if

and have that for every area ie. tuesday evening, wedensday morning etc, however that code is definitly not right, can anyone tell me a better way to do it or please show me how to fix the code (i do not know how to place the if after the day detection thing)

thanks

 

[raskew]

Hi -

A little more info would be helpful:

1) Where does afternoon figure in this?

2) Define morning, afternoon, evening and night. I would tend to think
morning = 0600 - 1159;
afternoon = 1200 - 1759
evening = 1800 - 2359
night = 2400 - 0559

...but, don't believe I've ever seen an 'official' definition.

Please post back. Think that some the posters can help you out.

Bob

 

[gryffin]

oh sorry

1) when a booking is placed, they select a day and a time (from morning, evening, night)

this is done in a form, and the morning evening or night is placed in the "slot" field, then it is placed into a table

therefore i need to find out which bookins are on monday morning, tuesday morning, wedensday morning etc and display them

then do what bookings are on monday evening, tuesday evening, etc etc..



so i was thinking, as its in colums, to put a textbox under each and then use a query to select bookings whcih's date field (after working out what day the date is actualy on with Format([date],"dddd") equals monday and which slot field equals morning etc.. and do that for all of the colums/rows so friday evening as a random example, but i dont know if thats possible or how to do iti

i hope that explained it better, thanks

 

[gryffin]

can anyone help i cant work it out, thanks alot

 

[raskew]

Hi -

If you'll return to my original response, you've answered neither of the questions. If you'd do that, we might be able to help.

Bob

 

[antomack]

Assuming you are dealing with a defined number of slots for each day then the following would be a way to achieve what you are looking for. Thus if you have x slots the assumption is that you will see only x customers per day.

Doing Manually for small number of fixed slots
Create a table with fields for the date and then a field for each of the slots available as in
SlotDate - date/time, primary key so only one record per date
Morning - text
Evening - text
Night - text

You then need to populate the new table with the dates you want the timetable created for, eg. add records for 6th February to 12th February. You can easily write code to automate this depending on what the user selects from a form. For subsequent runs you will also need to empty the table before appending the new records.

Create 3 (one for each slot) update queries, to update the table for each of the available slots i.e. to add the customer name to the relevant field for the specific date.
UPDATE YourNewTable INNER JOIN YourNewTable ON YourCurrentTable.YourDateField = YourNewTable.SlotDate SET YourNewTable.Evening = YourCurrentTable.YourCustomerNameField
WHERE YourCurrentTable.YourSlotField="Evening";

You now have a table which is in essence your timetable.
SlotDate Morning Evening Night
06/02/2006 another sdsd addsd
07/02/2006 green fgfg fdgfgjjk
08/02/2006 fg

Coding to handle large or varying number of slots
All this can be achieved through code to recreate the new table each time you run the timetable while taking into account that you may have varying slots available in the future.
a) You would first need to create a table listing the available slots which would then link to the form where slots are assigned.
b) When creating the new table through code you would need to loop through the slots table and create a field for each slot record.
c) append the default records for the required dates
d) loop through the slot records again and dynamically create the required update SQL which you can then execute to update each slots details.

NB
You could also create the table with fields for the dates rather than slots by adding default records for each of the slots you have available and looping through the required dates when creating the fields for the table but it would need to be defined to run for only a week at most.
Slot Monday Tuesday etc.
Morning another fffff gggggg
Evening dddddd hhh jjjjjjjjjjj

 

[gryffin]

hi thanks alot, i now have a table with the date and the slots

to book something the booking form is used, this is based on a query which links the booking table (date, slot, customer details, event title etc) and the new slot table

i now on this form have the slotdate field from the new slot table. What i am trying to get it to do is update off the date field above (this date field enters into the old booking table) so that the date does not have to be typed twice and i can hide it

i also have text fields relating to the new slots table "morning, evening, night" what would have to be done is the customer id would be entered into the morning textbox if they were booking the morning and the other two left blank. However above there is already a customer id field, and a slot select dropdown (entering into the booking table) so i was wondering if its possible to update these fields with a customer id if the slot was selected a few seconds ago in the dropdown above, so the customer id does not have to be manually entered in the correct box

if anyone could help me with that i would be forever grateful

thanks