Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    access 97 sr2 (sql syntax question)

    Can you tell me the correct syntex for this line of code I am sure if it needs quotation marks or brackets etc.
    strsqrl = "Select count (*) as recordcount from [file_check1]" - this currently is working fine what I need to do is add a where clause
    *********[file_check1] is a query*********

    where date field in table = passed in parameter

    Thanks in advance for your help

    Cathy

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

    Re: access 97 sr2 (sql syntax question)

    If the parameter value is stored in a variable dtmParam of type Date, you can use:

    strSQL = "SELECT Count(*) AS RecordCount FROM [File_Check1] WHERE [DateField] = #" & Format(dtmParam, "mm/dd/yyyy") & "#"

    If you're in the USA, the Format function isn't strictly necessary, it ensures that the date will be interpreted correctly on systems with non-US date formats too.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: access 97 sr2 (sql syntax question)

    Hans, I am in the US so tell me if I understand what you are saying.

    dim dtmparam as date

    strSQL = "SELECT Count(*) AS RecordCount FROM [File_Check1] WHERE [DateField] = #" & (dtmParam) & "#"

    ** would this be correct or do I need quotes around dtimpaam

    Thanks so much for your quick response you are the best!

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

    Re: access 97 sr2 (sql syntax question)

    You can simply use
    <code>
    strSQL = "SELECT Count(*) AS RecordCount FROM [File_Check1] WHERE [DateField] = #" & dtmParam & "#"
    </code>
    No quotes or parentheses needed around dtmParam. The # characters are essential, however, for a date parameter.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: access 97 sr2 (sql syntax question)

    Thanks you so very much

    Have a good day
    Cathy

Posting Permissions

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