Solved

Excel/MS Query

Posted on 2014-02-27
8
25 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

708 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

14 Experts available now in Live!

Get 1:1 Help Now