Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Question Unanswered: Undefined function WHERE in Expression

    Hi Guys

    Im not very good with SQL so any help would be appreciated, I used:

    SELECT
    (SELECT Count(*) FROM [tbl_stock/equipment]) AS Bases,
    WHERE ((([tbl_stock/equipment].InternalID) Like "D*" And ([tbl_stock/equipment].InternalID) Not Like "DM*") AND (([tbl_stock/equipment].Sold)=0)),

    (SELECT Count(*)FROM [tbl_stock/equipment]) As Monitors,
    WHERE ((([tbl_stock/equipment].InternalID) Like "DM**") AND (([tbl_stock/equipment].Sold)=0)),

    Count(*) As Printers FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].InternalID) Like "PR***") AND (([tbl_stock/equipment].Sold)=0));

    and all Im getting is "Undefined function WHERE in Expression" and im stuck,

    What im trying to do is get a count based upon the results of the Like query, and i dont know where im going wrong.

    If i use:

    SELECT

    Count(*) AS Printers FROM [tbl_stock/equipment] WHERE ((([tbl_stock/equipment].[InternalID]) Like "PR***") And (([tbl_stock/equipment].[Sold])=0));

    I get a count for that result, its just when i try and add multiple columns i get problems. Any Ideas???

    Thanks
    Mike

  2. #2
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Talking which

    in which statement are u getting the error on?

  3. #3
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    the Top one, the one where im tring to do a count on multiple different "Like's"

  4. #4
    Join Date
    Sep 2004
    Posts
    161
    May be can you use this syntax :

    Code:
    SELECT Count(Field1]) AS [CompteDeCode OI], Mid([Field1],1,2) AS Expr2
    FROM [Table1]
    GROUP BY Mid([Field1],1,2)
    HAVING (((Mid([Field1],1,2)) Like "AU*")) OR (((Mid([Field1],1,2)) Like "ES*")) OR (((Mid([Field1],1,2)) Like "DE*"));
    This, give one record for each "like" with the count

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's a paranthesis issue. Take a look at that first subselect:

    (SELECT Count(*) FROM [tbl_stock/equipment]) AS Bases,
    WHERE ((([tbl_stock/equipment].InternalID) Like "D*" And ([tbl_stock/equipment].InternalID) Not Like "DM*") AND (([tbl_stock/equipment].Sold)=0))
    your select statement ends there. Anything you place after that is going to be invalid. Remember that with subselects, the ENTIRE statement must be enclosed in a single set of paranthesis, including the where clause.

    Aliases should be declared after the entire statement to avoid that sort of confusion. A proper version of the first statement may look like:

    (SELECT Count(*) FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].InternalID) Like "D*" And ([tbl_stock/equipment].InternalID) Not Like "DM*") AND (([tbl_stock/equipment].Sold)=0))) AS Bases,
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    will that reurn the resuls in there own seperat colums: Like
    Bases Monitors Printers
    10 2 6

    Not to sure how to use your syntax as quite new to sql and access

    Thanks
    Mike

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yup, that's the idea.

    Basically access is seeing a subselect, and then an unrelated function called where() that it doesn't like.

    (SELECT * FROM yourtable WHERE something=thatOtherThing) = good
    (SELECT * FROM yourtable) WHERE (something=ThatOtherThing) = bad
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    thanks teddy much appreciated, i will get to gripps with this one day.

    Thanks
    Mike

Posting Permissions

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