[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel/MS Query

Posted on 2014-02-27
8
Medium Priority
?
36 Views
Last Modified: 2016-02-25
I have an external database that has date field, when I use >=#12/1/2014# in the value field it runs fine, when I put >=[somedate] and prompt the user to enter the date, I get a CONVERSION ERROR, it does not matter how I enter the date:  12/1/14, 12/01/14, 12/01/2014, 01-DEC-14, 01-DEC-2014, or any other variant you can think of, they all give the same error.
0
Comment
Question by:charlie3104
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39893329
In the MS Query editor window, select the View menu, then choose Parameters.  Select the parameter 'somedate' and click the Edit button.  Make sure you have the data type for the parameter specified as DATETIME

OM Gang
0
 

Author Comment

by:charlie3104
ID: 39894728
not an option.  The Datatype is set to DATE, and it is grayed out, so I can not alter.
0
 
LVL 28

Expert Comment

by:omgang
ID: 39895517
What version of Excel and how did you create the query?  When I look at the parameters option it's not greyed out.  I tested in Excel 2007 but can also test in 2010.
OM Gang
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:charlie3104
ID: 39895648
I am running excel 2003, but the same thing happens in 2007.  I have been researching on line, I think it has something to do with the ODBC driver and the way the data is passed.  It is a PROVIDEX database, and it says the field is a DATE, so when you look at the parameter properties to try to change, it is grayed out because it is tied to the FIELD that I am trying to provide the parameter to match.  The ProvideX odbc driver does not know how to handle the date as a date, and it would let me cast it into a a DateTimeStamp.  So it errors out.  At least that is what I am reading online.

I even tried passing the parameter from a Linked cell in the excel file, I get the same CONVERSION ERROR, reguardless of how the cell is formatted as well.  

I guess I am going to have to hard code the date and edit the query as needed.
0
 
LVL 28

Expert Comment

by:omgang
ID: 39895778
I don't have a ProvideX db to experiment with.  Are you working with Mas90 or similar?  I believe I have some code from a legacy application where I changed/modified the MS Query SQL statement on the fly.  I'm thinking this may work for you as you could prompt the user for the date range values and then construct the SQL statement for the MS Query and then refresh.  I'll see what I can dig up.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39895996
I put together some sample code for you.  Give this a shot and see if it will work.
For production use you'd want to validate the string input by the user to make sure it is in the correct format.
OM Gang


Sub ChangeSQLString()
On Error GoTo Err_ChangeSQLString

    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection
    Dim dteDateCriteria As Date
    Dim strPrompt As String, strCriteria As String, strSQL As String
    Dim varResponse As Variant
   
    'prompt user for data param
    strPrompt = "Please input your date parameter in the following format"
    strPrompt = strPrompt & vbCrLf & "YYYY-MM-DD"
    varResponse = InputBox(strPrompt, "Enter Date")
    If IsNull(varResponse) Then
        GoTo Exit_ChangeSQLString
    End If
   
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection
            Debug.Print odbcCn.CommandText
           
            strCriteria = "WHERE (data.dDate>={d '" & varResponse & "'})"
           
                'get command text from connection
            strSQL = odbcCn.CommandText
           
                'remove existing WHERE clause
            strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
            Debug.Print strSQL
           
                'append new WHERE clause
            strSQL = strSQL & strCriteria
            Debug.Print strSQL
           
                'update the connection command
            odbcCn.CommandText = strSQL
           
                'refresh the sheet
            ThisWorkbook.RefreshAll
           
        End If
    Next

Exit_ChangeSQLString:
        'destroy object variable
    Set odbcCn = Nothing
    Exit Sub
   
Err_ChangeSQLString:
    MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error in Sub ChangeSQLString"
    Resume Exit_ChangeSQLString
   
End Sub
0
 

Author Comment

by:charlie3104
ID: 39896174
yes, it is a MAS200 database using the ProvideX ODBC.  The Dim statements As WorkbookConnection & As ODBCConnection are not available in the list of available options (my guess is they are Excel 2007 or higher)  So the sample code you gave me will not compile/run.
0
 
LVL 28

Accepted Solution

by:
omgang earned 1500 total points
ID: 39896223
My legacy app was originally written in Excel 2003 and in that code I did use something different.

Dim qt As QueryTable

With ActiveWorkbook.Sheets(1)
    If .QueryTables.Count <> 0 Then
        Set qt = .QueryTables(1)



In that app I was changing the ConnectionString (qt.Connection) for the source db.  I don't have Excel 2003 available right now so can't test for you but from the MSDN article http://msdn.microsoft.com/en-us/library/office/ff836161.aspx

You should be able to change the SQL statement

Dim qt As QueryTable
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
 "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
 Destination:=Range("B1"), Sql:=sqlstring)
 .Refresh
End With

OM Gang
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

656 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