Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter query (97)

    Hello all <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    I have a query running that shows information until a given date.
    When putting this in the query as a parameter it works fine, but when using a popup field to enter the date when you run the query, the results are seldom good. Most of the time the result shows information from later dates.

    Why?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query (97)

    When passing a date to a query, you have to use the us format mm/dd/yyyy.
    Can you post the sql string you are using?
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query (97)

    Hello Francois,

    Thanks for the reaction. We have a dutch version here so the format is dd/mm/yyyy. I tried different formats
    Here is the SQL statement:

    SELECT CallFormulier.BerekendeDeadline, CallFormulier.DatumIn, Date()-[BerekendeDeadline] AS [Dagen te laat], CallFormulier.Probleembeschrijving, CallFormulier.Callnummer, CallFormulier.OpenCalls, CallFormulier.TijdsduurGeschat, CallFormulier.NG, CallFormulier.GH, CallFormulier.JV, CallFormulier.PS, CallFormulier.Status.Status
    FROM CallFormulier
    WHERE (((CallFormulier.BerekendeDeadline)<=[Tot en met welke datum?]) AND ((CallFormulier.OpenCalls)=-1) AND ((CallFormulier.GA)=-1))
    ORDER BY CallFormulier.BerekendeDeadline;

    If I replace [Tot en met welke datum?] with 30-09-2004 the result is up to 30-08-2004, but if I run the query the way it is en type 30-09-2004 in the popup te result shows date up to 14-10-2004

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query (97)

    I found the solution by adding the following to the SQL

    PARAMETERS [Tot en met welke datum?] DateTime;

    Thanks anyway for thinking with me Fran

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query (97)

    When ever prompted for dates, I always like to enter them dd mmm yyyy (ie. 2 sep 2004). This way, there is absolutely no ambiquity in what I intend the date to be (and MS Access handles the convertion correctly). This may help you in the future (although probably not with this particular problem [img]/forums/images/smilies/wink.gif[/img] )

    Like you Patric, being in New Zealand, our dates are regionally formatted dd/mm/yyyy , but because of the more 'native' use of mm/dd/yyyy I have had a great deal of headaches in the past.

    Glad you found a solution.

Posting Permissions

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