Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date Time in sql string

Posted on 2012-04-10
13
Medium Priority
?
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37830729
change

 Set rs.openrecordset(sSQL)

with

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

Expert Comment

by:Rey Obrero (Capricorn1)
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 52

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 2000 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 52

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
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37833642
10-4 Nick...
0
 
LVL 52

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 52

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

597 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