Query in Excel 2010 Date Format Error

Posted on 2012-09-19
Last Modified: 2012-11-14
When I use:
SELECT "HPD:HelpDesk"."Case ID+", "HPD:HelpDesk"."Create Time"
FROM "HPD:HelpDesk" "HPD:HelpDesk"
WHERE ("HPD:HelpDesk"."Create Time">=? And "HPD:HelpDesk"."Create Time"<?)

And set Parameter reference to be Cells A1, A2, I get an error: "[AR System ODBC Driver] Conversion Error"

But when I hard-code the Param it works. However it's not using a standard date format.

WHERE  ("HPD:HelpDesk"."Create Time">={ts '2012-09-18 00:00:00'} And "HPD:HelpDesk"."Create Time"<{ts '2012-09-19 00:00:00'})

 I think this is a simple query language command where you tell it to interpret that non-standard date format as a date, but I'm not great with MS query.

Also worth noting, it's using the "AR System ODBC driver," not sure if this matters.

Could you help me?
Question by:cnrlzen
    LVL 23

    Expert Comment

    Set 2 new cells with formulas in them to be your new parameter references.

    e.g  If you enter your dates in A1 and A2, then set a cell, say X1 with formula:

    =TEXT(A1,"dd mmm yy")  

    and another cell, say X2 to:

    =TEXT(A2,"dd mmm yy")

    and then redirect your parameters to X1 and X2 instead of A1 and A2....

    Author Comment

    Thanks, but it didn't work, same error.

    Isn't there a way in query language to interpret a text date as a date value?
    LVL 23

    Expert Comment

    I just double checked this with my own database and my sql is very similar to yours:

    e.g. I get something like:

    WHERE (CUSTOMER_ORDER.CREATE_DATE>={ts '2006-10-23 12:40:36'})

    and the solution I offered above gets me the results I want...

    my SQL query shows:


    as you had...

    Maybe you can also try addind dashes in the formula as:

    =TEXT(A1,"dd-mmm-yy")   or other combinations...

    Accepted Solution

    I finally found the solution that works for me. It came down to the order of creating the query and, strangely, saving it at proper times.

    1. Enter the parameter value in the cell where you want it to live (i.e. Parameters!A1)
    2. Build Query in Query Wizard.
    3. Save your Query
    4. Create the Criteria with the parameter (i.e. Create Time >=[Parameter1])
    5. Save the query again
    6. Exit MS Query
    7. From Excel, Data tab -> Existing Connections
    8. Click Browse for More and open your saved query.
    9. Place the results into your spreadsheet and select the cell where your parameter is.

    Author Closing Comment

    figured it out myself

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now