Date Criteria Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that groups records by Client and uses the date entered by the
user which all works fine, but I also want to show the last entered date
prior to the date entered by the user for each client that has an entry on
the date entered by user.
This is my query

SELECT tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
FROM tblClientDetails INNER JOIN tblTrainingSessions ON
tblClientDetails.ClientID = tblTrainingSessions.ClientID
GROUP BY tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory, tblClientDetails.ClientGoals,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
HAVING
(((tblTrainingSessions.Date)=[Forms]![frmPrintClientPreSessionReport]![SessionDate]))
ORDER BY tblTrainingSessions.Date;
hope someone can help
Thanks
Rodney
 
Rodney said:
I have a query that groups records by Client and uses the date entered by
the
user which all works fine, but I also want to show the last entered date
prior to the date entered by the user for each client that has an entry on
the date entered by user.
This is my query

SELECT tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
FROM tblClientDetails INNER JOIN tblTrainingSessions ON
tblClientDetails.ClientID = tblTrainingSessions.ClientID
GROUP BY tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory, tblClientDetails.ClientGoals,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
HAVING
(((tblTrainingSessions.Date)=[Forms]![frmPrintClientPreSessionReport]![SessionDate]))
ORDER BY tblTrainingSessions.Date;
hope someone can help
Thanks
Rodney
Click to expand...

here might be...

I'm sorry but I have to get to work...

I've separated out a correlated subquery for you
but I don't have time to test and I cannot remember
if wrapping in First aggregate will work for you...

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes,
First(

(SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate])

) As LatestPriorDate
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID =T.ClientID
GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
HAVING
T.[Date]=[Forms]![frmPrintClientPreSessionReport]![SessionDate]
ORDER BY
T.Date;
 
actually...I'd probably just create a separate query

qryLatestPriorDate

SELECT
q.ClientID,
Max(q.[Date]) As LatestPriorDate
FROM
tblTrainingSessions As q
WHERE
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate]
GROUP BY
q.ClientID;

then join this prequery to original query...

also...I wonder if you won't get better performance
if you move "HAVING" clause to the "WHERE" clause...

Gary Walter said:
Rodney said:
I have a query that groups records by Client and uses the date entered by
the
user which all works fine, but I also want to show the last entered date
prior to the date entered by the user for each client that has an entry
on
the date entered by user.
This is my query

SELECT tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
FROM tblClientDetails INNER JOIN tblTrainingSessions ON
tblClientDetails.ClientID = tblTrainingSessions.ClientID
GROUP BY tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory, tblClientDetails.ClientGoals,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
HAVING
(((tblTrainingSessions.Date)=[Forms]![frmPrintClientPreSessionReport]![SessionDate]))
ORDER BY tblTrainingSessions.Date;
hope someone can help
Thanks
Rodney
Click to expand...

here might be...

I'm sorry but I have to get to work...

I've separated out a correlated subquery for you
but I don't have time to test and I cannot remember
if wrapping in First aggregate will work for you...

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes,
First(

(SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate])

) As LatestPriorDate
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID =T.ClientID
GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
HAVING
T.[Date]=[Forms]![frmPrintClientPreSessionReport]![SessionDate]
ORDER BY
T.Date;
Click to expand...
 
Thanks Gary I will try it out today

Rodney

Gary Walter said:
actually...I'd probably just create a separate query

qryLatestPriorDate

SELECT
q.ClientID,
Max(q.[Date]) As LatestPriorDate
FROM
tblTrainingSessions As q
WHERE
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate]
GROUP BY
q.ClientID;

then join this prequery to original query...

also...I wonder if you won't get better performance
if you move "HAVING" clause to the "WHERE" clause...

Gary Walter said:
Rodney said:
I have a query that groups records by Client and uses the date entered by
the
user which all works fine, but I also want to show the last entered date
prior to the date entered by the user for each client that has an entry
on
the date entered by user.
This is my query

SELECT tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
FROM tblClientDetails INNER JOIN tblTrainingSessions ON
tblClientDetails.ClientID = tblTrainingSessions.ClientID
GROUP BY tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory, tblClientDetails.ClientGoals,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
HAVING
(((tblTrainingSessions.Date)=[Forms]![frmPrintClientPreSessionReport]![SessionDate]))
ORDER BY tblTrainingSessions.Date;
hope someone can help
Thanks
Rodney
Click to expand...

here might be...

I'm sorry but I have to get to work...

I've separated out a correlated subquery for you
but I don't have time to test and I cannot remember
if wrapping in First aggregate will work for you...

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes,
First(

(SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate])

) As LatestPriorDate
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID =T.ClientID
GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
HAVING
T.[Date]=[Forms]![frmPrintClientPreSessionReport]![SessionDate]
ORDER BY
T.Date;
Click to expand...
Click to expand...
 
Gary,
Thanks, I have tryed your suggestions, the first one returns 0 records and
the second one works but it show all previous record to the date entered by
the user.
What I would like is
If a client has a training session for 07/0307 & 09/03/07 & 10/03/07
and the user enters 10/03/07 then I want the 10th & 09th to show but not the
07th
If the client dosen't have a record for the 10th then I don't want any
record for that client to show.

Thanks heaps for your help
Rodney

Gary Walter said:
actually...I'd probably just create a separate query

qryLatestPriorDate

SELECT
q.ClientID,
Max(q.[Date]) As LatestPriorDate
FROM
tblTrainingSessions As q
WHERE
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate]
GROUP BY
q.ClientID;

then join this prequery to original query...

also...I wonder if you won't get better performance
if you move "HAVING" clause to the "WHERE" clause...

Gary Walter said:
Rodney said:
I have a query that groups records by Client and uses the date entered by
the
user which all works fine, but I also want to show the last entered date
prior to the date entered by the user for each client that has an entry
on
the date entered by user.
This is my query

SELECT tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
FROM tblClientDetails INNER JOIN tblTrainingSessions ON
tblClientDetails.ClientID = tblTrainingSessions.ClientID
GROUP BY tblClientDetails.ClientID, tblClientDetails.ClientFirstName,
tblClientDetails.ClientLastName, tblClientDetails.ClientHeightOnStart,
tblClientDetails.ClientChestOnStart, tblClientDetails.ClientWaistOnStart,
tblClientDetails.ClientHipsOnStart, tblClientDetails.ClientWeightOnStart,
tblClientDetails.ClientMedicalHistory, tblClientDetails.ClientGoals,
tblClientDetails.ClientEmergencyContact, tblTrainingSessions.Date,
tblTrainingSessions.ClientChest, tblTrainingSessions.ClientWaist,
tblTrainingSessions.ClientHips, tblTrainingSessions.ClientCurrentWieght,
tblTrainingSessions.TrainingSessionNotes
HAVING
(((tblTrainingSessions.Date)=[Forms]![frmPrintClientPreSessionReport]![SessionDate]))
ORDER BY tblTrainingSessions.Date;
hope someone can help
Thanks
Rodney
Click to expand...

here might be...

I'm sorry but I have to get to work...

I've separated out a correlated subquery for you
but I don't have time to test and I cannot remember
if wrapping in First aggregate will work for you...

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes,
First(

(SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<[Forms]![frmPrintClientPreSessionReport]![SessionDate])

) As LatestPriorDate
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID =T.ClientID
GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
HAVING
T.[Date]=[Forms]![frmPrintClientPreSessionReport]![SessionDate]
ORDER BY
T.Date;
Click to expand...
Click to expand...
 
Rodney said:
Gary,
Thanks, I have tryed your suggestions, the first one returns 0 records and
the second one works but it show all previous record to the date entered
by
the user.
What I would like is
If a client has a training session for 07/0307 & 09/03/07 & 10/03/07
and the user enters 10/03/07 then I want the 10th & 09th to show but not
the
07th
If the client dosen't have a record for the 10th then I don't want any
record for that client to show.
Click to expand...

First, you said previously that the query worked fine,
but you are using non-US format dates in your example.
I assume if it was working fine, then you have some
mechanism for converting your parameter to US-format
(or even better "yyyy-mm-dd")?

Allen covers everything here:
http://allenbrowne.com/ser-36.html

Neverless, that has nothing to do with the fact that
I completely misunderstood what it was you wanted.

I thought you just wanted to "show" that previous date.

It appears that you also wanted the record for that date
as well (unless I have misunderstood again).

I would save Allen's SQLDate() function in a separate
code module (be sure to add "Public" to start of first
line of code, i.e.,

'*** quote***
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively
by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function'*** unquote***Save it in a new module (say "modAllen").then,
this is what I would
try:SELECTC.ClientID,C.ClientFirstName,C.ClientLastName,C.ClientHeightOnStart,C.ClientChestOnStart,C.ClientWaistOnStart,C.ClientHipsOnStart,C.ClientWeightOnStart,C.ClientMedicalHistory,C.ClientEmergencyContact,T.[Date],T.ClientChest,T.ClientWaist,T.ClientHips,T.ClientCurrentWieght,T.TrainingSessionNotesFROMtblClientDetails
As CINNER JOINtblTrainingSessions As TONC.ClientID
=T.ClientIDWHERET.[Date]=SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])ORT.[Date]=(SELECTMax(q.[Date])FROMtblTrainingSessions
As
qWHEREq.ClientID=C.ClientIDANDq.[Date]<SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate]))GROUP
BYC.ClientID,C.ClientFirstName,C.ClientLastName,C.ClientHeightOnStart,C.ClientChestOnStart,C.ClientWaistOnStart,C.ClientHipsOnStart,C.ClientWeightOnStart,C.ClientMedicalHistory,C.ClientGoals,C.ClientEmergencyContact,T.[Date],T.ClientChest,T.ClientWaist,T.ClientHips,T.ClientCurrentWieght,T.TrainingSessionNotesORDER
BYT.[Date];
 
Rodney said:
Gary,
Thanks, I have tryed your suggestions, the first one returns 0 records and
the second one works but it show all previous record to the date entered
by the user.
What I would like is
If a client has a training session for 07/0307 & 09/03/07 & 10/03/07
and the user enters 10/03/07 then I want the 10th & 09th to show but not
the 07th
If the client dosen't have a record for the 10th then I don't want any
record for that client to show.
Click to expand...

First, you said previously that the query worked fine,
but you are using non-US format dates in your example.
I assume if it was working fine, then you have some
mechanism for converting your parameter to US-format
(or even better "yyyy-mm-dd")?

Allen covers everything here:
http://allenbrowne.com/ser-36.html

Neverless, that has nothing to do with the fact that
I completely misunderstood what it was you wanted.

I thought you just wanted to "show" that previous date.

It appears that you also wanted the record for that date
as well (unless I have misunderstood again).

I would save Allen's SQLDate() function in a separate
code module (be sure to add "Public" to start of first
line of code, i.e.,

'*** quote***
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively
' by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
' component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
'*** unquote***

Save it in a new module (say "modAllen").then,
this is what I would try:

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID=T.ClientID
WHERE

T.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])

OR

T.[Date]=
(
SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes

HAVING

C.ClientID IN
(
SELECT
q.ClientID
FROM
tblTrainingSessions As q
WHERE
q.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

ORDER BY
T.[Date];
 
Gary Hi
Thanks for your reply again.
Your question about the date format,
I have the tables and forms set to short date format which acepts dd mm yy
and converts it to dd/mm/yyyy which is the standard date/time format
English-Australia.
I have not had any problems in the past with this format, do I need to have
it converted to yyyy/mm/dd.

Rodney

Gary Walter said:
Rodney said:
Gary,
Thanks, I have tryed your suggestions, the first one returns 0 records and
the second one works but it show all previous record to the date entered
by the user.
What I would like is
If a client has a training session for 07/0307 & 09/03/07 & 10/03/07
and the user enters 10/03/07 then I want the 10th & 09th to show but not
the 07th
If the client dosen't have a record for the 10th then I don't want any
record for that client to show.
Click to expand...

First, you said previously that the query worked fine,
but you are using non-US format dates in your example.
I assume if it was working fine, then you have some
mechanism for converting your parameter to US-format
(or even better "yyyy-mm-dd")?

Allen covers everything here:
http://allenbrowne.com/ser-36.html

Neverless, that has nothing to do with the fact that
I completely misunderstood what it was you wanted.

I thought you just wanted to "show" that previous date.

It appears that you also wanted the record for that date
as well (unless I have misunderstood again).

I would save Allen's SQLDate() function in a separate
code module (be sure to add "Public" to start of first
line of code, i.e.,

'*** quote***
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively
' by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
' component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
'*** unquote***

Save it in a new module (say "modAllen").then,
this is what I would try:

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID=T.ClientID
WHERE

T.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])

OR

T.[Date]=
(
SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes

HAVING

C.ClientID IN
(
SELECT
q.ClientID
FROM
tblTrainingSessions As q
WHERE
q.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

ORDER BY
T.[Date];
Click to expand...
 
I assumed you had a field "Date"
(which is an Access reserved word
and truly should not be used as a
field name) that was type Date/Time.

Please verify that first.

If so, I suggest reading Allen Browne's
discussion in the link I gave you. He has
the real-world experience in this regard
(he knows what he is talking about). I,
on the other hand, work for a US company
where this never comes up, and am only
parrotting what I believe to be the case.

If it is not a Date/Time type, why not? You
cannot sort *date strings* properly, i.e., the
"hierarchy" becomes a string sort, not a date sort...

in the "string world"

"3/3/2007" is not between "1/1/2007" and
"12/12/2007"

"3/3/2007" is equal to "3/3/2007"

but

"12/12/2007" is not > "3/3/2007"

A Date/Time field value is *stored* as a float number,
irrespective of what your International settings
are, or how you *format* that float value when you
enter it or display it.

Does that make sense?

Please read Allen's excellent discussion.

Rodney said:
Your question about the date format,
I have the tables and forms set to short date format which acepts dd mm yy
and converts it to dd/mm/yyyy which is the standard date/time format
English-Australia.
I have not had any problems in the past with this format, do I need to
have
it converted to yyyy/mm/dd.

Rodney

First, you said previously that the query worked fine,
but you are using non-US format dates in your example.
I assume if it was working fine, then you have some
mechanism for converting your parameter to US-format
(or even better "yyyy-mm-dd")?

Allen covers everything here:
http://allenbrowne.com/ser-36.html

Neverless, that has nothing to do with the fact that
I completely misunderstood what it was you wanted.

I thought you just wanted to "show" that previous date.

It appears that you also wanted the record for that date
as well (unless I have misunderstood again).

I would save Allen's SQLDate() function in a separate
code module (be sure to add "Public" to start of first
line of code, i.e.,

'*** quote***
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used
natively
' by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
' component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
'*** unquote***

Save it in a new module (say "modAllen").then,
this is what I would try:

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID=T.ClientID
WHERE

T.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])

OR

T.[Date]=
(
SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes

HAVING

C.ClientID IN
(
SELECT
q.ClientID
FROM
tblTrainingSessions As q
WHERE
q.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

ORDER BY
T.[Date];
Click to expand...
Click to expand...
 
Gary,
Yes the date type is date/time and yes I had named the field Date (which I
hadn't noticed) I have renamed it ClientSessionDate (which it should have
been all along).
I will have another read of Allens discussions.

Thank you very much for your help it is much appreciated.

Rodney

Gary Walter said:
I assumed you had a field "Date"
(which is an Access reserved word
and truly should not be used as a
field name) that was type Date/Time.

Please verify that first.

If so, I suggest reading Allen Browne's
discussion in the link I gave you. He has
the real-world experience in this regard
(he knows what he is talking about). I,
on the other hand, work for a US company
where this never comes up, and am only
parrotting what I believe to be the case.

If it is not a Date/Time type, why not? You
cannot sort *date strings* properly, i.e., the
"hierarchy" becomes a string sort, not a date sort...

in the "string world"

"3/3/2007" is not between "1/1/2007" and
"12/12/2007"

"3/3/2007" is equal to "3/3/2007"

but

"12/12/2007" is not > "3/3/2007"

A Date/Time field value is *stored* as a float number,
irrespective of what your International settings
are, or how you *format* that float value when you
enter it or display it.

Does that make sense?

Please read Allen's excellent discussion.

Rodney said:
Your question about the date format,
I have the tables and forms set to short date format which acepts dd mm yy
and converts it to dd/mm/yyyy which is the standard date/time format
English-Australia.
I have not had any problems in the past with this format, do I need to
have
it converted to yyyy/mm/dd.

Rodney

First, you said previously that the query worked fine,
but you are using non-US format dates in your example.
I assume if it was working fine, then you have some
mechanism for converting your parameter to US-format
(or even better "yyyy-mm-dd")?

Allen covers everything here:
http://allenbrowne.com/ser-36.html

Neverless, that has nothing to do with the fact that
I completely misunderstood what it was you wanted.

I thought you just wanted to "show" that previous date.

It appears that you also wanted the record for that date
as well (unless I have misunderstood again).

I would save Allen's SQLDate() function in a separate
code module (be sure to add "Public" to start of first
line of code, i.e.,

'*** quote***
Public Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used
natively
' by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
' component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
'*** unquote***

Save it in a new module (say "modAllen").then,
this is what I would try:

SELECT
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes
FROM
tblClientDetails As C
INNER JOIN
tblTrainingSessions As T
ON
C.ClientID=T.ClientID
WHERE

T.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])

OR

T.[Date]=
(
SELECT
Max(q.[Date])
FROM
tblTrainingSessions As q
WHERE
q.ClientID=C.ClientID
AND
q.[Date]<
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

GROUP BY
C.ClientID,
C.ClientFirstName,
C.ClientLastName,
C.ClientHeightOnStart,
C.ClientChestOnStart,
C.ClientWaistOnStart,
C.ClientHipsOnStart,
C.ClientWeightOnStart,
C.ClientMedicalHistory,
C.ClientGoals,
C.ClientEmergencyContact,
T.[Date],
T.ClientChest,
T.ClientWaist,
T.ClientHips,
T.ClientCurrentWieght,
T.TrainingSessionNotes

HAVING

C.ClientID IN
(
SELECT
q.ClientID
FROM
tblTrainingSessions As q
WHERE
q.[Date]=
SQLDate([Forms]![frmPrintClientPreSessionReport]![SessionDate])
)

ORDER BY
T.[Date];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top