[AccessD] Query returns data for wrong time period

Another question - are you using a regular access query or a passthru?


Using Access2k3 - I tried the Cdate(fix()) against an oracle db table. I
selected the actual field and the modified one.  It worked fine.  So
then I also checked it using Access97 still worked ok.  I also wrote one
where I had to link two table to get results and that worked ok.

Then I pulled in one of our "views"  with the create_dt.  This one took
longer but still worked.

   SELECT CaseID,
               CREATE_DT,
               CrDt: cDATE(fix(CREATE_DT))
  FROM Tbl1

I can not run this in passthru because Cdate is not a valid oracle
function.  I can't get to a Sql Server table at the moment.
               
**************************************************
* 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 
> Kaup, Chester
> Sent: Wednesday, September 19, 2007 08:57 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query returns data for wrong time period
> 
> See responses below:
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> O'Connor, Patricia (OTDA)
> Sent: Tuesday, September 18, 2007 7:10 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query returns data for wrong time period
> 
> Are you saying if you use a hardcoded date in ProductionDate  example
> ProductionDate: #08/18/2007# the query works fine? 
> Correct
> 
> When you use  ProductionDate:
> CDate(Fix(dbo_V_S_Oil_Metering_Daily_Volumes!Date)) it gets wacky?
> Correct
> 
> Where is dbo_V_S_Oil_Metering_Daily_Volumes!Date located in a 
> table or form? 
> It is a view in SQL server
>  
> How is it described? 
> It is a DateTime data type
> 
> Why are you using fix?
> To strip of the time part of the SQL server DateTime field
>  
> Just some quick tries. --
> I rarely use ! for table fields in queries and I do queries 
> using access tables and oracle tables.
> If  dbo_V_S_Oil_Metering_Daily_Volumes!Date is in a table then
> Try  -   ProductionDate: 
> Cdate(dbo_V_S_Oil_Metering_Daily_Volumes.Date)
>       or   cdate([dbo_V_S_Oil_Metering_Daily_Volumes].[Date])
>  
> If this query is a regular access gui query you could try 
> setting the format to date in the gui
>  
> If dbo_V_S_Oil_Metering_Daily_Volumes!Date is in a form, you 
> could set a hidden field to a date format and insert the 
> entered data into the hidden field and use that in the query 
> instead of have to do
> Cdate(dbo_V_S_Oil_Metering_Daily_Volumes.Date) in the query.
>  
> BTW -  Date is usually considered a reserved word in most 
> languages and software. It can cause some strange happenings. 
> Agreed however I did not create nor can I create the SQL server view
> 
> Thanks for the ideas.
>  
>  
> *************************************************************
> * Patricia E. O'Connor
> * Associate Computer Programmer/Analyst
> * OTDA - BDMA
> * (W) mailto:Patricia.O'Connor at otda.state.ny.us
> * (W) mailto:aa1160 at otda.state.ny.us
> ***********************************************************
> 
>