Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: Alias Error

    Hello all,

    I want to make an alias on my query for a feild.

    Here's my qry

    Code:
    SELECT hq_ITEM.ItemLookUpCode AS ITEM, SUM(hq_TRANSACTIONENTRY.Quantity) AS cPeriode(left(fdate(hq_TRANSACTION.Time),4) ,'y', -1, 'TOT')
    FROM hq_ITEM, hq_TRANSACTIONENTRY INNER JOIN hq_TRANSACTION ON hq_TRANSACTIONENTRY.TransactionNumber=hq_TRANSACTION.TransactionNumber
    WHERE hq_TRANSACTIONENTRY.ItemID = hq_ITEM.ID AND LEN(hq_ITEM.ItemLookUpCode) >= 14
    GROUP BY hq_ITEM.ItemLookUpCode
    ORDER BY hq_TRANSACTION.Time, hq_ITEM.ItemLookUpCode;
    I have some codes in a module file:
    Code:
    Function fdate(dtm As Date) As String
        fdate = Format(dtm, "yyyy-mm-dd")
    End Function
    Function cPeriode(str As String, x As String, y As Integer, prfx As String) As String
        Select Case x
            Case "m"
                str = Mid(str, 5, 2)
                MsgBox str
            Case "y"
                str = Left(str, 4)
            Case "d"
                str = Right(str, 2)
        End Select
        str = prfx & CStr((CInt(str) + y))
        cPeriode = str
    End Function
    Function cLEN(str As String) As Integer
        cLEN = Len(str)
    End Function
    When I try to run my query I got this message:
    The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the puctuation is incorrect
    Last edited by ortho; 11-21-06 at 12:50.

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    my alias should look like this

    TOT2005

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't think you can do what you want with the approach you are taking. If I understand it correctly, you should be able to do it with a Select query and a Crosstab query. First, select all the fields you want and then calculate the period for each record selected. Then create a crosstab query that uses your first query. ITEM will be your Row Headings, Period with be your Column Headings and the sum of the quantity will be the data.

Posting Permissions

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