Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New set of eyes (A2K)

    Your syntax error seems to indicate, by the ##, that it is not reading the [Admit_Date] field properly. Have you tried substituting something less esoteric than that particular date formatting to see if that is what is causing the problem? I haven't seen formatting for a date using both and /. Why don't you try plain old dashes and see what Access thinks?

    Just some thoughts. HTH

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

    New set of eyes (A2K)

    Edited by HansV to break enormously long line that caused horizontal scrolling
    I have this field in a query:
    <pre>PrevSTD: CDate(Nz(DMax("Discharge_Date","tIP_DEMOS_DX","Dis charge_Date<#" &
    Format([ADMIT_Date],"mm/dd/yyyy") & "# AND SSN=" & Chr(34) & [SSN] &
    Chr(34)),#1/1/1900#))</pre>


    with a parameter of:
    <pre>>=[Discharge_Date]-30</pre>


    but continue to get an error of:
    <pre>Syntax error in date in query expression 'Discharge Date <## AND SSN="a ssn here".</pre>


    When I hit "OK" it returns:
    <pre>Unknown </pre>


    What on earth am I missing?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: New set of eyes (A2K)

    Apparently ADMIT_Date can be null (empty). Perhaps you should restrict the query to records for which ADMIT_Date is not null, otherwise you must use another Nz function to replace nulls in ADMIT_Date with a valid date.

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

    Re: New set of eyes (A2K)

    Thanks Hans, it was the NULL factor that was creating the problem. I included the Nz in the syntax and all was well.

    I resolved this a week ago but I forgot I posted this question. Sorry for the delay in response.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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