Solved

Access 2007 Query Select Dates

Posted on 2014-07-28
54
472 Views
Last Modified: 2014-08-03
select * from dbo.iApplicationStat    

The above Access 2007 query pulls records from a Oracle DB. Query is called PullData.

I only want to select records where TimeStamp field (its a text field) contains Todays date - 1 day ?

I have tried a few things but keep getting errors.


Table Examples:


Timestamp
2013/10/05 00:15:00      1      Master_Script      0      0      0      0      0
2013/10/05 00:15:10      1      Master_Script      0      0      0      0      0
2014/07/27 00:18:00      1      Master_Script      0      0      0      0      0
0
Comment
Question by:thayduck
  • 26
  • 12
  • 11
  • +1
54 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
You need to first convert your Text column to Date data type. Use CDate for this. Then you need to compare it with today's date - 1 day. Use DateAdd for this. The whole query will look like this:
select * from dbo.iApplicationStat
where CDate([Timestamp]) >= DateAdd('d', -1, Date());

Open in new window

0
 

Author Comment

by:thayduck
Comment Utility
select * from dbo.iApplicationStat    


where CDate([Timestamp]) >= DateAdd('d', -1, Date())


Get below error:

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions under which this error can occur. Please contact Microsoft Product Support Services for more information.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Have you typed the query in the Access Query object, or in the VBA code? Is it really Access 2007? It is strange that you have a table called "dbo.iApplicationStat". Usually, when you link a table from SQL Server, the tables are called "dbo_iApplicationStat". Another mysterious thing is that you have mentioned Oracle. AFAIK, Oracle don't usually have a "dbo" schema, unless you have created a dbo user in Oracle.
0
 

Author Comment

by:thayduck
Comment Utility
dbo.iapplicationstat Properties:

Type Query:  SQL Pass-Through Query
Desc: Pass Through to bypass odbc signon



Note: I support, did not write.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
OK I get it. Is it really connected to Oracle? If it is Oracle use this syntax:
select * from dbo.iApplicationStat
where TO_DATE([Timestamp]) >= TRUNC(SYSDATE) - 1;

Open in new window

However, I still not convinced that it is Oracle. Most Likely it is SQL Server. For SQL Server use this query:
select * from dbo.iApplicationStat
where CONVERT(DATETIME, REPLACE([Timestamp], '/', '-'), 120) >= CONVERT(DATETIME, DateAdd(d, -1, Convert(DATE, GetDate())));

Open in new window

0
 

Author Comment

by:thayduck
Comment Utility
Get same error on both suggestions.

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions under which this error can occur. Please contact Microsoft Product Support Services for more information.


Query runs fine without Where Statement. I just need this query to select 1 day old records now.

This Access DB was converted to Access 2007 from Access 2000. I did that conversion.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
OK. Maybe it is Oracle. In this case you do not need square brackets:
select * from dbo.iApplicationStat
where TO_DATE(Timestamp) >= TRUNC(SYSDATE) - 1;

Open in new window

0
 

Author Comment

by:thayduck
Comment Utility
Same error.

I changed to :

Where TO_DATE(dbo.iApplicationStat.Timestamp) >= TRUNC(SYSDATE) - 1

and got error:

User Defined SQL Function 'DBO.TRUNC' does not exist.

So maybe it is SQL ?
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
I have just tested the queries for Oracle (BTW, for Oracle use this query)
select * from iApplicationStat
where TO_DATE(Timestamp, 'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE) - 1;

Open in new window

and for SQL Server. Both work
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Check the ODBC DSN settings and advise what database you are connecting to
0
 

Author Comment

by:thayduck
Comment Utility
Same error.

If I remove where clause and execute query I get records from Oracle.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Have you tried this updated query:
select * from iApplicationStat
where TO_DATE(Timestamp, 'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE) - 1;

Open in new window

0
 

Author Comment

by:thayduck
Comment Utility
Yes. Get same error.


select * from iApplicationStat
where TO_DATE(Timestamp, 'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE) - 1;



Reserved error (-7711) There is no msg for this error.

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions under which this error can occur. Please contact Microsoft Product Support Services for more information.
0
 
LVL 24

Accepted Solution

by:
chaau earned 50 total points
Comment Utility
There are so many reports about this error with so many workarounds (including one from Microsoft) that I suggest redesigning the whole thing. Let's leave your query as is. Let's create a new query based on the existing one. Just create a new query and use the query for "iapplicationstat" as a source. Here you are able to use Access functions in the WHERE Clause, like this:
select * from qryiApplicationStat
where CDate([Timestamp]) >= DateAdd('d', -1, Date());

Open in new window

Please correct the "from qryiApplicationStat"  to use the correct name of the query. Save your new query and use it
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
I guess you run a pass-through query to SQL Server and the field really is of datetime. Then:

select * from dbo.iApplicationStat
where DATEDIFF(DAY, timestamp, GETDATE()) = 1

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Gustav Brock :

Get same error on my Pass Through query.  FYI, TimeStamp field is a Text field.

Reserved error (-7711) There is no msg for this error.

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions under which this error can occur. Please contact Microsoft Product Support Services for more information.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
OK, then try casting the timestamp to datetime:

select * from dbo.iApplicationStat
where DATEDIFF(DAY, CAST(timestamp as DateTime), GETDATE()) = 1

But, again, is this pulled from SQL Server or Oracle?

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Oracle
0
 

Author Comment

by:thayduck
Comment Utility
Gustav Brock :

Same error using:

select * from dbo.iApplicationStat
where DATEDIFF(DAY, CAST(timestamp as DateTime), GETDATE()) = 1



Reserved error (-7711) There is no msg for this error.

Reserved error <Item>; there is no message for this error. (Error 3000)
An unexpected error occurred. The specified code identifies the conditions under which this error can occur. Please contact Microsoft Product Support Services for more information.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
I think you'll be better off to go with the plan B I have suggested.
select * from qryiApplicationStat
where CDate([Timestamp]) >= DateAdd('d', -1, Date());

Open in new window

0
 

Author Comment

by:thayduck
Comment Utility
Chaau:

I tried Plan B, but it wont let me create a query using another query as the source.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
As this is a pass-through query to Oracle, you should ask for having the topic changed from Access to Oracle.
I'm not into Oracle, but this should work:

select * from dbo.iApplicationStat
where DATEDIFF(DAY, CAST(timestamp as Date), GETDATE()) = 1

But you are probably facing other errors.

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
The pass through query works fine without the Where Clause.

I was trying to put in the Where Clause to reduce amount of records accessed from Oracle.

Thought this would be something very simple to do and it is not.


Per  Chaau, it looks like it is a known issue.....

There are so many reports about this error with so many workarounds
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Oh, then your where is the issue.

However, a timestamp should be convertible to Date or DateTime according to this page:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm#i1269136

Can you run a simple query like this:

select *,  CAST(timestamp as Date)         // or DateTime
from dbo.iApplicationStat

?

/gustav


/gustav
0
 

Author Comment

by:thayduck
Comment Utility
select *,  CAST(timestamp as Date)
    from dbo.iApplicationStat

No, get same error.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
And:

select *,  CAST(timestamp as DateTime)
from dbo.iApplicationStat

?

/gustav
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:thayduck
Comment Utility
select *,  CAST(timestamp as DateTime)
from dbo.iApplicationStat

Same Error.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
OK, then check with the Oracle guys m/f. Raise an admin request for the topic change.

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Curious, this works and Application is a TEXT field:

select *
from dbo.iApplicationStat

Where SUBSTRing(application,1,4) = 'MAST'


But this does not work (get same error) and Timestamp is also a TEXT field:

select *
from dbo.iApplicationStat

Where SUBSTRing(Timestamp,1,4) = '2014'
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
There may be some trick to apply.

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Made request to add Oracle to conversation
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
Comment Utility
Open your Oracle database and build the query there.  Once you have the syntax correct, copy it and paste it into your Access pass-through query.  We are just guessing as to what the actual syntax should be.

If the query works in the Oracle database but not as a pass-through, the problem is probably with the Oracle driver you are using.  You may need to install a different one.

Also, is there some reason that this must be a pass-through query.  Generally select queries created as Access querydefs work fine against linked tables.  Access makes every effort to "pass through" every query so the effect would be the same.  We can help you with Access SQL syntax.  Converting to a date is a standard function and so the ODBC driver should translate cdate() to whatever syntax the server requires.  You can verify this by using whatever traffic monitoring tool Oracle offers to let you see the actual query that Access is sending.  If it turns out that cdate() can't actually be translated to some native Oracle function, then you'll need to go back to doing this as a pass-through and figure out that syntax.

And finally, TimeStamp is s special type field in SQL Server and it isn't actually a date/time so perhaps that is the case with Oracle too and you won't actually be able to convert the field to a date.
0
 

Author Comment

by:thayduck
Comment Utility
I finally got chaau's  solution to work. I am now only pulling in records with yesterdays date.

Problem is, the same problem is still there, query freezes saying there are to many records to process and no more room.

Maybe it still takes up room when it reads all these records even though I am asking for only 12,000 records max.

Actual table has over 1.5 million records. I only want to process around 12,000 records...
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Which one?  Post what you have working.  Is it a pass-through?

Is the TimeStamp field indexed?
0
 

Author Comment

by:thayduck
Comment Utility
Pass Through Query  

I created query that uses pass through query as its data source.


There are so many reports about this error with so many workarounds (including one from Microsoft) that I suggest redesigning the whole thing. Let's leave your query as is. Let's create a new query based on the existing one. Just create a new query and use the query for "iapplicationstat" as a source. Here you are able to use Access functions in the WHERE Clause, like this:

select * from qryiApplicationStat
where CDate([Timestamp]) >= DateAdd('d', -1, Date());
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> Maybe it still takes up room when it reads all these records even though
> I am asking for only 12,000 records max.

That's because the use of CDate forces a full table scan where each and every record has to be checked.

You could try with this Access query:

select * from qryiApplicationStat
where Timestamp
    between Format(Format(DateAdd("d", -1, Date()), "yyyy\/mm\/dd hh\:nn\:ss")
    and Format(DateAdd("s", -1, Date()), "yyyy\/mm\/dd hh\:nn\:ss")

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
It is important to get the criteria into the pass-through query.  It sounds like Access is bringing back all the rows.  Hopefully gustav's solution will fix that but if not, try using his between suggestion in the pass-through after first changing it to Oracle syntax.
0
 

Author Comment

by:thayduck
Comment Utility
Currently using in query:


>=DateAdd('d',-1,Date())

Above is only bringing back yesterday's records, but stll ends with error saying:


Not enough room in Access DB (over 2gig)   or

Not enought temporary storge space on disk
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You have to stop Access from asking for the entire table.  Obviously, Access is not able to merge the date function selection criteria with the underlying pass-through query.  Did you try gustav's solution?
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
I was just browsing Internet and found on a forum a suggestion to use ODBC functions that when creating a conditions in the pass through query. Try this:
select * from dbo.iApplicationStat
where {fn TIMESTAMPDIFF(SQL_TSI_DAY, {fn CONVERT(Timestamp, SQL_DATE)}, {fn CURDATE()} )} <= 1

Open in new window

If nothing works I guess the only option for you will be to approach your DBA and ask him to create the following view on the server:
CREATE VIEW applicationsToday
AS select * from iApplicationStat
where TO_DATE(Timestamp, 'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE) - 1;

Open in new window

When the view is created modify your pass-through query to select from applicationsToday
0
 

Author Comment

by:thayduck
Comment Utility
This works, brings back records, but will fail after 10 min with storage problems:

SELECT dbo_iApplicationstat.Timestamp
FROM dbo_iApplicationstat
WHERE (((dbo_iApplicationstat.Timestamp)>=DateAdd('d',-1,Date())));

Both of these queries fail:

select * from dbo.iApplicationStat
where {fn TIMESTAMPDIFF(SQL_TSI_DAY, {fn CONVERT(Timestamp, SQL_DATE)}, {fn CURDATE()} )}

select * from qryiApplicationStat
where Timestamp
    between Format(Format(DateAdd("d", -1, Date()), "yyyy\/mm\/dd hh\:nn\:ss")
    and Format(DateAdd("s", -1, Date()), "yyyy\/mm\/dd hh\:nn\:ss")
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
I think you need to create a view on the server. Also, you need to consider creating a function-based index for the following column:
CREATE INDEX iApplicationStat_date
ON iApplicationStat
(TO_DATE(Timestamp, 'YYYY/MM/DD HH24:MI:SS'));

Open in new window

This will make sure that the date equivalent of the Timestamp column is indexed. That way, I believe, it should work faster
0
 

Author Comment

by:thayduck
Comment Utility
Sorry, this query worked to, it was just missing a ) .

select * from qryiApplicationStat
where Timestamp
    between Format(Format(DateAdd("d", -1, Date()), "yyyy\/mm\/dd hh\:nn\:ss")
    and Format(DateAdd("s", -1, Date()), "yyyy\/mm\/dd hh\:nn\:ss")


So, to sum things up, when I created a query that used the pass-through query as its data source, and then put in any suggested code to 'only select certain records by dates', the query would run but again it would stop running after 20 minutes saying that acess db has no more room or there is not enough temporary disk space to complete operation.

Not good.

Then, I put the suggested code to 'only select certain records by dates'  in the pass through query and no matter what I tried, the pass through would just fail telling me there were syntax errors. But, this same code worked fine in the other query.

So, what I finally did to get this issue resolved was put below code (temp fix) in pass through query:

select * from dbo.iApplicationStat

where substring(dbo.iApplicationStat.timestamp,1,7) >= '2014-07'


So now it only brings back records starting in July 2014.

Now reports are running again and not halting.

I know this is not the ideal way and I will have to change this query every few months, but it works for now.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 total points
Comment Utility
It's ideal in the way that it runs on the server.

You can save the p-t query like:

select * from dbo.iApplicationStat
where substring(dbo.iApplicationStat.timestamp,1,7) >= 'yyyy-mm'

then, in VBA, adjust the SQL before calling it like:

Set qdf = dbs.QueryDefs("YourPTQueryName")
strSQL = Replace(qdf.SQL, "yyyy-mm", Format(Date, "yyyy\-mm"))
qdf.SQL = strSQL
Set rst = qdf.OpenRecordset

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Since this report shows yesterdays records not current days, I would need your code to create the yyyy\-mm as current date  minus 1 month.

So, if today was 08/01/2014, your yyyy\-mm  would be 2014-07

Since I am not a VB guy , how would you code that ?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Oh, then it would be:

strSQL = Replace(qdf.SQL, "yyyy-mm", Format(DateAdd("m", -1, Date), "yyyy\-mm"))

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Set qdf = dbs.QueryDefs("dbo_iApplicationstat")
    strSQL = Replace(qdf.Sql, "yyyy-mm", Format(DateAdd("m", -1, Date), "yyyy\-mm"))
    qdf.Sql = strSQL
    Set rst = qdf.OpenRecordset
   
    Get error on first line of your code.
0
 

Author Comment

by:thayduck
Comment Utility
run time error 424
object required
0
 

Author Comment

by:thayduck
Comment Utility
ub Main()
   
    Dim xlApp As New Excel.Application
    Dim wb As New Excel.Workbook
    Dim ws As New Excel.Worksheet
    Dim pt As PivotTable
    Dim qt As QueryTable
   
    Dim accessApp As New Access.Application
       
' Build  Hourly Call Distribution Customer Table
    accessApp.OpenCurrentDatabase ("C:\Symposium Phone Reports\Symposium Phone Reports.accdb")
   
   
    Set qdf = dbs.QueryDefs("dbo_iApplicationstat")
    strSQL = Replace(qdf.Sql, "yyyy-mm", Format(DateAdd("m", -1, Date), "yyyy\-mm"))
    qdf.Sql = strSQL
    Set rst = qdf.OpenRecordset
   
   
   
   
   
    accessApp.DoCmd.RunMacro "Hourly Call Distribution Customer"
    accessApp.CloseCurrentDatabase
' Exit Sub
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It's not the full code, essential parts only. Dims etc. are left out so, for example, Set dbs is missing.

You'll have to incorporate it in your existing Access code which, I guess, RunMacro "Hourly Call Distribution Customer" is calling.

/gustav
0
 

Author Comment

by:thayduck
Comment Utility
Ok, I will have to wait then, since Access Guy is away on vacation.
I had to get this fixed because it was a emergency but I will tell him about your suggestion to improve temp fix I did.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
OK, it shouldn't take him long.

/gustav
0
 

Author Closing Comment

by:thayduck
Comment Utility
Thanks for help...

Right now the only thing that works is:

Where substring(dbo.iapplicationstat.timestamp,1,7)   >=  '2014-08'

in my pass through query.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now