query for "events during the week beginning...."

  • Thread starter Thread starter Kathryn
  • Start date Start date
K

Kathryn

Is it possible to set a query to list events with a parameter "week beginning
dd/mm/yyyy" - or do we have to use between two dates a week apart?

Many thanks
 
Try this in the criteria for your date field:

Between [week beginning dd/mm/yyyy] And [week beginning dd/mm/yyyy] + 6.99999

Make sure to define [week beginning dd/mm/yyyy] as a Date/Time field in the
parameter box. It should only prompt you for the date once.
 
Thanks Jerry

I tried what you suggested and, when I typed in the date, got the following
message: The expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the expression
to variables." - which I would if had a clue what it meant!

Basically the query is to list the layouts of our conference room for the
coming week if either the conference room or all our rooms are booked.
In the Events table, the Date of event is in Date/Time format - I wasn't
sure how to specify it in a parameter box (or should that happen
automatically).

Here is how the SQL currently looks in case that helps:
SELECT Events.[Date of event], Events.Title, Events.[Start Time],
Events.[End Time], Events.[Conf Rm Layout], Events.[Estimated number]
FROM Events
WHERE (((Events.[Date of event]) Between [week beginning dd/mm/yyyy] And
[week beginning dd/mm/yyyy]+6.99999) AND ((Events.[All])=True)) OR
(((Events.Conf)=True));

Thanks again.

Jerry Whittle said:
Try this in the criteria for your date field:

Between [week beginning dd/mm/yyyy] And [week beginning dd/mm/yyyy] + 6.99999

Make sure to define [week beginning dd/mm/yyyy] as a Date/Time field in the
parameter box. It should only prompt you for the date once.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kathryn said:
Is it possible to set a query to list events with a parameter "week beginning
dd/mm/yyyy" - or do we have to use between two dates a week apart?

Many thanks
Click to expand...
Click to expand...
 
Test this to see if it works:

PARAMETERS [Enter Week Beginning Date] DateTime;
SELECT Events.[Date of event],
Events.Title,
Events.[Start Time],
Events.[End Time],
Events.[Conf Rm Layout],
Events.[Estimated number]
FROM Events
WHERE Events.[Date of event]
Between [Enter Week Beginning Date]
And [Enter Week Beginning Date] + 6.99999 ;

If that works, try this:
PARAMETERS [Enter Week Beginning Date] DateTime;
SELECT Events.[Date of event],
Events.Title,
Events.[Start Time],
Events.[End Time],
Events.[Conf Rm Layout],
Events.[Estimated number]
FROM Events
WHERE Events.[Date of event]
Between [Enter Week Beginning Date]
And [Enter Week Beginning Date] + 6.99999
AND (Events.[All]=True OR Events.Conf=True);

Notice that the first line defines the parameter as data/time. Also I
changed the parameter some to make it simpler. The date entry format
shouldn't matter anyway UNLESS the Events.[Date of event] field is not a Date
Time data type.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kathryn said:
Thanks Jerry

I tried what you suggested and, when I typed in the date, got the following
message: The expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the expression
to variables." - which I would if had a clue what it meant!

Basically the query is to list the layouts of our conference room for the
coming week if either the conference room or all our rooms are booked.
In the Events table, the Date of event is in Date/Time format - I wasn't
sure how to specify it in a parameter box (or should that happen
automatically).

Here is how the SQL currently looks in case that helps:
SELECT Events.[Date of event], Events.Title, Events.[Start Time],
Events.[End Time], Events.[Conf Rm Layout], Events.[Estimated number]
FROM Events
WHERE (((Events.[Date of event]) Between [week beginning dd/mm/yyyy] And
[week beginning dd/mm/yyyy]+6.99999) AND ((Events.[All])=True)) OR
(((Events.Conf)=True));

Thanks again.

Jerry Whittle said:
Try this in the criteria for your date field:

Between [week beginning dd/mm/yyyy] And [week beginning dd/mm/yyyy] + 6.99999

Make sure to define [week beginning dd/mm/yyyy] as a Date/Time field in the
parameter box. It should only prompt you for the date once.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kathryn said:
Is it possible to set a query to list events with a parameter "week beginning
dd/mm/yyyy" - or do we have to use between two dates a week apart?

Many thanks
Click to expand...
Click to expand...
Click to expand...
 
Brilliant! Many thanks - they worked first time ... a great start to a
morning's work.

Jerry Whittle said:
Test this to see if it works:

PARAMETERS [Enter Week Beginning Date] DateTime;
SELECT Events.[Date of event],
Events.Title,
Events.[Start Time],
Events.[End Time],
Events.[Conf Rm Layout],
Events.[Estimated number]
FROM Events
WHERE Events.[Date of event]
Between [Enter Week Beginning Date]
And [Enter Week Beginning Date] + 6.99999 ;

If that works, try this:
PARAMETERS [Enter Week Beginning Date] DateTime;
SELECT Events.[Date of event],
Events.Title,
Events.[Start Time],
Events.[End Time],
Events.[Conf Rm Layout],
Events.[Estimated number]
FROM Events
WHERE Events.[Date of event]
Between [Enter Week Beginning Date]
And [Enter Week Beginning Date] + 6.99999
AND (Events.[All]=True OR Events.Conf=True);

Notice that the first line defines the parameter as data/time. Also I
changed the parameter some to make it simpler. The date entry format
shouldn't matter anyway UNLESS the Events.[Date of event] field is not a Date
Time data type.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kathryn said:
Thanks Jerry

I tried what you suggested and, when I typed in the date, got the following
message: The expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many complicated
elements. Try simplifying the expression by assigning parts of the expression
to variables." - which I would if had a clue what it meant!

Basically the query is to list the layouts of our conference room for the
coming week if either the conference room or all our rooms are booked.
In the Events table, the Date of event is in Date/Time format - I wasn't
sure how to specify it in a parameter box (or should that happen
automatically).

Here is how the SQL currently looks in case that helps:
SELECT Events.[Date of event], Events.Title, Events.[Start Time],
Events.[End Time], Events.[Conf Rm Layout], Events.[Estimated number]
FROM Events
WHERE (((Events.[Date of event]) Between [week beginning dd/mm/yyyy] And
[week beginning dd/mm/yyyy]+6.99999) AND ((Events.[All])=True)) OR
(((Events.Conf)=True));

Thanks again.

Jerry Whittle said:
Try this in the criteria for your date field:

Between [week beginning dd/mm/yyyy] And [week beginning dd/mm/yyyy] + 6.99999

Make sure to define [week beginning dd/mm/yyyy] as a Date/Time field in the
parameter box. It should only prompt you for the date once.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Is it possible to set a query to list events with a parameter "week beginning
dd/mm/yyyy" - or do we have to use between two dates a week apart?

Many thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top