Re: Date Criteria Question

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

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


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. allen@xxxxxxxxxxxxxxx, 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];



.

Relevant Pages

  • Re: Date Criteria Question
    ... Public Function SQLDateAs String ... Return a delimited string in the date format used natively ... tblTrainingSessions As T ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... code module (be sure to add "Public" to start of first ... Public Function SQLDateAs String ... Return a delimited string in the date format used natively ...
    (microsoft.public.access.queries)
  • Re: A Date list box for Month selection and separte one for year sele.
    ... set the ColumnCount to 2 and set the RowSource to: ... Finally, if you go for the number field in yyyymm format, you will find ... Public Function DateToMonth(dt as Date) as Long ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Visual basic problem in Access 2000/XP
    ... just assign the controls/fields/variables the ... You can set the text box's Format property to ... Public Function CalcDueAs Date ... Dim DtDue As Date ...
    (microsoft.public.access.formscoding)
  • Re: How to pass a DateTime value to a WebMethod
    ... my webservice the date to be passed using that format. ... > Public Function TimeCodeMakerAs String ... > Public Function TimeCodeDecoderAs DateTime ...
    (microsoft.public.dotnet.framework.webservices)
Loading