Re: DatePart

On Tue, 17 Feb 2004 15:34:01 +0100, Portroe <fb_at_oooi.com> wrote:

>Hi All,
>
>I am currently struggling with the use of the DatePart() function,
>
>for instance if I have a check in and check out date in my hotel
>datebase, how can I find all bookings I have for may,
>
>A pointer to an online tutorial would be appreciated,

Sounds more like a MSAccess/VBA question to me. The Month(<some date>) function would be easier to use here, but it's also VBA and therefore does not work for anything but queries executing within Access itself, as opposed to queries over an ODBC connections to an Access database, or ASP web pages, for example.

If you store check-in and check-out dates in separate columns for the same booking, then you can use "BETWEEN ... AND" syntax which is also portable ANSI-SQL syntax. This would give you a booking for a stay which is entirely within the month of May. However, you will typically have some bookings which start on April 30th and end on May 2, for example, or cross the end of the month from May to June. Also, there is the (remote?) possibility that someone might stay for more than a month.

To catch all these, your "where" clause might look like this:

WHERE (checkin BETWEEN #05/01/2003# AND #05/31/2003#) OR (checkin <= #05/01/2003# AND checkout >= #05/01/2003#) OR (checkin <= #05/31/2003# AND checkout >= #05/31/2003#) etc.

You need the parentheses when mixing "AND" and "OR", BTW.

The date syntax as written above is the way Access understands it. If you are connecting from ODBC, you might need to use the ODBC canonical date syntax "{d '2003-05-01'}" for May 1st, for example (check the ODBC Programmer's Reference in MSDN for the exact syntax ... I might have misplaced a quote or something). ODBC also has some functions which you may be able to use which work similar to the VBA functions in Access.

HTH

--
Bob Hairgrove
NoSpamPlease_at_Home.com