Results 1 to 3 of 3
  1. #1
    ats00 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2016
    Posts
    1

    SQL in VBA showing missing operator in query expression

    I have a query inserted into VBA



    SELECT
    mtn.*FROM ( SELECT PATIENT_KEY, RENDERING_PROVIDER_KEY FROM main_table_name WHERE CPT_HCPS_PROC_CD IN ('82330', '82374', '82435', '82565', '82947', '84132', '84295', '84520') GROUPBY PATIENT_KEY, RENDERING_PROVIDER_KEY HAVINGCOUNT(DISTINCT CPT_HCPS_PROC_CD) = 8) AS pat_matchesINNERJOIN main_table_name mtn ON mtn.PATIENT_KEY = pat_matches.PATIENT_KEY AND mtn.RENDERING_PROVIDER_KEY = pat_matches.RENDERING_PROVIDER_KEYWHERE mtn.CPT_HCPS_PROC_CD IN ('82330', '82374', '82435', '82565', '82947', '84132', '84295', '84520');

    I tried the query but for some reason I kept getting a Syntax Error (missing operator) in query expression 'COUNT(DISTINCT CPT_HCPS_PROC_CD) = 8'. It seems something is not formatted correctly or missing. Any help is highly appreciated !

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Lots of things look strange about this: no spaces between some operators (*FROM; HAVINGCOUNT; pat_matchesINNERJOIN; RENDERING_PROVIDER_KEYWHERE)
    Something is missing here: main_table_name mtn but I can't tell what because both mtn and main_table_name look like tables, not one a field and the other a table
    You seem to be treating CPT_HCPS_PROC_CD as text in one place ('82330' and numeric in another = 8)
    Lastly, in my limited experience with sub queries (which is what you have) the sub query table and field references have to be aliased, which I don't see here.

    Your error might be due to 8 being text and you have no quotes around it. If so, I think you will move on to other errors based on the above.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    don't think

    COUNT(DISTINCT CPT_HCPS_PROC_CD)

    works - DISTINCT is associated with SELECT and not relevant on it's own

    So the error message is to do with the fact you have a space - it requires square brackets - and then of course you will get a different error because you don't have a field called 'DISTINCT CPT_HCPS_PROC_CD'

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-16-2016, 12:24 PM
  2. Replies: 2
    Last Post: 02-17-2014, 10:27 AM
  3. Replies: 1
    Last Post: 06-02-2013, 04:34 AM
  4. Replies: 9
    Last Post: 01-22-2013, 04:23 PM
  5. Replies: 1
    Last Post: 10-07-2009, 07:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums