Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to run a sql query from an on click event from a button. Here is my event:

    Private Sub Command44_Click()

    Dim dbs As Database, qdf As QueryDef, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    ' Construct SQL statement including parameters.
    qdf.sql = "SELECT [Line Item Header].ItemDescription, [Line Item Header].ItemID, [Ticket Register].PhaseID, [Ticket Register].TicketNumber, [Ticket Register].TicketDate, [Ticket Register].NRecord AS Unknown1, [Ticket Register].BillingAmount, [Ticket Register].BillingRate, [Ticket Register].DurationHours, [Ticket Register].DurationMinutes, [durationhours]+[durationminutes]/60 AS [Time], [Ticket Register].RecordedByID, nz([EmployeeName],[RecordedByID]) AS EmployeeName1, "Billable" AS Status, [Job Data].JobDescription, [Job Data].Supervisor, [Job Data].PONumber, [Customer Header].CustomerID, [Ticket Register].ARUsed, [Ticket Register].Description, [Ticket Register].VendorFlag, [Ticket Register].ItemClass, [Ticket Register].Memo, [Employee Header].CustomField2, nz([job estimate].[revenues],0) AS Revenues, [Job Data].JobType, [Ticket Register].BillingStatus, [Ticket Register].CompletedForID, [Employee Header].EmployeeName" & _
    "FROM ((((([Ticket Register] LEFT JOIN [Employee Header] ON [Ticket Register].RecordedByID = [Employee Header].EmployeeID) INNER JOIN [Job Data] ON [Ticket Register].CompletedForID = [Job Data].JobID) INNER JOIN [Line Item Header] ON [Ticket Register].ItemIndex = [Line Item Header].Index) LEFT JOIN [Customer Header] ON [Job Data].CustomerIndex = [Customer Header].Index) LEFT JOIN [ThreeI ItemID List] ON [Line Item Header].ItemID = [ThreeI ItemID List].ItemID) LEFT JOIN [Job Estimate] ON [Job Data].Index = [Job Estimate].JobIndex" & _
    "WHERE ((([Ticket Register].TicketDate) >= [Forms]![ThreeI frm Main Menu]![txtStDate2] And ([Ticket Register].TicketDate) <= [Forms]![ThreeI frm Main Menu]![txtEndDate2]) And (([Ticket Register].NRecord) = 0) And (([Ticket Register].BillingAmount) > 0) And (([Customer Header].CustomerID) Like "*vectren int*") And (([Ticket Register].ItemClass) >= 0) And (([Ticket Register].BillingStatus) = 1))" & _
    "ORDER BY [Ticket Register].CompletedForID;"

    ' Create snapshot-type Recordset object from QueryDef object.
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    ' Perform operations with recordset.


    rst.Close
    Set dbs = Nothing

    End Sub

    The query is red and I am not sure why. Can someone please tell me what is wrong?
    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try single quotes around '*vectren int*' and 'Billing' like so:

    Option Compare Database

    Private Sub Command44_Click()

    Dim dbs As Database, qdf As QueryDef, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    ' Construct SQL statement including parameters.
    qdf.SQL = "SELECT [Line Item Header].ItemDescription, [Line Item Header].ItemID, [Ticket Register].PhaseID, [Ticket Register].TicketNumber, [Ticket Register].TicketDate, [Ticket Register].NRecord AS Unknown1, [Ticket Register].BillingAmount, [Ticket Register].BillingRate, [Ticket Register].DurationHours, [Ticket Register].DurationMinutes, [durationhours]+[durationminutes]/60 AS [Time], [Ticket Register].RecordedByID, nz([EmployeeName],[RecordedByID]) AS EmployeeName1, 'Billable' AS Status, [Job Data].JobDescription, [Job Data].Supervisor, [Job Data].PONumber, [Customer Header].CustomerID, [Ticket Register].ARUsed, [Ticket Register].Description, [Ticket Register].VendorFlag, [Ticket Register].ItemClass, [Ticket Register].Memo, [Employee Header].CustomField2, nz([job estimate].[revenues],0) AS Revenues, [Job Data].JobType, [Ticket Register].BillingStatus, [Ticket Register].CompletedForID, [Employee Header].EmployeeName" & _
    "FROM ((((([Ticket Register] LEFT JOIN [Employee Header] ON [Ticket Register].RecordedByID = [Employee Header].EmployeeID) INNER JOIN [Job Data] ON [Ticket Register].CompletedForID = [Job Data].JobID) INNER JOIN [Line Item Header] ON [Ticket Register].ItemIndex = [Line Item Header].Index) LEFT JOIN [Customer Header] ON [Job Data].CustomerIndex = [Customer Header].Index) LEFT JOIN [ThreeI ItemID List] ON [Line Item Header].ItemID = [ThreeI ItemID List].ItemID) LEFT JOIN [Job Estimate] ON [Job Data].Index = [Job Estimate].JobIndex" & _
    "WHERE ((([Ticket Register].TicketDate) >= [Forms]![ThreeI frm Main Menu]![txtStDate2] And ([Ticket Register].TicketDate) <= [Forms]![ThreeI frm Main Menu]![txtEndDate2]) And (([Ticket Register].NRecord) = 0) And (([Ticket Register].BillingAmount) > 0) And (([Customer Header].CustomerID) Like '*vectren int*') And (([Ticket Register].ItemClass) >= 0) And (([Ticket Register].BillingStatus) = 1))" & _
    "ORDER BY [Ticket Register].CompletedForID;"

    ' Create snapshot-type Recordset object from QueryDef object.
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    ' Perform operations with recordset.


    rst.Close
    Set dbs = Nothing

    End Sub
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Jerry has already nailed the problem and provided a solution.

    For background info about this problem, see Post 230516.

Posting Permissions

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