Booking Query

[rushhour]

Hi

I need help with queries in MS Access 2003. I am creating a booking system and need to show all dinner bookings for the coming week. Does anyone have any ideas on how to go about this? The query should show the customer's forename and surname, time and number of dinners all for the coming week eg. week beginning 06th October 2008-12th October 2008. It would also need to show them for other weeks of course and not just for that week.

Thanks in advance.

 

[raskew]

As a starter, this:

? date() - WeekDay(date()) + 2 + IIf(WeekDay(date()) >= 2, 7, 0)

... will return the next Monday (Sun = 1, Mon = 2 thru Sat = 7)

adding 6 days to that date will return the following Sunday.

Need a little more info on your query to provide a concise answer.

HTH - Bob

 

[rushhour]

As a starter, this:

? date() - WeekDay(date()) + 2 + IIf(WeekDay(date()) >= 2, 7, 0)

... will return the next Monday (Sun = 1, Mon = 2 thru Sat = 7)

adding 6 days to that date will return the following Sunday.

Need a little more info on your query to provide a concise answer.

HTH - Bob

Thanks for your reply well basically the question asks the following to be completed:

1.[FONT=&quot] [/FONT]To show all the Dinner bookings for the coming week including Customer names, time and number of diners using the query wizard.


2. As above, but using the design query to show only the bookings of a particular Account Customer during the last four weeks.

 

[raskew]

Try copying/pasting this as the criteria for your date field:

Between Date()-Weekday(Date())+2+IIf(Weekday(Date())>=2,7,0) And DateAdd("d",6,Date()-Weekday(Date())+2+IIf(Weekday(Date())>=2,7,0))

Bob

 

[rushhour]

Thanks that worked for me. Any ideas on how to do the next part which is the same as before, but using the design query to show only the bookings of a particular Account Customer during the last four weeks. This will be the last time that I require your help.

Thanks if you can help.

 

[rushhour]

To clarify, i need help on one more thing, i need the secnd query to do the same thing again, but to show only the bookings of a particular Account Customer during the last four weeks.

Thanks if anyone can help.

 

[raskew]

Hi -

Sorry for the delay (see below for details).

The simple answer is to enter a parameter (e.g. [Enter CustomerID]) as criteria for your CustomerID field.

What hung me up was attempting to include an Iif() statement which would allow you to either enter a CustomerID to see just those specific records, or enter "All" to return all records.

For some reason I still don't have it. There are a multitude of references in this forum and others, yet I can't seem to get the "All" side of the equation working.

Hopefully someone will see this post and provide some guidance.

Best Wishes - Bob

 

[rushhour]

Well basically i need to do it as the first query, but to show only the bookings of a particular Account Customer during the last four weeks.

Can either yourself, or someone else help me? Just read through the topic and hopefully you can help. I also need an answer as quick as possible from anyone who can help me.

Thanks in advance.

 

[rushhour]

Any help at all would be much appreciated, so please if anyone knows what to do, please click reply and reply!

 

[WayPay]

You'll need something like

BookingDate BETWEEN Date() and (Date()-28)

in your WHERE clause.

 

[rushhour]

You'll need something like

BookingDate BETWEEN Date() and (Date()-28)

in your WHERE clause.

can you go through this exactly step-by-step please.

 

[WayPay]

You're not giving us much to work on, you know . What is the SQL statement that solved you first problem?

 

[rushhour]

This one

Try copying/pasting this as the criteria for your date field:

Between Date()-Weekday(Date())+2+IIf(Weekday(Date())>=2,7,0) And DateAdd("d",6,Date()-Weekday(Date())+2+IIf(Weekday(Date())>=2,7,0))

Bob

that solved my first problem, but the second one needs to be the same as the first query, but to show only the bookings of a particular Account Customer during the last four weeks.

 

[WayPay]

Oops, didn't read the thread well enough , I thought I needed to know the name of the date field.

Try copying/pasting this as the criteria for your date field:

Between (Date() and (Date()-28))

 

[raskew]

Hi -

My post (Post #4), lengthy as it is, will provide the following week. The hangup, which I poised, is to give the operator the option of either selecting 'All' appointments for the week, or selecting a specific customer.

Bob

 

[rushhour]

Oops, didn't read the thread well enough , I thought I needed to know the name of the date field.

Try copying/pasting this as the criteria for your date field:

Between (Date() and (Date()-28))

This didnt work sorry, it says there is a missing operator before between or something like that, can you help again!

 

[WayPay]

My bad.

Between Date() and (Date()-1)

 

[raskew]

Hello - What are we smoking here?

...need to show all dinner bookings for the coming week.

Not today and yesterday..

Between Date() and (Date()-1)

or the past 4 weeks..

Between Date() and (Date()-28)

Hope you get to feeling better.

Bob

 

[WayPay]

..or the past 4 weeks..

I think he did ask for that:

2. As above, but using the design query to show only the bookings of a particular Account Customer during the last four weeks.

But yeah, me making 2 typos in a row is not nice .

Hope you get to feeling better.

I will, after having a GOOD night's sleep ..

 

[rushhour]

\so can you quickly confirm what one i should use for the query to show the bookings for a particular account customer over the past four weeks?

Thanks.