[AccessD] Query oddity

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
> 
>