query multiple non-consecutive dates

[Todd]

I am trying to build a report based on a query that will show client activity
for specific dates over a 3 month period. Is there a way to build the
criteria using expression builder?

Example: I want to find activity for a specific week in October, November,
and December for each client. I want the user to be able to input the dates
for each of these months. I am familiar with the "Between [StartDate] and
[EndDate]". I just can't figure out how to build it with the criteria I am
looking for. I am pretty new to Access.

 

[Michel Walsh]

You can make a criteria with more than one condition. It may be easier to do
it in SQL view, though. As example, you already know how to get the data
for a range of dates:


.... WHERE dateTimeFIeld BETWEEN firstDate AND lastDate


You can have a criteria to get only dates that are not a Saturday:

..... WHERE DatePart("w", dateTimeField) <> 7


since DatePart("w", someDate) returns the week day number (1 == Sunday, 7==
Saturday ).


If you need both conditions, at the same time, write the two conditions,
separated with an AND conjunction:


.... WHERE ( dateTimeFIeld BETWEEN firstDate AND lastDate )
AND ( DatePart("w", dateTimeField) <> 7 )


(you would use an OR if either of the two conditions fit your goal).


You can then switch back in design view to see how you could have done it,
graphically.


If you mix AND and OR conjunction, be aware to use parenthesis to properly
define the order of execution.

( a OR b ) AND c
is not the same as
a OR ( b AND c )
as example.



Hoping it may help,
Vanderghast, Access MVP

 

[Todd]

Well, since I don't know SQL yet, I am having some difficulty understanding
all that you stated (some of it makes sense). I am trying to relate this to
my situation.

Let's say I want to query a field to display results from Oct 3 and 4, Nov 5
and 6, and Dec 5 and 6, but I want a user to run the query and have the
dialog box come up so that they can enter the specific dates to query for
each of these 3 months. Could you write a sql statement that would do this so
I can try to make sense of it? I sure do appreciate your help.

Todd
--
Todd

You can make a criteria with more than one condition. It may be easier to do
it in SQL view, though. As example, you already know how to get the data
for a range of dates:


.... WHERE dateTimeFIeld BETWEEN firstDate AND lastDate


You can have a criteria to get only dates that are not a Saturday:

..... WHERE DatePart("w", dateTimeField) <> 7


since DatePart("w", someDate) returns the week day number (1 == Sunday, 7==
Saturday ).


If you need both conditions, at the same time, write the two conditions,
separated with an AND conjunction:


.... WHERE ( dateTimeFIeld BETWEEN firstDate AND lastDate )
AND ( DatePart("w", dateTimeField) <> 7 )


(you would use an OR if either of the two conditions fit your goal).


You can then switch back in design view to see how you could have done it,
graphically.


If you mix AND and OR conjunction, be aware to use parenthesis to properly
define the order of execution.

( a OR b ) AND c
is not the same as
a OR ( b AND c )
as example.



Hoping it may help,
Vanderghast, Access MVP

I am trying to build a report based on a query that will show client
activity
for specific dates over a 3 month period. Is there a way to build the
criteria using expression builder?

Example: I want to find activity for a specific week in October, November,
and December for each client. I want the user to be able to input the
dates
for each of these months. I am familiar with the "Between [StartDate] and
[EndDate]". I just can't figure out how to build it with the criteria I am
looking for. I am pretty new to Access.

 

[Michel Walsh]

This is a special case. If you always have 6 dates exactly, you can use


SELECT *
FROM yourTableNameHere
WHERE (
dateTimeField >= CDate(FORMS!FormName!FirstStartingDate)
AND dateTimeField < 1+
CDate(FORMS!FormName!FirstEndingDate)
)
OR
(
dateTimeField >= CDate(FORMS!FormName!SecondStartingDate)
AND dateTimeField < 1+
CDate(FORMS!FormName!SecondEndingDate)
)
OR
(
dateTimeField >= CDate(FORMS!FormName!ThirdStartingDate)
AND dateTimeField < 1+
CDate(FORMS!FormName!ThirdEndingDate)
)




in SQL view. Words I typed all in cap are key words to be typed as they
appear. Other words have to be changed to suit your situation.
FirstStartingDate and similar names are for the text box control on your
form supplying these values.



Hoping it may help,
Vanderghast, Access MVP

Well, since I don't know SQL yet, I am having some difficulty
understanding
all that you stated (some of it makes sense). I am trying to relate this
to
my situation.

Let's say I want to query a field to display results from Oct 3 and 4, Nov
5
and 6, and Dec 5 and 6, but I want a user to run the query and have the
dialog box come up so that they can enter the specific dates to query for
each of these 3 months. Could you write a sql statement that would do this
so
I can try to make sense of it? I sure do appreciate your help.

Todd
--
Todd

You can make a criteria with more than one condition. It may be easier to
do
it in SQL view, though. As example, you already know how to get the data
for a range of dates:


.... WHERE dateTimeFIeld BETWEEN firstDate AND lastDate


You can have a criteria to get only dates that are not a Saturday:

..... WHERE DatePart("w", dateTimeField) <> 7


since DatePart("w", someDate) returns the week day number (1 == Sunday,
7==
Saturday ).


If you need both conditions, at the same time, write the two conditions,
separated with an AND conjunction:


.... WHERE ( dateTimeFIeld BETWEEN firstDate AND lastDate )
AND ( DatePart("w", dateTimeField) <> 7 )


(you would use an OR if either of the two conditions fit your goal).


You can then switch back in design view to see how you could have done
it,
graphically.


If you mix AND and OR conjunction, be aware to use parenthesis to
properly
define the order of execution.

( a OR b ) AND c
is not the same as
a OR ( b AND c )
as example.



Hoping it may help,
Vanderghast, Access MVP

I am trying to build a report based on a query that will show client
activity
for specific dates over a 3 month period. Is there a way to build the
criteria using expression builder?

Example: I want to find activity for a specific week in October,
November,
and December for each client. I want the user to be able to input the
dates
for each of these months. I am familiar with the "Between [StartDate]
and
[EndDate]". I just can't figure out how to build it with the criteria I
am
looking for. I am pretty new to Access.

 

[I don''''t know??]

Are you looking to use the built in query function in acccess?