Query for a range of records

[leot]

I have a table of salesmen and the contacts they make.

SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

Above will get me all contacts made by salesmen for a particular month
(between dates). How can I change the above so that I get all records are
included for contacts in a particular month whether the contacts were
initiated before the First Date or ended after the Last Date?

In other words, the salesman gets credit for contacts initiated before the
first of the month and ending after the last of the month as long as contacts
also occurred during the month in question. I would like a list of all
records of contacts that occurred in a chosen month whether they started
before the month or ended after the month.

Thanks for any help! I am stumped.

 

[KARL DEWEY]

Try this --
SELECT data.salesmen, data.locationofcontact, Format(CVDate([Enter date]),
"yyyymm") AS X
FROM data.salesmen
WHERE Format(CVDate([Enter date]), "yyyymm") Between Format(data.[first
date], "yyyymm") AND Format(data.[last date], "yyyymm");

 

[Ryan]

The way I handle this kind of issue is to add another column to your table
called LastContactDate. Then you would need to determin what would
constitute a contact on your form and write an event that would add the
current date to the LastContactDate column. For example, you could add a
button on your form called Contacted, and the code for the button would look
like this

Private Sub Contacted_Click()
Me.LastContactDate = Date()
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

Then in your change to this
SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [LastContactDate]) Between [Enter From Date] And [Enter To
Date]))
ORDER BY data. [LastContactDate];

The button is just one example, you could use many different events to
trigger the update of the LastContactDate column.

 

[leot]

The "Dewey" method returns all records. I don't understand how the "Ryan"
method would work either? I any case I have about 500,000 records and don't
know how to add a relavant new column to each row.

SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

I want to write a query that returns all records for a month (say September)
when first date could be prior to September (August or June or March) and
last date can be December or whatever.

Again I believe I am missing something about access and queries. Is this
even possible? Thanks for any suggestions.

 

[KARL DEWEY]

It works for me except I see a typo in my post. Try this --
SELECT data.salesmen, data.locationofcontact, Format(CVDate([Enter date]),
"yyyymm") AS X
FROM data
WHERE Format(CVDate([Enter date]), "yyyymm") Between Format(data.[first
date], "yyyymm") AND Format(data.[last date], "yyyymm");

--
KARL DEWEY
Build a little - Test a little

The "Dewey" method returns all records. I don't understand how the "Ryan"
method would work either? I any case I have about 500,000 records and don't
know how to add a relavant new column to each row.

SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

I want to write a query that returns all records for a month (say September)
when first date could be prior to September (August or June or March) and
last date can be December or whatever.

Again I believe I am missing something about access and queries. Is this
even possible? Thanks for any suggestions.

 

[leot]

Isn't there some way to use:
where (first date is between [enter] and [enter] OR (last date is between
[enter] and [enter]

I just can't get it to work? Thanks for any suggestions that work!

 

[John W. Vinson]

Isn't there some way to use:
where (first date is between [enter] and [enter] OR (last date is between
[enter] and [enter]

I just can't get it to work? Thanks for any suggestions that work!

You need separate prompts for the start and end. If you use [enter] four
times, you will get prompted only once and it will use the same value for all
four criteria.

I'd really recommend using an unbound Form for the criteria, and SQL like

[First Date] >= [Forms]![YourForm]![txtStartDate] AND [First Date] <=
[Forms]![YourForm]!txtEndDate]

It would help quite a bit if you would post the actual SQL you're using; the
names of your fields; and the nature of the "not work" you're experiencing.

 

[leot]

Isn't there some way to use:
where (first date is between [enter] and [enter] OR (last date is between
[enter] and [enter]

I just can't get it to work? Thanks for any suggestions that work!

You need separate prompts for the start and end. If you use [enter] four
times, you will get prompted only once and it will use the same value for all
four criteria.

I'd really recommend using an unbound Form for the criteria, and SQL like

[First Date] >= [Forms]![YourForm]![txtStartDate] AND [First Date] <=
[Forms]![YourForm]!txtEndDate]

It would help quite a bit if you would post the actual SQL you're using; the
names of your fields; and the nature of the "not work" you're experiencing.

I use:

SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

Changed to
SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date])
OR [Last Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

did not work, returns no records

SELECT data.salesmen, data.locationofcontact, Format(CVDate([Enter date]),
"yyyymm") AS X
FROM data
WHERE Format(CVDate([Enter date]), "yyyymm") Between Format(data.[first
date], "yyyymm") AND Format(data.[last date], "yyyymm");

returns all records

I didn't want to admit that while I wrote a form to enter data, I find that
entering data directly into my table is faster. I just open table go to
saleman and change last date (for contacts). A sin for access users, but I
find it faster to not even open the form.

 

[leot]

Concerning my last post, it just occurred to me that you were talking about a
report form. I was referring to a form to enter data. I don't have a report
form, but use sql in a query to create a table. I am under the weather and
not thinking very straight, but still trying to get work done. The only
solution I can see is to create two queries. One for "first date" of month
in question. One for "last date". Then combine the two queries leaving out
duplicates?

 

[John W. Vinson]

Concerning my last post, it just occurred to me that you were talking about a
report form. I was referring to a form to enter data. I don't have a report
form, but use sql in a query to create a table. I am under the weather and
not thinking very straight, but still trying to get work done. The only
solution I can see is to create two queries. One for "first date" of month
in question. One for "last date". Then combine the two queries leaving out
duplicates?

I still don't understand.

What is in your table, in the First Date and Last Date fields?

What kind of criteria do you want to enter - a single date, and return all
records where that date is between First Date and Last Date? Or a range of
dates, and you want to return all records where the range of dates in the
table overlaps the range of dates in the criteria? Or what?

 

[leot]

What is in your table, in the First Date and Last Date fields?

First Date Field [contains date of first contact with client]
Last Date Field [contains date of last contact with client]

dates are: ddmmyyyy

What kind of criteria do you want to enter - a single date, and return all
records where that date is between First Date and Last Date? Or a range of
dates, and you want to return all records where the range of dates in the
table overlaps the range of dates in the criteria? Or what?
--

I would like to return all records of salesmen that contacted clients
where[first date] is between(first and last days of month) and/or [last
date] falls in same month, but whose first date may be before the month in
question.

SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

 

[John Spencer]

Parameters [Enter From Date] Datetime,
[Enter to Date] DateTime;
SELECT data.salesmen
, data.[first date]
, data.[last date]
, data.locationofcontact
FROM data.salesmen
WHERE data.[First Date] Between [Enter From Date] And [Enter To Date]
OR [Last Date] Between [Enter From Date] And [Enter To Date]
ORDER BY data.[First Date]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

What is in your table, in the First Date and Last Date fields?

First Date Field [contains date of first contact with client]
Last Date Field [contains date of last contact with client]

dates are: ddmmyyyy

What kind of criteria do you want to enter - a single date, and return all
records where that date is between First Date and Last Date? Or a range of
dates, and you want to return all records where the range of dates in the
table overlaps the range of dates in the criteria? Or what?
--

I would like to return all records of salesmen that contacted clients
where[first date] is between(first and last days of month) and/or [last
date] falls in same month, but whose first date may be before the month in
question.

SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

 

[leot]

The below appears to work. Thanks

However....why does it work? It asks for the entering of the [first date]
between [enter from date] and [enter to date] which I understand. but then it
only asks for one additional date? why not two [last date] between [enter
from date] and [enter to date]??? It appears that I get the same results no
matter what I enter into the third parameter box??? That doesn't make sense
to me, just trying to understand for future queries I make? Why doesn't it
ask for a fourth parameter. Or if it is using the two [last date] parameters
as the same for the two [first date] parameters, why does it ask for a third
parameter???

Furthermore, why doesn't the following work?

Parameters [Enter From Date] Datetime,
[Enter to Date] DateTime;
SELECT data.salesmen
, data.[first date]
, data.[last date]
, data.locationofcontact
FROM data.salesmen
WHERE data.[First Date] Between [Enter From Date] And [Enter To Date]
OR [Last Date] = [Month]([First Date])
ORDER BY data.[First Date]


Below does appear to work.

 

[John W. Vinson]

The below appears to work. Thanks

However....why does it work? It asks for the entering of the [first date]
between [enter from date] and [enter to date] which I understand. but then it
only asks for one additional date? why not two [last date] between [enter
from date] and [enter to date]??? It appears that I get the same results no
matter what I enter into the third parameter box??? That doesn't make sense
to me, just trying to understand for future queries I make? Why doesn't it
ask for a fourth parameter. Or if it is using the two [last date] parameters
as the same for the two [first date] parameters, why does it ask for a third
parameter???

You won't get prompted if the value in brackets is recognized as a table
fieldname. You have not posted the names of the fields in your table, so
nobody here can answer your question accurately - *we cannot see your
database*.

 

[leot]

You won't get prompted if the value in brackets is recognized as a table
fieldname. You have not posted the names of the fields in your table, so
nobody here can answer your question accurately - *we cannot see your
database*.
--

I thought I indicated my database with
SELECT data.salesmen, data. [first date], data. [last date],
data.locationofcontact
FROM data.salesmen
WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

database:

(salesman) (first date) (last date) (locationofcontact)
number 1 3/1/2008 3/29/2008 colorado
number 2 3/3/2008 4/2/2008 utah
number 3 2/28/2008 3/30/2008 colorado
number 1 3/2/2008 3/17/2008 wyoming
number 4 4/1/2008 4/28/2008 california

etc.
in above case for march, 2008 I want all records except number 4 as result
of query.

 

[John W. Vinson]

You won't get prompted if the value in brackets is recognized as a table
fieldname. You have not posted the names of the fields in your table, so
nobody here can answer your question accurately - *we cannot see your
database*.
--

I thought I indicated my database with
SELECT data.salesmen, data. [first date], data. [last date],

This CANNOT be correct as posted. Access will barf on the blank following
"data. ". Please open the query in SQL view and copy and paste the actual
query string.

data.locationofcontact
FROM data.salesmen

and this cannot be correct either; the FROM clause must refer to a tablename,
not to a tablename.fieldname.

WHERE (((data. [First Date]) Between [Enter From Date] And [Enter To Date]))
ORDER BY data. [First Date];

database:

(salesman) (first date) (last date) (locationofcontact)
number 1 3/1/2008 3/29/2008 colorado
number 2 3/3/2008 4/2/2008 utah
number 3 2/28/2008 3/30/2008 colorado
number 1 3/2/2008 3/17/2008 wyoming
number 4 4/1/2008 4/28/2008 california

etc.
in above case for march, 2008 I want all records except number 4 as result
of query.

so you want only those records for which any date in the month of March, from
March 1 through March 31, falls between [First Date] and [Last Date]? Any
overlap?

If so, try

SELECT data.salesmen, data.[first date], data.[last date],
data.locationofcontact
FROM data
WHERE data.[Last Date]) >=
DateSerial(Year([Enter a date in desired month:], Month([Enter a date in
desired month:], 1)
AND data.[First Date] <
DateSerial(Year([Enter a date in desired month:], Month([Enter a date in
desired month:]+1, 1)
ORDER BY data. [First Date];

 

[leot]

(salesman) (first date) (last date) (locationofcontact)

number 1 3/1/2008 3/29/2008 colorado
number 2 3/3/2008 4/2/2008 utah
number 3 2/28/2008 3/30/2008 colorado
number 1 3/2/2008 3/17/2008 wyoming
number 4 4/1/2008 4/28/2008 california

etc.
in above case for march, 2008 I want all records except number 4 as result
of query.

so you want only those records for which any date in the month of March, from
March 1 through March 31, falls between [First Date] and [Last Date]? Any
overlap?

So now I am getting confused. In the example of March, I want all records where the [first date] is between 3/1 and 3/31 and any records where the [last date] also occurs in March. There are records where the last date is in march but the first date is feb or jan or etc. And there are records where the [first date] occurs in March but last date is april, may, or to be determined later (but not void. [last date] can change later in history. I will try below.

If so, try

SELECT data.salesmen, data.[first date], data.[last date],
data.locationofcontact
FROM data
WHERE data.[Last Date]) >=
DateSerial(Year([Enter a date in desired month:], Month([Enter a date in
desired month:], 1)
AND data.[First Date] <
DateSerial(Year([Enter a date in desired month:], Month([Enter a date in
desired month:]+1, 1)
ORDER BY data. [First Date];