Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

Access 2007 Query Select Dates

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
thayduck
Asked:
thayduck
  • 26
  • 12
  • 11
  • +1
3 Solutions
 
chaauCommented:
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
 
thayduckAuthor Commented:
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
 
chaauCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
thayduckAuthor Commented:
dbo.iapplicationstat Properties:

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



Note: I support, did not write.
0
 
chaauCommented:
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
 
thayduckAuthor Commented:
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
 
chaauCommented:
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
 
thayduckAuthor Commented:
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
 
chaauCommented:
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
 
chaauCommented:
Check the ODBC DSN settings and advise what database you are connecting to
0
 
thayduckAuthor Commented:
Same error.

If I remove where clause and execute query I get records from Oracle.
0
 
chaauCommented:
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
 
thayduckAuthor Commented:
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
 
chaauCommented:
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
 
Gustav BrockCIOCommented:
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
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
thayduckAuthor Commented:
Oracle
0
 
thayduckAuthor Commented:
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
 
chaauCommented:
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
 
thayduckAuthor Commented:
Chaau:

I tried Plan B, but it wont let me create a query using another query as the source.
0
 
Gustav BrockCIOCommented:
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
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
thayduckAuthor Commented:
select *,  CAST(timestamp as Date)
    from dbo.iApplicationStat

No, get same error.
0
 
Gustav BrockCIOCommented:
And:

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

?

/gustav
0
 
thayduckAuthor Commented:
select *,  CAST(timestamp as DateTime)
from dbo.iApplicationStat

Same Error.
0
 
Gustav BrockCIOCommented:
OK, then check with the Oracle guys m/f. Raise an admin request for the topic change.

/gustav
0
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
There may be some trick to apply.

/gustav
0
 
thayduckAuthor Commented:
Made request to add Oracle to conversation
0
 
PatHartmanCommented:
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
 
thayduckAuthor Commented:
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
 
PatHartmanCommented:
Which one?  Post what you have working.  Is it a pass-through?

Is the TimeStamp field indexed?
0
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
> 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
 
PatHartmanCommented:
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
 
thayduckAuthor Commented:
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
 
PatHartmanCommented:
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
 
chaauCommented:
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
 
thayduckAuthor Commented:
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
 
chaauCommented:
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
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
Oh, then it would be:

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

/gustav
0
 
thayduckAuthor Commented:
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
 
thayduckAuthor Commented:
run time error 424
object required
0
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
thayduckAuthor Commented:
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
 
Gustav BrockCIOCommented:
OK, it shouldn't take him long.

/gustav
0
 
thayduckAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 26
  • 12
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now