Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: need to use floor() function in access

    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

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    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;

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    Originally posted by M Owen
    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

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    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 ...

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: need to use floor() function in access

    Originally posted by PC2
    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
    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

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Gregg,

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

  7. #7
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question

    Originally posted by M Owen
    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

  8. #8
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    You don't need the Floor function.

    You need:

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

    Or

    DateValue([YourExpression])


    HTH

  9. #9
    Join Date
    Nov 2003
    Posts
    4
    Originally posted by M Owen
    Gregg,

    Problem is trying to use the Floor function within a query ... That requires SQL functions to which Floor is not one of them.
    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

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    PC,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •