[AccessD] Query oddity

I agree. If your form is asking for just the date, then you should revise
the code to compare against just the date portion of the SQL column. Here's
a function that does this:

>code>
CREATE FUNCTION [dbo].[JustDate_fn]
  ( @date datetime )
RETURNS varchar(10)
AS
BEGIN
  RETURN ( CONVERT(varchar(10), at date,101) )
END
<./code>

While I'm at it, here's a function that returns just the time portion of a
SQL datetime column:

<code>
CREATE FUNCTION [dbo].[JustTime_fn]
  ( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
  RETURN ( CONVERT(varchar(7),right(@fDate,7),101) )
END
</code>

hth,
Arthur

On 10/26/07, Andy Lacey <andy at minstersystems.co.uk> wrote:
>
> Hi John
> I had the same thought as Anita - I'll bet it's the fact that you're
> storing
> date AND time. Not sure then though why other dates would work but then we
> don't know what you know. If it is that though an alternative to Anita's
> solution is to wrap Int() around the dates.
>
> -- Andy Lacey
> http://www.minstersystems.co.uk
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Anita Smith
> > Sent: 26 October 2007 04:41
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Query oddity
> >
> >
> > Perhaps there is time involved.
> >
> > Try formatting the field and the criteria:
> > WHERE Format(TimeDate, "dd-mmm-yyyy") =
> > Format([Forms]![frmEnterTime]![txtTimeDate], "dd-mmm-yyyy")
> >
> > Anita
> >
> >
> >
> > On 10/26/07, John Bartow <john at winhaven.net> wrote:
> > >
> > > I have recently found a couple of issues in an app
> > converted from A97
> > > to A2k3. The items of questions worked in A97 but fail in A2k3.
> > >
> > > Problem 1. )
> > >
> > > This is the SQL for the row source of a ListBox (on screen
> > aid) that
> > > basically shows the user a miniature summation of what they have
> > > already entered in a time sheet:
> > >
> > > SELECT tblStaffTime.fldTimeID, tblStaffTime.fldStfID,
> > > tblStaffTime.fldTimeDate, tblStaffTime.fldTimeHours AS Hours,
> > > tlkHourType.fldHrsType AS [Type of Hours], tlkProgram.fldProgName,
> > > tlkActType.fldActTypeCode FROM tlkProgram RIGHT JOIN (tlkHourType
> > > RIGHT JOIN (tlkActType RIGHT JOIN tblStaffTime ON
> > > tlkActType.fldActTypeID = tblStaffTime.fldActTypeID) ON
> > > tlkHourType.fldHrsTypeID = tblStaffTime.fldHrsTypeID) ON
> > > tlkProgram.fldProgID = tblStaffTime.fldProgID WHERE
> > > (((tblStaffTime.fldStfID)=[Forms]![frmEnterTime]![txtStfID]) AND
> > > ((tblStaffTime.fldTimeDate)=[Forms]![frmEnterTime]![txtTimeDate]));
> > >
> > > This works as expected EXCEPT when the date is today. Which, of
> > > course, happens to be the most usual case in end user experience.
> > >
> > > I created a separate, identical query and ran it while
> > using the form
> > > to try figure out what is going on. It reacts the same way.
> > The query
> > > always works
> > > except when the date is today. I hard coded the date into
> > the query with
> > > no
> > > better results.
> > >
> > > I'm running on empty - anyone else have an idea?
> > >
> > >
> > >
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>