?
Solved

Query in Excel 2010 Date Format Error

Posted on 2012-09-19
5
Medium Priority
?
613 Views
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?
0
Comment
Question by:cnrlzen
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38414711
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....
0
 

Author Comment

by:cnrlzen
ID: 38415118
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?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38417822
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:

WHERE (CUSTOMER_ORDER.CREATE_DATE>=?) AND (CUSTOMER_ORDER.CREATE_DATE<=?)

as you had...

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

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

Accepted Solution

by:
cnrlzen earned 0 total points
ID: 38418240
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.
0
 

Author Closing Comment

by:cnrlzen
ID: 38431895
figured it out myself
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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