SQL Query reference cell

[Dharris144]

I have a sheet with a query that i need to change the date every month. This is ok for me but not an average end user
Is it possible to change DECLARE @D Date Set @D = '05/31/2017' to DECLARE @D Date Set @D = Cell reference here ?
Or something to that affect

Excel 2016

 

[Worf]

See if this VBA example helps:

Sub Macro2()
Dim d$
d = Format([p55], "yyyy-mm-dd")                                                     ' from cell P55
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\Eddie\Desktop\Campaign_Template13.mdb;" & _
"DefaultDir=C:\Users\Eddie\Desktop;DriverId=25;FIL=MS " _
), Array("Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$v$61")).QueryTable
.CommandText = Array( _
"SELECT Campaign_Table.ProductID, Campaign_Table.Date_Added" & Chr(13) & "" & Chr(10) & _
"FROM `C:\Users\Eddie\Desktop\Campaign_Template13.mdb`.Campaign_Table Campaign_Table" & Chr(13) & _
"" & Chr(10) & "WHERE (Campaign_Table.Date_Added>{ts '" & d & " 00:00:", "00'})" & Chr(13) & "" & _
Chr(10) & "ORDER BY Campaign_Table.Date_Added")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False:             .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False:                  .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0:                     .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table5"
    .Refresh BackgroundQuery:=False
End With
End Sub

 

[Dharris144]

See if this VBA example helps:

Sub Macro2()
Dim d$
d = Format([p55], "yyyy-mm-dd")                                                     ' from cell P55
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\Eddie\Desktop\Campaign_Template13.mdb;" & _
"DefaultDir=C:\Users\Eddie\Desktop;DriverId=25;FIL=MS " _
), Array("Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$v$61")).QueryTable
.CommandText = Array( _
"SELECT Campaign_Table.ProductID, Campaign_Table.Date_Added" & Chr(13) & "" & Chr(10) & _
"FROM `C:\Users\Eddie\Desktop\Campaign_Template13.mdb`.Campaign_Table Campaign_Table" & Chr(13) & _
"" & Chr(10) & "WHERE (Campaign_Table.Date_Added>{ts '" & d & " 00:00:", "00'})" & Chr(13) & "" & _
Chr(10) & "ORDER BY Campaign_Table.Date_Added")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False:             .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False:                  .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0:                     .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table5"
    .Refresh BackgroundQuery:=False
End With
End Sub

So I just run my SQL query within a macro? I never thought of that. I guess if that is the case I could create a pop up window prompting for the date and set the variable that way?

 

[Worf]

Like this:

Sub Macro2()
Dim d$
d = Application.InputBox("Enter date as yyyy-mm-dd", , Format(Now(), "yyyy-mm-dd"), , , , , 2)
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\Eddie\Desktop\Campaign_Template13.mdb;" & _
"DefaultDir=C:\Users\Eddie\Desktop;DriverId=25;FIL=MS " _
), Array("Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$a$15")).QueryTable
    .CommandText = Array( _
    "SELECT Campaign_Table.ProductID, Campaign_Table.Date_Added" & Chr(13) & "" & Chr(10) & _
    "FROM `C:\Users\Eddie\Desktop\Campaign_Template13.mdb`.Campaign_Table Campaign_Table" & Chr(13) & _
    "" & Chr(10) & "WHERE (Campaign_Table.Date_Added>{ts '" & d & " 00:00:", "00'})" & Chr(13) & "" & _
    Chr(10) & "ORDER BY Campaign_Table.Date_Added")
    .RowNumbers = False:                    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False:             .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False:                  .SaveData = True
    .AdjustColumnWidth = True:              .RefreshPeriod = 0
    .ListObject.DisplayName = "Table7"
    .Refresh BackgroundQuery:=False
End With
End Sub

 

[Dharris144]

Like this:

Sub Macro2()
Dim d$
d = Application.InputBox("Enter date as yyyy-mm-dd", , Format(Now(), "yyyy-mm-dd"), , , , , 2)
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\Eddie\Desktop\Campaign_Template13.mdb;" & _
"DefaultDir=C:\Users\Eddie\Desktop;DriverId=25;FIL=MS " _
), Array("Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$a$15")).QueryTable
    .CommandText = Array( _
    "SELECT Campaign_Table.ProductID, Campaign_Table.Date_Added" & Chr(13) & "" & Chr(10) & _
    "FROM `C:\Users\Eddie\Desktop\Campaign_Template13.mdb`.Campaign_Table Campaign_Table" & Chr(13) & _
    "" & Chr(10) & "WHERE (Campaign_Table.Date_Added>{ts '" & d & " 00:00:", "00'})" & Chr(13) & "" & _
    Chr(10) & "ORDER BY Campaign_Table.Date_Added")
    .RowNumbers = False:                    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False:             .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False:                  .SaveData = True
    .AdjustColumnWidth = True:              .RefreshPeriod = 0
    .ListObject.DisplayName = "Table7"
    .Refresh BackgroundQuery:=False
End With
End Sub

Perfect thank you very much

 

[Worf]

You are welcome and thanks for the feedback.