UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> .seek Fails Find Datetime In Access Jet Db, Access 97    
 
   
dw85745
post Aug 2 2019, 02:34 PM
Post#1



Posts: 103
Joined: 29-September 10
From: AZ


I have a PrimaryKey of DateTime.
Using .Seek to find that DateTime in the DB fails at times.
This is because the DB stores the DateTime data type as a double and the precision of the .Seek "=" dtmDateTime
is different than that stored in the database.

Is there anyway to use .Seek to bracket the date stored in the database?
For example .Seek ">" dtmDateTime and .Seek "<" dtmDateTime

If not what is the best way to find these records as they exist, just can't be found by .Seek?
This post has been edited by dw85745: Aug 2 2019, 02:35 PM
Go to the top of the page
 
theDBguy
post Aug 2 2019, 02:38 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,503
Joined: 19-June 07
From: SunnySandyEggo


Hi. If the DB stores the dates as Doubles, then have you tried converting your date values into Doubles before seeking for them?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dw85745
post Aug 2 2019, 05:17 PM
Post#3



Posts: 103
Joined: 29-September 10
From: AZ


theDBGuy: Thanks for responding.

Had never tried converting a Date to a Double and then use .Seek, but it does work as far as doing the Seek of a DateTime Data Type.
Thanks learned something..
Whether that converted double will equal the DateTime double value stored within the DB is Unknown at this time as
this condition occurs rarely -- but does occur.

Have tried an alternate method of using an SQL BETWEEN clause to resolve the issue BUT have encountered other problems.
-- which I have yet to resolve -- because search fails because of gaps in the recordset.



Go to the top of the page
 
ADezii
post Aug 2 2019, 05:55 PM
Post#4



Posts: 2,688
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Can you post the Code containing the Seek Method?
  2. Converting a DateTime Value can be done as follows:
    CODE
    Debug.Print CDbl(Now())    'produces
    43679.7871180556

QUOTE
Had never tried converting a Date to a Double and then use .Seek

  1. Actually, it can be done.
  2. Sample Data:
    IDMyDateTime
    18/2/2019 8:00:00 AM
    28/2/2019 9:00:00 AM
    38/2/2019 10:00:00 AM
    48/2/2019 11:00:00 AM
    58/2/2019 12:00:00 PM
    68/2/2019 1:00:00 PM
    78/2/2019 2:00:00 PM
    88/2/2019 3:00:00 PM
    98/2/2019 4:00:00 PM
    108/2/2019 5:00:00 PM
    118/2/2019 6:00:00 PM
  3. Code Definition:
    CODE
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim dteSeekDate As Date

    dteSeekDate = #8/2/2019 1:00:00 PM#

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("tblTest", dbOpenTable)

    With rst
      .Index = "MyDT"
      .Seek "=", CDbl(dteSeekDate)
      
       If Not .NoMatch Then
         Debug.Print ![ID], ![MyDateTime]
       End If
    End With

    rst.Close
    Set rst = Nothing
  4. OUTPUT:
    CODE
    6            8/2/2019 1:00:00 PM


This post has been edited by ADezii: Aug 2 2019, 06:33 PM
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2019, 07:49 AM
Post#5


UA Admin
Posts: 36,029
Joined: 20-June 02
From: Newcastle, WA


"This is because the DB stores the DateTime data type as a double and the precision of the .Seek "=" dtmDateTime
is different than that stored in the database."

Yes, as a matter of fact, ALL relational database engines with which I am familiar do store date values as Date AND Time. We also have to account for that fact when using date fields with any kind of logic in VBA or any filtering in SQL.

You need to make sure that whatever values you use compare "Apples to Apples", i.e. that you ensure the precision of both elements in an operation is the same. You can use the cDbl() conversion operator, as noted, to make that happen.

Check out my YouTube Video on the subject. There are additional videos there touching on other aspects of this situation, including one on using dates/times in filtering and sorting operations.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Aug 3 2019, 10:17 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,503
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it somewhat working. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
PhilS
post Aug 4 2019, 07:51 AM
Post#7



Posts: 634
Joined: 26-May 15
From: The middle of Germany


[post withdrawn until further notice]
This post has been edited by PhilS: Aug 4 2019, 07:53 AM

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 03:42 PM