Re: Date Criteria Question

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

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" wrote:
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:
<snip>
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: Time difference
    ... As John S has pointed out, a date/time data type is entirely inappropriate ... string, using the Format function, before you do any manipulation. ... Dim lSeconds As Long ...
    (microsoft.public.access.queries)
  • Re: Date Criteria Question
    ... field name) that was type Date/Time. ... If it is not a Date/Time type, ... "hierarchy" becomes a string sort, ... I have the tables and forms set to short date format which acepts dd mm yy ...
    (microsoft.public.access.queries)
  • Re: Help with query on a Date field for UK date style dd/mm/yyyy ???
    ... >format from the region settings in windows. ... regardless of the computer's date/time setting. ... >When I run the above query it says no records found. ... a string and then searching that string. ...
    (microsoft.public.access.queries)
  • Re: mm/dd/yyyy format question
    ... I was not talking about how .Net or SQL Server stores date/time values ... I get the impression that you are talking about ISO 8601 in terms ... applications use the format for unambiguously representing date/time ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Access Query dates
    ... Date/Time values stored in Access are completely locale-independent. ... internally in U.S. format -- so this is probably what is ... it SHOULD convert the string to the local ... "Running Microsoft Access 2000" ...
    (microsoft.public.access.queries)