Problem using SQL in DAO VBA code. Interger / double issue potentially

[Mightyginger]

I have a database which stores hourly data. The field "Date" is the index field for the database but is a Double. It stores the date as a double where the integer part is the excel serial for todays date and the decimal part is the hour. So today would be 39580 and the decimal .17 for the hour. So the entry in the field would be 39580.17. So in the database I have the following;

Date Value 1 Value 2
39580.15 101.1 102.1
39580.16 101.2 102.1
39580.17 101.3 102.0

What I want to do is query the database and grab all data for the nth hour of the day. So for example 06 hour. I have tried to do this using an SQL statment as follows
strSQL = "SELECT * FROM " & strTableName & " WHERE (Date-Rnd(Date))=0.06;"
It returns no data however and when I try creating a query in Access to play around with it I get "Data type mismatch in criteria expression". I think it may because Date isa double then I round that and it gives me an Integer. I'm a little stuck to be honest. Maybe I should try to change the type for my Date from Double to actual Date and then I beleive this is a lot easier to achieve. Sorry for the huge post but I'm somewhat stuck as to what to do now.

Sub dumpdata()
Dim strWorksheet As Worksheet
Dim thisWorkbook As Workbook
Dim strTableName As String
Dim strTypeName As String
Dim strPeriodName As String
Dim strDbSource As String
Dim strTempTblName As String
Dim i As Integer
Dim j As Integer
Dim dtDate As Double
Dim rs As Recordset
Dim strSQL As String

strDbSource = "L:\DB2.mdb"
Set db = OpenDatabase(strDbSource)
Set thisWorkbook = ActiveWorkbook
Dim x As Integer
Dim y As Integer

x = 6
y = 2


Do Until Sheet12.Cells(x, y) = ""
strTableName = Sheet12.Cells(x, y)
strSQL = "SELECT * FROM " & strTableName & " WHERE (Date-Rnd(Date))=0.06;"
thisWorkbook.Sheets.Add
ActiveSheet.Name = strTableName
ActiveSheet.Range("A2:IH1000").Clear
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With ActiveSheet.Range("a1")
i = 0
For Each dbField In rs.Fields
.Offset(0, i) = dbField.Name
i = i + 1
Next dbField
End With
'Copy data
ActiveSheet.Range("a2").CopyFromRecordset rs
ActiveSheet.Select
ActiveSheet.Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
rs.Close
Set rs = Nothing
x = x + 1
Loop

db.Close
Set db = Nothing

End Sub

 

[Golom]

Actually, 39580.06 (for example) is a date in Access. Dates are stored as doubles with the day before the decimal place and the fraction of the day after it. ".06" isn't hour number 6 ... it is 6/100 of a day ... specifically 1:26:24 AM.

Your code is returning nothing because a double is a floating point representation and floating point numbers are not exact. The 39580.06 is probably a rounded version of a double value that has non-zero parts somewhere down around the 15th digit after the decimal place.

You need to use the date and time functions. If you want the sixth hour then

strSQL = "Select cDate([Date]), Value1, Value2 " & _
         "From " & strTableName & " " & _
         "WHERE Hour([Date]) = 6"

 

[SkipVought]

Hi,

This is Excel VBA, not Access.

I made a few small changes.

    Do Until Sheet12.Cells(x, y) = ""
            strTableName = Sheet12.Cells(x, y)
            strSQL = "SELECT * FROM " & strTableName & " WHERE Hour(Date)=6;"
            thisWorkbook.Sheets.Add
            ActiveSheet.Name = strTableName
            ActiveSheet.Range("A2:IH1000").Clear
            Set rs = Db.OpenRecordset(strSQL, dbOpenDynaset)
            With ActiveSheet.Range("a1")
                i = 0
                    For Each dbField In rs.Fields
                        .Offset(0, i) = dbField.Name
                        i = i + 1
                    Next dbField
                End With
            'Copy data
            ActiveSheet.Range("a2").CopyFromRecordset rs
            ActiveSheet.Range("A1").Sort _
                Key1:=ActiveSheet.Range("A1"), Order1:=xlDescending, _
                Header:=xlYes, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
            rs.Close
            Set rs = Nothing
        x = x + 1
    Loop

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

The other thing that I wanted to say is that Date is a reserve word and ought not be used as a Field Name.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Golom]

This is Excel VBA, not Access.

True enough but he did ask in forum705 and his date problem was associated with fields stored in an Access database.

 

[Mightyginger]

Hi,

Thanks for your responses. Yes, it is Excel VBA but I was a little unsure where to post and thought this might be the best idea. This sadly doesn't work. I have created this in MS Access and in the design mode even though the field is called Date it is actually a Double. It is defined as "Data Tpe" Number and field size "Double". I fear I do need to use some numerical query on this. Alternatively I need to create another field which is actually a Date (not named Date obviously) and port over the way I'm storing my data but that too would require me to manipulate the existing double.

When I try running the code in Excel it returns nothing and when I try running a query in Access and explicitly staing the field (Hour("Date")=6) I get the error "Date type mismatch in criteria expression".

Any suggestions?

 

[PHV]

what about this ?
& " WHERE Round(100*(Date-Round(Date)))=6;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Or this ?
& " WHERE Round(Date-Round(Date),2)=0.06;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

No QUOTES!!!

Hour([Date])=6

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[Mightyginger]

I only used the quotes in the Access query. The Round function is Rnd using VBA and I was using that in the first piece of code and it wasn't working (sadly).

I can e-mail a mini version of the database with the spreadsheet if that works to resolve this?

Neil.

 

[Golom]

How is the Date field being populated?

As I said, DateTime fields in Access (and in Excel) are doubles internally. If you have such datetime fields in Excel or in Access and choose to store them in a table with datatype "double", access won't complain.

However, double or datetime, the interpretation of the field to represent a date remains the same ... date before the decimal; fraction of a day after it.

The only way that the ".06" in "39580.06" represents the sixth hour is if you manually constructed the double as something like

TheHour = 6.0
[Date] = 39580.0 + (TheHour / 100.0)

In normal DateTime conversions, The sixth hour would correspond to the doubles

39580.25 to 39580.2916666667

and the hour is easily recovered with Hour([Date])