Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25

    Unanswered: SQL Statement, alias in where clause

    Code:
        
    With recordset
    
        strDate1 = Worksheets(1).Cells(1, 5)
        strDate2 = Worksheets(1).Cells(1, 6)
    
    Src = "SELECT qryMergeColumns.userNumber, dbo_ViewUser.FirstName, dbo_ViewUser.LastName, "
        Src = Src & "dbo_ViewDropDownFields.NameInIndex, dbo_ViewEvents.AreaNumberText, dbo_ViewEvents.DoorNumberText, "
        Src = Src & "dbo_ViewEvents.MPCLocation, dbo_ViewDropDownFields.Index_, dbo_ViewUser.CardNumber, "
        Src = Src & "CVDate([EventDT] - 8 / 24) As testingBlah "
    
        Src = Src & "FROM (dbo_ViewDropDownFields INNER JOIN (qryMergeColumns INNER JOIN dbo_ViewUser ON qryMergeColumns.userNumber = dbo_ViewUser.UserNumber) ON dbo_ViewDropDownFields.DropDownID = qryMergeColumns.UserInfoDropDown1) INNER JOIN dbo_ViewEvents ON dbo_ViewUser.UserNumber = dbo_ViewEvents.UserNumber "
        Src = Src & "WHERE testingBlah between CVDate('" & strDate1 & "') and CVDate('" & strDate2 & "') "
        Src = Src & "ORDER BY qryMergeColumns.userNumber DESC , CVDate([EventDT]-8/24);"
    
    .Open Source:=Src, ActiveConnection:=Connection
    I'm using a query in Excel VBA to talk to an access database but I'm having problems now that I'm not doing select * from qryAccessName and actually building the query in VB.

    I had code that relied on a query but now I'm just trying to be more direct and only pulling the records I need. But the alias is giving me an Automation error.
    http://support.microsoft.com/default...;en-us;Q306518

    Code:
    Error Message
    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    [Microsoft][ODBC Microsoft Access 97 Driver]
    Too few parameters. Expected 1. 
    Cause
    This error occurs because the column name that you used in the query syntax does not exist. Often this error is just a typographical error. Check the column names in a database against your query string. If you are using Microsoft Access, make sure that the actual column name is used and not a column's "display" name.
    My alias is currently known as Testblah because I was worried maybe I was using a reserved word, or a variable name already used.

    So can you not refer to an alias that is defined in your query in a where statement or am I making some other error?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think instead of using:
    WHERE testingBlah between ...

    you need to use
    WHERE CVDate([EventDT] - 8 / 24) between ...

    You can't use an alias name in a where or a group by statement.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Awesome thanks for your help. That rules sounds vaguely familar from an Oracle class not too long ago.

Posting Permissions

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