Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39

    Unanswered: Parameter query on a calculated field containing dates

    Hi,

    I'm having yet another problem

    I'm hoping explorer will behave this time - I had a fatal error last time I tried to post this question. Anyways....

    I'm trying to take a field containing dates (DateCommenced) and add five years to it. I've achieved this with:
    AccredDue: DateAdd("yyyy",+5,[DateCommenced])
    as the field name in my query. This works fine by itself.

    The problem occurs when I try to add a parameta criteria:
    Between [Enter Beginning Date] And [Enter End Date]

    This will sometimes give me the results I want, but not always. The idea is to type in two dates (ie 1/1/04 and 1/12/04) and the query will return any entries from the DateCommenced field between these dates, but five years ago (ie between 1/1/99 and 1/12/99)

    Part of the problem seems to be that:
    a) Access seems to alternate between American and Australian format dates (ie 12/30/04 and 30/12/04) - I'm Australian just for the record
    b) The parameter entered is only recognised as a date when it is entered in full (01/01/2004 as opposed to 1/1/04)

    I could be wrong here, but that is what seems to be happening.

    Also, my teacher can only suggest that I "stick to something simpler" - any ideas on this? (I can't bring myself to argue that this would be simple if it worked )

    Any help or suggestions regarding this would be greatly appreciated

    Cheers,
    frekster

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    try using a popup form where the user can enter the dates, instead of them ugly query popups. Then refering to the form for parameters.....

    Access is tricky when it comes to dates, it is expecting American format in query criteria (as in others btw)

    GL

  3. #3
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39
    Thanks for the suggestion namliam.

    Would you (or anyone else who knows) mind giving me a quick run through on how to go about this?

    Also, is there a way to change the default date format? Or am I stuck (:P) with it defaulting to American style?

    frekster

  4. #4
    Join Date
    May 2004
    Location
    Moscow, Russia
    Posts
    29
    Between clng([Enter Beginning Date]) And clng([Enter End Date])

    1 if date without time
    2 MSAccess only sql

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Stuck with the American format i am affraid....

    I am unsure what Alexy is on about...

    But on forms you can monitor the input.
    So create a form with 2 text boxes.
    Then make a button on the form "Run Query"
    Then in the query do something like:
    Between format(Forms!Form1!txtDate1,"mm/dd/yyyy") and ...

    This will ensure american format AND give you a slicker looking interface, since you can make the form look like the rest of your app whereas the "popups" from the query parameters, are so plainly access...

    BR & GL

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7

    Post

    Quote Originally Posted by frekster22
    Hi,

    I'm having yet another problem

    I'm hoping explorer will behave this time - I had a fatal error last time I tried to post this question. Anyways....

    I'm trying to take a field containing dates (DateCommenced) and add five years to it. I've achieved this with:
    AccredDue: DateAdd("yyyy",+5,[DateCommenced])
    as the field name in my query. This works fine by itself.

    The problem occurs when I try to add a parameta criteria:
    Between [Enter Beginning Date] And [Enter End Date]

    This will sometimes give me the results I want, but not always. The idea is to type in two dates (ie 1/1/04 and 1/12/04) and the query will return any entries from the DateCommenced field between these dates, but five years ago (ie between 1/1/99 and 1/12/99)

    Part of the problem seems to be that:
    a) Access seems to alternate between American and Australian format dates (ie 12/30/04 and 30/12/04) - I'm Australian just for the record
    b) The parameter entered is only recognised as a date when it is entered in full (01/01/2004 as opposed to 1/1/04)

    I could be wrong here, but that is what seems to be happening.

    Also, my teacher can only suggest that I "stick to something simpler" - any ideas on this? (I can't bring myself to argue that this would be simple if it worked )

    Any help or suggestions regarding this would be greatly appreciated

    Cheers,
    frekster
    Yes I All way have a problem With USA DATES and NZ DATE
    MM/DD/YYYY or DD/MM/YYYY

    The problem is when in Query design view you can put the NZ date in and it will work if you then view the SQL it Will in USA date

    SO When Writing Builing SQL and You need the use the USA date

    I use this Function to put the date into a USA format Each time
    Function USADATE(THISDATE as date)
    USADATE = month(THISDATE) & "/" & Day(THISDATE) & "/" & Year(THISDATE)
    End Function

    sql = ""
    sql = sql & "SELECT Employees.*"
    sql = sql & " FROM Employees "
    sql = sql & " WHERE Employees.BirthDate)>#"& USADATE(#01/12/60#) & "#));"

    this hasn't let me down yet
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    frekster

    I am also Australian. The best thing to do is as namliam suggests, that is use a form as the basis for the criteria.

    I use the following to get all the records between two dates

    >=[Forms]![Attempts]![1] And <=[Forms]![Attempts]![2]

    In the above case [1] and [2] are unbound text boxes on the form Attempts.

    I enter the dates as in 25/5/04 and have no problems. I am using Access 95.

    My experience has been only poor with entering parameters in queries along the lines of Between [Enter Beginning Date] And [Enter End Date]

    If a date is entered directly in the criteria row such as <25/4/04 then my Access will automatically format it as <#25/04/2004#

    Mike

  8. #8
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39

    I think it makes sense.....

    Thanks guys for your suggestions.

    Just wondering (I suspect I missed something, but I'll ask anyways ) - if it is possible to force an american format, isn't it possible to simply change that to an Australian format? Or is the issue in the way that the data is processed later?

    namlim: I think I get the idea *crosses fingers* . I'll give it a try - Thanks.

    myle:

    I use this Function to put the date into a USA format Each time
    Function USADATE(THISDATE as date)
    USADATE = month(THISDATE) & "/" & Day(THISDATE) & "/" & Year(THISDATE)
    End Function
    The way I read your code, your function would allow me to then set the format of any date.... Is that right? If so, where can I enter this code?


    Also, while I'm here, can anyone point me towards a resource that list's all the VBCode (functions/operators/etc - I hope I'm using the right words here) used in access? I find the help in Access 2002 very vague, and it doesn't even have a list of commands (that I can find), just a few random examples without any explanation or breakdown of the code.

    Well, thanks for all the help, I might even get the thing finished someday

    frekster

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    List of all functions: Help!


    As for the usadate function dont use that ( i am sorry ) but rather use the default Format function as i described above...

    Regards

  10. #10
    Join Date
    May 2004
    Location
    South Australia
    Posts
    39

    Thumbs up Mission Succesful

    Thanks guys,

    I finally got it working!

    It all worked perfectly when I used a single parameter, but as soon as I used Between it just fell apart. It seems that access doesn't like using a Between criteria on a calculated field, so I ended up using the following:

    Between (DateAdd("yyyy",-5,[Forms]![frmAccredDue-EnterDates]![Text1])) And (DateAdd("yyyy",-5,[Forms]![frmAccredDue-EnterDates]![Text3]))

    I made this the criteria for my uncalculated field, and it now works fine.

    I also used 'd mmm yy' in the format property of the text boxes. I'm unsure of the reason, but the entry of Australian dates, no matter how I enter them, has worked every time I've tried it so far.

    Thanks namliam and Mike for your suggestions with the code - I would never have thought of using a form without your suggestions.

    Cheers,
    frekster

Posting Permissions

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