Gerald New service date idea

[justsomedumbguy]

Thank so much it worked great! however I changed my mind on selecting Starting Tank & ending Tank I would like to select between dates

Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a querry named "reminder labels" that get data from

"Service tabel" & "Customer Tabel'

what i need to do is have the querry get the newest

service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date

Here is my SQL
SELECT Customers.TankID, Customers.[First Name],

Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]

FROM Customers INNER JOIN Service ON Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And [EndingTank#]));



.

Expand AllCollapse All

 

[Gerald Stanley]

If you require more assistance, please specify which column
is to be checked against the dates.

Gerald Stanley MCSD

-----Original Message-----
Thank so much it worked great! however I changed my mind

Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a querry named "reminder labels" that get data from

"Service tabel" & "Customer Tabel'

what i need to do is have the querry get the newest

service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date

Here is my SQL
SELECT Customers.TankID, Customers.[First Name],

Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]

FROM Customers INNER JOIN Service ON Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And [EndingTank#]));



.

Expand AllCollapse All
.
[/QUOTE]

 

[justsomedumbguy]

what i want is for the user to select a starting point and ending point from a range of dates in The"Next Due" Field (for example 1/1/04 to 2/1/04).

From there, i want the query to:

1) Dynamically read the user selected criteria for a starting and stopping point, incorporating this into the WHERE clause of the query

2) Display the most recent service date for each customer that has had at least one service date for the timeframe indicated by the user

If you require more assistance, please specify which column
is to be checked against the dates.

Gerald Stanley MCSD

-----Original Message-----
Thank so much it worked great! however I changed my mind

Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a querry named "reminder labels" that get data from
"Service tabel" & "Customer Tabel'
what i need to do is have the querry get the newest
service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date

Here is my SQL
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]
FROM Customers INNER JOIN Service ON
Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#]));



.

Expand AllCollapse All
.

[/QUOTE]

 

[justsomedumbguy]

what i want is for the user to select a starting point and ending point from a range of dates in The"Next Due" Field (for example 1/1/04 to 2/1/04).

From there, i want the query to:

1) Dynamically read the user selected criteria for a starting and stopping point, incorporating this into the WHERE clause of the query

2) Display the most recent service date for each customer that has had at least one service date for the timeframe indicated by the user

If you require more assistance, please specify which column
is to be checked against the dates.

Gerald Stanley MCSD

-----Original Message-----
Thank so much it worked great! however I changed my mind

Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a querry named "reminder labels" that get data from
"Service tabel" & "Customer Tabel'
what i need to do is have the querry get the newest
service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date

Here is my SQL
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]
FROM Customers INNER JOIN Service ON
Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#]));



.

Expand AllCollapse All
.

[/QUOTE]

 

[Gerald Stanley]

You will need to enclose the request dates in ## and ensure
that they in mm/dd/yyyy format.
Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((S1.[Date of Service]) Between # Format([Start
Date], 'mm/dd/yy') # And # Format([End Date], 'mm/dd/yy')
#)) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

-----Original Message-----
what i want is for the user to select a starting point and

ending point from a range of dates in The"Next Due" Field
(for example 1/1/04 to 2/1/04).

From there, i want the query to:

1) Dynamically read the user selected criteria for a

starting and stopping point, incorporating this into the
WHERE clause of the query

2) Display the most recent service date for each customer

that has had at least one service date for the timeframe
indicated by the user

If you require more assistance, please specify which column
is to be checked against the dates.

Gerald Stanley MCSD

-----Original Message-----
Thank so much it worked great! however I changed my mind

on selecting Starting Tank & ending Tank I would like to
select between dates

:

Try something along the lines of
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
S1.[Date of Service]
FROM Customers INNER JOIN Service AS S1 ON
Customers.TankID=S1.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#])) AND S1.[Date of Service] IN (SELECT
Max([Date of Service]) FROM Service WHERE Service.TankId =
S1.TankId)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a querry named "reminder labels" that get data from
"Service tabel" & "Customer Tabel'
what i need to do is have the querry get the newest
service date for multiple customers, every customer has
multiple dates. I tried DMax("Date of Service","Service")
but if john doe has a date 7/12/2004 which is the newest i
will only get that date even though joe blows newest date
is 4/25/2003 ,and i still need joes newest date

Here is my SQL
SELECT Customers.TankID, Customers.[First Name],
Customers.[Last Name], Customers.Address, Customers.City,
Customers.State, Customers.[Zip Code], Customers.[Filter?],
Service.[Date of Service]
FROM Customers INNER JOIN Service ON
Customers.TankID=Service.TankID
WHERE (((Customers.TankID) Between [Starting Tank #] And
[EndingTank#]));



.





Expand AllCollapse All
.

.