Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: syntax for where criteria in the openform method

    What is the correct syntax for where criteria in the openform method?
    I would like to compare the shipped date in the form based on a table with the input date. The error occurred in the syntax in input date because I tested with an actual date and is working.
    I tried various ways but still can't figure out what is the syntax for the date.
    I'm using Access 2000 as FE and SQL Server 7.0 as BE.

    This is what I have
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='& # bdt # &'"

    Thanks for your help!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try taking the "#" signs out. SQL Server doesn't like them.

  3. #3
    Join Date
    May 2002
    Posts
    395

    syntax in where condition of the openform method

    Originally posted by RedNeckGeek
    Try taking the "#" signs out. SQL Server doesn't like them.
    No, it still doesn't work.
    It works with an actual date.
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='7/8/2003'"

    any other suggestions?

    Thanks!

  4. #4
    Join Date
    May 2002
    Posts
    395

    Re: syntax in where condition of the openform method

    Originally posted by alicejwz
    No, it still doesn't work.
    It works with an actual date.
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='7/8/2003'"

    any other suggestions?

    Thanks!
    I got the right syntax. It is look like this

    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] = " & "'" & bdt & "'"

    Thanks for your help!

  5. #5
    Join Date
    May 2002
    Posts
    395

    syntax error in openform method

    but when I combined the two conditions together it doesn't work.

    this doesn't work - "Type Mismatch"
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] >= " & "'" & bdt & "'" And "[shipped_date] <= " & " '" & edt & "'"


    These all work
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] = " & "'" & bdt & "'"
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] ='7/8/2003'"
    DoCmd.OpenForm "shipment_hist_list", , , "[shipped_date] <= " & " '" & edt & "'"

    Please take a look. Thanks!

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try this:
    [shipped_date] between '" & bdt & "' and '" & edt & "'

Posting Permissions

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