I have a datatable in access that has a field that contains date information (called Trade_Date). This date is not truncated so I have hour, minutes and seconds information. I need to make a query from VBA to retrieve data for a specific date so I need a query like

" SELECT Trade_Date,... FROM Table WHERE floor(Trade_Date) = desired Date "

I am using Microsoft.Jet.OLEDB.4.0 as the provider and floor() function is not recognized.
In the help I found that scalar functions like floor() should work since you use {} like:

" SELECT Trade_Date,... FROM Table WHERE {fn floor(Trade_Date)} = desired Date "

but it is still not working.
I also tried to use a pass-through query but It fails (maybe because I am not used with this)

Can someone please help me. I need to use a floor() function but don't know how.
Thanks,
PC2

Since you're seeking a match to an exact date why use the "floor" function? Just do : WHERE ( MyTableName.TradeDate=#Date Literal Here#);

Or if it's inexact the date (i.e. the lastmost time ) do something like:

SELECT TOP 1 Fieldsgohere ... FROM MyTable WHERE (MyTable.Table_Date=#Date Literal Here#) ORDER BY Table_Date DESC;

M Owen,
Thanks for your reply.
Actually I need to retrieve all the data that has the same day of the desired data doesn't matter hour,minutes,seconds.

For example the desired date is 11/05/2003 so I want that records with dates like 11/05/2003 18:53:30, 11/05/2003 01:34:12, ...etc all come in my query.

I know one approach could be ... WHERE MyTableName.TradeDate>=#Date Literal Here# AND
MyTableName.TradeDate<#Date Literal Here# + 1

but I also have more complex queries that joins this table with dates like "mm/dd/yy hh:mm:ss" with another table than has just "mm/dd/yy" and in this case I'd do something like
... table1 INNER JOIN table2 WHERE table2.Table_Date = floor(table1.Table_Date)

That is why the floor() function would help me a lot
Rgds,
PC2

Okay ... Bad news. Floor is an Excel VBA function not an SQL function ... So for your needs you'll need to do a Between test:

WHERE ( DateDuJour BETWEEN #01/01/2003 00:00:01 AM# AND #01/02/2003 00:00:00 AM#)

You'll need to specify the date and time within the date literals ...

I'm not all that familiar with care and feeding of Excels objects but if you really want to use the floor function can't you just set a reference to the Excel library? I see it's part of the WorksheetFunction class in the Object Browser. Just reference, declare and instantiate and you should be able to use it.

Gregg

Gregg,

Problem is trying to use the Floor function within a query ... That requires SQL functions to which Floor is not one of them.

M

He mentioned VBA, and it sounded like the SQL might be created in code so that's where I was coming from. He could still create a custom function in a standard module that wraps around the Excel function and achieve the same result couldn't he?

Gregg

You don't need the Floor function.

You need:

Format([YourExpression], "mm/dd/yyyy")

Or

DateValue([YourExpression])


HTH

Yes, that is the point, I need it inside the query.
The solution:
WHERE (MyTableName.TradeDate>=#Date Literal Here# AND
MyTableName.TradeDate<#Date Literal Here# + 1)
solves my problems.

The point is that the Microsoft Jet 4.0 (the provider I am using) help manual says that it supports scalar functions including floor(). But as I could see it does not support.

There migth be a way to pass-through the query direct to ODBC provider where floor function might work. But it is too technical for me so I will keep the simpler solution above.

I would like to thank you all for your replies.
If someone else has a better idea I would appreciate if he can share with us.
Rgds,
PC2

PC,

A pass-thru might work IF Floor was supported in SQL ... It's not, unless a specific provider has made an extension to the standard SQL language to include a custom function.