Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VB SQL, get date from Excel for SQL query in VB

Posted on 2009-05-06
4
Medium Priority
?
779 Views
Last Modified: 2013-12-26
Hi, pardon my english. I'm having problem getting a date from an excel column, and then use it in VB as a condition query in SQL.
This is what I've tried, let's say the date is in column A1, the value is '2009-05-06'
It will work if I done it manually in VB, under the SQL query:
WHERE Sales.InvoiceDate= '2009-05-06'

So I tried getting the date from excel and store it in an object (what does it call)
Dim DateExcel as Date
DateExcel = cell(1,1)
WHERE Sales.InvoiceDate= DateExcel

Couldn't work, so I tried 1 step back
Dim DateExcel as Date
DateExcel = "2009-05-06"
WHERE Sales.InvoiceDate= DateExcel

Couldn't work either. I tried changing the DateExcel to string, declaring the DateExcel on top (declarations), nothing works.

I use Excel macro recorder to get the VB lines for getting data from ODBC, i'll attach the recorded code below. I have little knowledge about the formats so I go by trial and errors, so there might be lots of mistakes in what i'm trying to do. Please point out where was I wrong.
Sub Macro17()
'
' Macro12 Macro
'
Dim DateExcel As Date
DateExcel = "2009-05-06"
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=SB MYOB 2009;", Destination:=Range("$C$4")).QueryTable
        .CommandText = Array( _
        "SELECT Sales.SaleID, Sales.InvoiceDate AS 'Date', Sales.InvoiceNumber AS 'Sales No', Items.ItemNumber AS 'Type', Sales.ShipToAddress AS 'Finance', Sales.TotalLines AS 'Price', Sales.TotalPaid AS 'Cash" _
        , _
        " DP'" & Chr(13) & "" & Chr(10) & "FROM Items Items, ItemSaleLines ItemSaleLines, Jobs Jobs, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE (Jobs.JobNumber='SLB') AND (ItemSaleLines.SaleID=Sales.SaleID) AND (Jobs.JobID=ItemSaleLines.JobID) AND (Items.ItemID" _
        , _
        "=ItemSaleLines.ItemID) AND (Items.ItemIsInventoried='Y') AND (Sales.InvoiceDate= nenenku )" & Chr(13) & "" & Chr(10) & "ORDER BY Sales.SaleID" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_SB_MYOB_2009_1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
Comment
Question by:MachoLinKz
[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
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 24312942
If your cell contains an actual date, rather than text, you can use:
 

Sub Macro17()
'
' Macro12 Macro
'
Dim DateExcel As Date
' change range reference as required
DateExcel = Range("A1").Value 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=SB MYOB 2009;", Destination:=Range("$C$4")).QueryTable
        .CommandText = Array( _
        "SELECT Sales.SaleID, Sales.InvoiceDate AS 'Date', Sales.InvoiceNumber AS 'Sales No', Items.ItemNumber AS 'Type', Sales.ShipToAddress AS 'Finance', Sales.TotalLines AS 'Price', Sales.TotalPaid AS 'Cash" _
        , _
        " DP'" & Chr(13) & "" & Chr(10) & "FROM Items Items, ItemSaleLines ItemSaleLines, Jobs Jobs, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE (Jobs.JobNumber='SLB') AND (ItemSaleLines.SaleID=Sales.SaleID) AND (Jobs.JobID=ItemSaleLines.JobID) AND (Items.ItemID" _
        , _
        "=ItemSaleLines.ItemID) AND (Items.ItemIsInventoried='Y') AND (Sales.InvoiceDate='" & Format(DateExcel, "yyyy-mm-dd") & "') " & Chr(13) & "" & Chr(10) & "ORDER BY Sales.SaleID" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_SB_MYOB_2009_1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
 
LVL 25

Expert Comment

by:reb73
ID: 24312973
You have to cast ExcelDate to character data and enclose within single quotes like -

"WHERE Sales.InvoiceDate= '" & format(DateExcel, "yyyy-mm-dd") & "'"

0
 

Author Closing Comment

by:MachoLinKz
ID: 31578376
Spot On! Thanks a bunch Ror!~
0
 

Author Comment

by:MachoLinKz
ID: 24313029
Thanks for the input guys~ Saved me hours of looking around
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

636 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