A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 30th, 2010, 03:24 PM posted to microsoft.public.access.gettingstarted
Kathryn
external usenet poster
 
Posts: 108
Default query for "events during the week beginning...."

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
  #2  
Old March 30th, 2010, 04:24 PM posted to microsoft.public.access.gettingstarted
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default query for "events during the week beginning...."

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" wrote:

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

  #3  
Old March 31st, 2010, 09:11 AM posted to microsoft.public.access.gettingstarted
Kathryn
external usenet poster
 
Posts: 108
Default query for "events during the week beginning...."

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" wrote:

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" wrote:

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

  #4  
Old March 31st, 2010, 03:04 PM posted to microsoft.public.access.gettingstarted
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default query for "events during the week beginning...."

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" wrote:

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" wrote:

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" wrote:

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

  #5  
Old April 1st, 2010, 08:30 AM posted to microsoft.public.access.gettingstarted
Kathryn
external usenet poster
 
Posts: 108
Default query for "events during the week beginning...."

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

"Jerry Whittle" wrote:

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" wrote:

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" wrote:

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" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.