Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Where is the variable?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    HUHandEH (2011-06-22)

  4. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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" _
    , "'})")

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    HUHandEH (2011-06-22)

  7. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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.


    Quote Originally Posted by HUHandEH View Post
    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" _
    , "'})")

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Huh? What is B S?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following User Says Thank You to rory For This Useful Post:

    HUHandEH (2011-06-22)

  10. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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.

  11. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Did you try the code I posted?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. The Following User Says Thank You to rory For This Useful Post:

    HUHandEH (2011-06-22)

  13. #9
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Yes I did, and I got the error , 1004 This operation is not available for extrenal Database.

    And Thank you for you effort.

  14. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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'})"
    Regards,
    Rory

    Microsoft MVP - Excel

  15. The Following User Says Thank You to rory For This Useful Post:

    HUHandEH (2011-06-22)

  16. #11
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •