Solved

Excel/MS Query

Posted on 2014-02-27
8
26 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

920 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

16 Experts available now in Live!

Get 1:1 Help Now