Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unknown Error (A2K)

    I have the following sql code in a query that works perfectly in one database but not another and not sure I understand why. Attached is the error message I get, but when I select "HELP", I don't can't anything, so needless to say...I can't seem to resolve this issue.

    <pre>SELECT [sfd]-nz((SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID WHERE ID.STD< tIP_DEMOS_DX.SFD_
    AND ID.SSN=tIP_DEMOS_DX.SSN ORDER BY ID.STD DESC,[sfd]+1) AS DaysSinceLastVisit,_
    tIP_DEMOS_DX.[maxMC#], tIP_DEMOS_DX.SSN AS SSN, tIP_DEMOS_DX.SFD, tIP_DEMOS_DX.STD,_
    tIP_DEMOS_DX.AUTHORIZATION_NO, tIP_DEMOS_DX.FACILITY_NAME, tIP_DEMOS_DX.PRIMARY_DX_DECIMAL,_
    tIP_DEMOS_DX.DISCHARGE_DX,_
    tIP_DEMOS_DX.DISCHARGE_DATE, tIP_DEMOS_DX.TOTAL_CERTIFIED_DAYS
    FROM tIP_DEMOS_DX
    WHERE ((([sfd]-nz((SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID WHERE ID.STD< tIP_DEMOS_DX.SFD AND_
    ID.SSN=tIP_DEMOS_DX.SSN ORDER BY ID.STD DESC,[sfd]+1)) Between 0 And 30));
    </pre>



    The data type for each field is the same as it is in the database where the subquery works just fine.

    Could someone please provide some insight on this?

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unknown Error (A2K)

    I don't see a screenshot or attachment, but anyway, I think we'll have to see the problem database.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unknown Error (A2K)

    I'll try it again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unknown Error (A2K)

    The error message indicates that the subquery

    (SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID WHERE ID.STD< tIP_DEMOS_DX.SFD AND_ID.SSN=tIP_DEMOS_DX.SSN ORDER BY ID.STD DESC

    doesn't return any records for at least one of the records in the main query. What happens if you replace the subquery with

    DMax("ID", "tIP_DEMOS_DX", "STD < " & [SFD] & " AND SSN = " & [SSN])

    If SFD and/or SSN are text fields, you must surround their values with double quotes, for example for SSN:

    DMax("ID", "tIP_DEMOS_DX", "STD < " & [SFD] & " AND SSN = " & Chr(34) & [SSN] & Chr(34))

Posting Permissions

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