Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54

    Red face Unanswered: Sql Query in VBA won't work.

    I've looked for this in this forum and have tried many things but can't get this query to work. I'm wanting to place the query into code so that I can change the WHERE portion. I pasted this from the query but when run, it comes back with Err 2342 stating it requires a SQL statement. It previously pointed out an extra semi-colon and so I know its seeing the statement correctly.

    Fields [WorkDate] and [CerClock] are fields in the active record when this is run.

    Code:
    Dim SqlQuery As String
    
    SqlQuery = "SELECT [T:CurrPrData].Employee, [T:CurrPrData].CerClock, " & _
    "[T:CurrPrData].WeekNum, Sum([T:CurrPrData].[Reg Hours]) AS [SumOfReg Hours], " & _
    "Sum([T:CurrPrData].[Unpaid Hours]) AS [SumOfUnpaid Hours], Sum([T:CurrPrData].AltRateDayTtlHrs) AS SumOfAltRateDayTtlHrs " & _
    "FROM [T:CurrPrData]" & _
    "WHERE ((([T:CurrPrData].WorkDate)< #'" & [WorkDate] & "'#) " & _
    "AND (([T:CurrPrData].CerClock)='" & [CerClock] & "')) " & _
    "GROUP BY [T:CurrPrData].Employee, [T:CurrPrData].CerClock, [T:CurrPrData].WeekNum;"
    
    DoCmd.RunSQL SqlQuery
    I realize I could create many regular queries but I thought this would be more flexible and space saving. Perhaps not? Thanks for any help.

    Matt

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    where are the results of your SELECT going?

  3. #3
    Join Date
    Oct 2004
    Posts
    4

    RunSQL Method

    Hi Matt,

    I thought that the runsql method was only used for action queries.
    From Help - RunSQL method in VBA.

    sqlstatement A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

    For your application, you might want to create a querydef to run your select.
    HTH

    MidnightRun
    John

    Quote Originally Posted by mmwilcox
    I've looked for this in this forum and have tried many things but can't get this query to work. I'm wanting to place the query into code so that I can change the WHERE portion. I pasted this from the query but when run, it comes back with Err 2342 stating it requires a SQL statement. It previously pointed out an extra semi-colon and so I know its seeing the statement correctly.

    Fields [WorkDate] and [CerClock] are fields in the active record when this is run.

    Code:
    Dim SqlQuery As String
    
    SqlQuery = "SELECT [T:CurrPrData].Employee, [T:CurrPrData].CerClock, " & _
    "[T:CurrPrData].WeekNum, Sum([T:CurrPrData].[Reg Hours]) AS [SumOfReg Hours], " & _
    "Sum([T:CurrPrData].[Unpaid Hours]) AS [SumOfUnpaid Hours], Sum([T:CurrPrData].AltRateDayTtlHrs) AS SumOfAltRateDayTtlHrs " & _
    "FROM [T:CurrPrData]" & _
    "WHERE ((([T:CurrPrData].WorkDate)< #'" & [WorkDate] & "'#) " & _
    "AND (([T:CurrPrData].CerClock)='" & [CerClock] & "')) " & _
    "GROUP BY [T:CurrPrData].Employee, [T:CurrPrData].CerClock, [T:CurrPrData].WeekNum;"
    
    DoCmd.RunSQL SqlQuery
    I realize I could create many regular queries but I thought this would be more flexible and space saving. Perhaps not? Thanks for any help.

    Matt

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and does this need some work:
    #'" & [WorkDate] & "'#

    'string' and #date# ??

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Matt,

    Though I am not too knowledgeable of SQL in VBA, but there was something in this forum once I saw, but it had this in the beginning of the SQL statement:

    DoCmd.RunSQL

    I notice you have it at the end of your query. Does that make any difference?? Just trying to help as best I can with the tiny bit I know.

    have a nice one,
    Bud

  6. #6
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54
    Thanks for your replies. I'm not sure where to start since all of you provided useful input. I was hoping to run this query and then be able to access and use the results, such as [SumofReg Hours] in code that follows. E.g. If [SumOfReg Hours] > test1variable then... That was going to be my next text once I addressed the error messages. Is that not possible?

    If not, I'll go back to creating regular, saved queries for each of these summations that I need, each w/ its own criterion. It just seemed inefficient to save each specific query and open it/ close it in the code so that it refreshes. But maybe that's no different than creating a sql query in the code.

    Again, thanks for your input.

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you can only run action queries with RunSql method as MidnightRun said. You need to open a recordset with DAO or ADO and operate on that recordset.
    ghozy.

  8. #8
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54
    Okay. Thank you for the guidance. I'll be able to accomplish my goals through another avenue. Every improvement is a learning experience...

    Thanks again.

    Matt

  9. #9
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You should place the strSQL into the record source property of the form, list box or combo box that you want to use. For example, on a form in you could place this could (probably in an OnClick event of a command button):

    Me.RecordSource = strSQL

    The forms data will change to the new record source. You could also use a forms Filter property. The syntaxt for this is the same as a WHERE clause but without the word WHERE. You must also turn the filter on/off. So for example you would use:

    Me.Filter = "((([T:CurrPrData].WorkDate)< #'" & [WorkDate] & "'#) " & _
    "AND (([T:CurrPrData].CerClock)='" & [CerClock] & "')) "
    Me.FilterOn = True

    I prefer changing the record source as I have found this gives consistent results rather then the Filter property.

    If you have a subform and you want to change the record source you would use the syntaxt:

    Me("MySubFormName").Form.RecordSource = strSQL

    Regards
    Justin

Posting Permissions

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