Solved

Date Time in sql string

Posted on 2012-04-10
13
316 Views
Last Modified: 2012-05-04
I am pulling the CreationTime of a message in Outlook and putting it into an Access table.  I want to see if it already exists in the table.  

(The format for CreationTime looks like this 3/29/2012 10:35:03 AM.  It is saved in a date field with the format set to general date.)  The data exists in the Access table so the recordcount should be returning a one instead of a zero.  How should the varDateTime be handled in the sql string?  

dim varDateTime as date
varDateTime = oMsg.CreationTime
       
                    sSQL = "SELECT tblOutlookData.CreationTime, tblOutlookData.CapExID" _
                    & " FROM tblOutlookData" _
                    & " WHERE tblOutlookData.CreationTime=" _
                    & "#" & varDateTime & "#"

                     Set rs.openrecordset(sSQL)

msgbox(rs.recordcount)
0
Comment
Question by:Sasha42
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37830729
change

 Set rs.openrecordset(sSQL)

with

 Set rs = currentdb.openrecordset(sSQL)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37830734
and to check your sSQL


                    sSQL = "SELECT tblOutlookData.CreationTime, tblOutlookData.CapExID" _
                    & " FROM tblOutlookData" _
                    & " WHERE tblOutlookData.CreationTime=" _
                    & "#" & varDateTime & "#"

Debug.print sSQL    'Add this line
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37830767
A few things - the way you are handling the date looks okay.

You need to
- Declare a recordset variable
- Use the syntax suggested in cap1's comment
- Move to the last record to populate the recordset. You will not necessarily get an accurate recordcount  by simply opening a recordset (it will always show 0 if there are no records, or 1 if there are any number of records greater than zero).  If you use a "MoveLast" statement to populate the recordset, the recordcount will show correctly if you have more than one record:


dim varDateTime as date
Dim rs as DAO.Recordset
varDateTime = oMsg.CreationTime
       
                    sSQL = "SELECT tblOutlookData.CreationTime, tblOutlookData.CapExID" _
                    & " FROM tblOutlookData" _
                    & " WHERE tblOutlookData.CreationTime=" _
                    & "#" & varDateTime & "#"

                     Set rs = currentdb.openrecordset(sSQL)
                    if rs.RecordCount = 0 then
                        MsgBox "No records found"
                   else
                        rs.MoveLast
                        msgbox  rs.recordcount
                        rs.MoveFirst
                   end if
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37831308
Also, date values must be passed as properly formatted string expressions:

 & "#" & Format(varDateTime, "yyyy/mm/dd") & "#"

/gustav
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 37833303
As you debug, if it is not working, temporarily replace varDateTime with an EXACT string value you know should work, and see if you get a correct result.
Access ultimately stores DateTimes as a special kind of double-precision decimal.  
Getting the results you want depends very much on getting varDateTime to be seen by Access as the value you want.
Your syntax looks good with the hash marks, but you are looking to see if your comparison is going to work

Ulitmately what the comparison is, is this
CDbl(tblOutlookData.CreationTime) = CDbl(CDate(varDateTime))
That can be subtly difficult to achieve

Hence, the advice to start debugging with an EXACT known good string value, and moving to the variable in the next step
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37833456
Nick, why this confusion? DateTime of Access is not a "special kind", it is a standard Double.

Also, formatting the date variable to a string expression as shown is the standard method because it is totally safe. No magic here.

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

 
LVL 26

Expert Comment

by:Nick67
ID: 37833621
/gustav

The time part is ALWAYS positive, yes?
0 is 30-Dec-1899 12:00:00 AM
1.333 is ~31-Dec-1899  8:00 AM
Nothing special there

-0.333 is ~29-Dec-1899 8:00 AM
Hold the phone!
If we go back 1/3 of a day from midnight on day zero, we should get 8:00 PM
But we don't.
Because the decimal part is ALWAYS positive
Which is why I refer to it as a special kind of Double

If you aren't dealing with the 19th Century, it really won't matter much.
But it is there.

Also /gustav
There is nothing wrong with how you suggested the string be formatted BUT

#3/29/2012 10:35:03 AM# will not be equal to  "#" & Format(2012/3/29, "yyyy/mm/dd") & "#"
Because one has the time, and the other does not
Coerce them to double and the first is
40997.4410069444
and the second is
40977.

Been burnt enough in query criteria of fields with DateTimes that I know to be cautious, and to verify what I am getting!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37833642
10-4 Nick...
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37836199
> #3/29/2012 10:35:03 AM# will not be equal to  "#" & Format(2012/3/29, "yyyy/mm/dd") & "#"

Sorry, missed the time, but I'm quite surprised that both of you neither know what to do nor can figure out how. It has been posted at EE and many other fora many times:

"#" & Format(varDateTime, "yyyy/mm/dd hh:nn:ss") & "#"


> Because the decimal part is ALWAYS positive
> Which is why I refer to it as a special kind of Double

Again, there is nothing special about the base data type Double of data type DateTime.
What seems to confuse you is the way date and time are mapped to numeric values. The reason for this is actually quite clever as it makes it easy to separate date and time from a value holding both.

I can recommend an article that explains this, which also demonstrates why the minimum and maximum dates that DateTime can hold (100-01-01 00:00:00 to 9999-12-31 23:59:59) are not arbitrary but carefully selected to allow for a millisecond resolution (though not officially supported) through the full range using the standard Double data type:

MS Access Can Handle Millisecond Time Values--Really

It also explains how the "native time" of VB(A) can be converted to a "linear time" (and vice versa) which you will need if you wish to perform millisecond calculations fast. You will pay special attention to Figure 1:

Linear and Native Comparison

If you study the code, you will also see, that sometimes it is necessary to convert date/double values to the Decimal data type to keep the millisecond precision at extreme date/time values.

Look up Sidebar 1:

Useful UDFs for Other Timing Techniques

for a list of all the beautiful functions contained in the code!

/gustav
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37837026
<but I'm quite surprised that both of you neither know what to do nor can figure out how.> that is just your line of thinking.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37838380
/gustav
LOL

Yes, I know what to do and how to figure it out.
Teaching it to the OP in a threaded discussion...can be complex.

1. DateTimes are stored in Access as a Double value
2. The decimal part of that Double value is always positive
    a.  This is a clever way to deal with times
    b.  Your standard way of mathematical thinking does not apply in full to negative values
        i. because the time part (decimal) is always positive, in number line thinking -1.6666 is actually closer in time to zero than -1.3333 is
        ii. unless you are dealing with pre-twentieth century dates, you won't encounter any problems with this anyway.
3. A date and time are BOTH ALWAYS stored
    a.  If you choose to only store a time (say 8 AM) what gets stored is 0.3333333 repeating
    b.  If you format that value as General date you will get 1899-12-30 8:00 AM -- which is not necessarily intuitive.
    c.  Since both are always stored, it makes little sense to have a field for time and one for date in your app
    d.  Format() only changes how a date DISPLAYS.  It does nothing to the value itself
    e.  If you build query criteria using BETWEEN you MUST be aware of time values, if you have stored them (using Now() for instance)
        i. BETWEEN 1-Jan-2102 AND 1-Apr-2012 is actually between 1-Jan-2012 12:00:00 AM and 1-Apr-2012 12:00:00 AM inclusive
       ii. That means that values of 1-Apr-2012 12:00:00 AM will be included and values greater that 1-Apr-2012 12:00:00.0001 (to whatever precision) will be EXCLUDED
      iii. This is a bit counter-intuitive.  To get the intuitive result BETWEEN 1-Jan-2012 AND 1-Apr-2012 11:59:59 PM is what you must construct
4.  The only truly safe string format for a DateTime is ISO 8601 format (yyyy-mm-dd hh:mm:ss)
5.  Geting control values and VBA variables to be recoginzed by Access VBA and Queries as the date you want them to represent can be tricky
    a.  Regional settings in Windows may comes into play
    b. Hash marks (#) surrounding the value are almost always required
    c.  Formatting your string, and then coercing it to date may both be required
    d.  Coercing values to Double and displaying them can be a valuable troubleshooting step if you aren't getting the results you expect.

I think that covers most of it.
I'd have written an article by now--but I do acknowledge that /gustav is the master and I but the student of DateTime.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37838576
Yep, that pretty much sums it up.

/gustav
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37930174
Sasha42,

You received a lot of comments here, and never responded letting us know what you tried, etc...

From the standpoint of someone answering your questions, that's a bit frustrating.

Can you post some feedback to the comments here to let us know what you tried and what the results were?
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

21 Experts available now in Live!

Get 1:1 Help Now