[AccessD] Query oddity

The problem is if the table date is a full date with time. If you try
the exact date you will either lose all those dates or 1/2 depending on
when the time occurs. I ran into this against an ORACLE db.  
 
You can do two things

1) This is what I generally use in both straight access to oracle and
passthrus. It works and saves me pain
*   select where the date is one day prior and after
          Where (Tbldt > #03/02/2007# and TblDt < #03/04/2007# ) 
          WHERE tblStaffTime.fldStfID =[Forms]![frmEnterTime]![txtStfID]

           AND (tblStaffTime.fldTimeDate >
([Forms]![frmEnterTime]![txtTimeDate] -1)
             and  tblStaffTime.fldTimeDate <
([Forms]![frmEnterTime]![txtTimeDate] + 1;

2) use this function FormatDateTime - I tried it against an Access table
and it worked
   *  add an extra column to your query gui without show checked
       *  In the field portion put  FormatDateTime(fldTimeDate, 2)
       *  In the criteria put = [Forms]![frmEnterTime]![txtTimeDate] 
   *  Actual sql should look like 
        *  WHERE tblStaffTime.fldStfID
=[Forms]![frmEnterTime]![txtStfID]    
           AND (FormatDateTime(tblStaffTime.fldTimeDate,2) =
([Forms]![frmEnterTime]![txtTimeDate])

HTH
Patti      
**************************************************
* Patricia O'Connor
* Associate Computer Programmer Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at nysemail.state.ny.us
**************************************************
 

> 
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow
> Sent: Thursday, October 25, 2007 11:29 PM
> To: _DBA-Access
> Subject: [AccessD] Query oddity
> 
> 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
> 
>