• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 40
  • Last Modified:

Excel/MS Query

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
charlie3104
Asked:
charlie3104
  • 5
  • 3
1 Solution
 
omgangCommented:
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
 
charlie3104Author Commented:
not an option.  The Datatype is set to DATE, and it is grayed out, so I can not alter.
0
 
omgangCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
charlie3104Author Commented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
charlie3104Author Commented:
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
 
omgangCommented:
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 expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now