Doing a DataBase thing where I pull the info, but I want to limit query.

Topic

New Reply

Hi Guys,

Been some time since I was last here, hope all is well with the forum.
I have a database query macro that I recorded, and want to modify the dates but everytime I put a variable to the date, it errors on me.

The Problem I beleive stems from this line here ts ‘2011-06-13 02:26:48……

Can anyone steer me in the right direction?

Thanks,
Darryl.

Range(“A1”).Select
With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DSN=VPC – PROD;Description=VPC – PROD;UID=datalink;PWD=datalink;APP=Microsoft® Query;WSID=VPC-PMXP-PLT1;DATABASE=VPC” _
, Destination:=Range(“A2”))
.CommandText = Array( _
“SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time” & Chr(13) & “” & Chr(10) & “FROM VPC.dbo.plate_flow plate_flow” & Chr(13) & “” & Chr(10) & “WHERE (plate_flow.line_number=’1’) AND (plate_flow.scanned_time>{ts ‘2011-06-13 02:26:48” _
, “‘})”)
.Name = “Query from VPC – PROD”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Reply | Quote

Replies

Where is the variable?

Reply | Quote

Option Explicit
Sub Macro1()
Dim sYear As String
Dim sMonth As String
Dim sDay As String
sYear = “2011”
sDay = “13”
sMonth = “06”
Range(“A1”).Select
With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DSN=VPC – PROD;Description=VPC – PROD;UID=datalink;PWD=datalink;APP=Microsoft® Query;WSID=VPC-PMXP-PLT1;DATABASE=VPC” _
, Destination:=Range(“A2”))
.CommandText = Array( _
“SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time” & Chr(13) & “” & Chr(10) & “FROM VPC.dbo.plate_flow plate_flow” & Chr(13) & “” & Chr(10) & “WHERE (plate_flow.line_number=’1′) AND (plate_flow.scanned_time>{ts ‘ syear-smonth-sday 02:26:48” _
, “‘})”)

Reply | Quote

rory, thanks for your reply

if you take a look at the variable, you will notice that in declaration there is a B S….but in the code it doesn’t see it.

Option Explicit
Sub Macro1()
Dim sYear As String
Dim sMonth As String
Dim sDay As String
sYear = “2011”
sDay = “13”
sMonth = “06”
Range(“A1”).Select
With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DSN=VPC – PROD;Description=VPC – PROD;UID=datalink;PWD=datalink;APP=Microsoft® Query;WSID=VPC-PMXP-PLT1;DATABASE=VPC” _
, Destination:=Range(“A2”))
.CommandText = Array( _
“SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time” & Chr(13) & “” & Chr(10) & “FROM VPC.dbo.plate_flow plate_flow” & Chr(13) & “” & Chr(10) & “WHERE (plate_flow.line_number=’1′) AND (plate_flow.scanned_time>{ts ‘ syear-smonth-sday 02:26:48” _
, “‘})”)

Reply | Quote

Code:

.CommandText = "SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time FROM VPC.dbo.plate_flow plate_flow WHERE (plate_flow.line_number='1') AND (plate_flow.scanned_time>{ts ' " & syear & "-" & smonth & "-" & sday & "00:00:00'}"

should work, I think.

Reply | Quote

Huh? What is B S?

Reply | Quote

oops…I meant the captial S, see how it didn’t change itself to a capital, if you declare the variable as a capital S, doesn’t see the s, and it keeps it small, I think the ‘ symbol is key to my problem.

Reply | Quote

Did you try the code I posted?

Reply | Quote

Yes I did, and I got the error , 1004 This operation is not available for extrenal Database.

And Thank you for you effort.

Reply | Quote

Just noticed a typo in my code:

Code:

.CommandText = "SELECT plate_flow.job_ref, plate_flow.line_number, plate_flow.scanned_time FROM VPC.dbo.plate_flow plate_flow WHERE (plate_flow.line_number='1') AND (plate_flow.scanned_time>{ts ' " & syear & "-" & smonth & "-" & sday & "00:00:00'})"

Reply | Quote

I didn’t notice any mispells, the problem stems from the variable string, which I think was a mistake on my part. The special notation ‘ for some reason needs a valid date variable imo. Once I changed the variable to date, and gave a two variable which worked.

Dim xdate1 as date
Dim ydate2 as date

xdate1=(00:00:00)
ydate2=the date value in the format year/month/day format.

you need to keep the special notation I am guessing cause it worked fine from that point on, if you add a calander control you can access a database and fire it when you click on the calander.

Thanks Rory. I didn’t think the problem was in the variable.

Reply | Quote