Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Location
    MA
    Posts
    24

    Unanswered: parameter query with date add function

    g morning all,

    Here's the problem. I am currently using the dateadd function to calculate future maintenance and service dates for various pieces of equipment owned by the company. I would like maintenance employees to enter a range of dates and generate a query/report of equipment needing maintenance/service between these two dates.

    Ive created a parameter query using the dateadd function. The following is the SQL behind it:
    ---------

    SELECT tblEquipment.idsAssetTag, tblEquipment.chrEquipmentName, tblSvcHistory.lngSvcIncrement, tblSvcHistory.dtmDateCompleted, Format(DateAdd('m',[tblSvcHistory]![lngSvcIncrement],[tblSvcHistory]![dtmDateCompleted]),"mm/dd/yy") AS Expr1
    FROM tblEquipment INNER JOIN tblSvcHistory ON tblEquipment.idsAssetTag = tblSvcHistory.lngAssetTag
    WHERE (((Format(DateAdd('m',[tblSvcHistory]![lngSvcIncrement],[tblSvcHistory]![dtmDateCompleted]),"mm/dd/yy")) Between [Beginning Date?] And [End Date?]));

    ---------

    Ive set the format of the dtmDateCompleted field to match the mm/dd/yy specified in the above statement.

    The problem is it is displaying dates before and after the year specified in the parameter prompts.

    ie: First date entered: 01/01/03, Second Date entered: 03/01/03, Returns the following datedue: 01/03/03, 02/04/03, 01/29/04, 01/10/02

    At first I thought it might be because the date format used in dtmDateCompleted was using the dafalut "yyyy", but Ive tried both in the query and it doesnt make a difference.

    Any thoughts? Ive attached the test dbase Ive been playing with. Any help would greatly be appreciated. Im really stumped here.

    Also, is there a way to specify an input mask for parameter prompts (ie: __/__/__)

    thanks in advance,

    Tim
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't know about an input mask for a query parameter, but you could easily use a text box somewhere and set an input mask for it.

    not that keen on parameter queries anyway... i prefer to run my queries from a form. i don't trust users to get things right every time: a form gives me the chance to check the input prior to running the query.


    don't really understand the rest of your question, but if it comes down to manipulating dates before they are used as criteria in your query, having the "parameter" in a text box will allow you to manipulate at your leasure.


    izy

  3. #3
    Join Date
    Sep 2002
    Location
    MA
    Posts
    24
    Izy,

    Thanks for the reply. I didnt think to use a txt box as far as the input mask. Good idea

    The problem Im facing is the dates entered as aparameter (ie:x/x/03 and x/x/03 are returning values other than the year 2003 (ie: 2002 and 2004). I though I read something about access 2000 still having some residual Y2K issues but using full year format doesnt seem to make a difference either.

    Thanks again. Anyone else have some thoughts?

    Tim

  4. #4
    Join Date
    Nov 2002
    Location
    Hillsboro, OR, USA
    Posts
    59
    What if you use the Format function on each parameter individually like so?
    Code:
    DateAdd('m',
      Format([tblSvcHistory]![lngSvcIncrement],"mm/dd/yy"),
      Format([tblSvcHistory]![dtmDateCompleted],"mm/dd/yy")
     )
    Any better?

  5. #5
    Join Date
    Sep 2002
    Location
    MA
    Posts
    24
    Bri,

    Doesnt seem to be working (get edxpression is types incorrectly). Although you may be on to something. Let me play with it some more.

    Thanks for the idea

    Tim

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your dateAdd works fine - this is clearly not the problem.

    can you try the following for me (...split the query into two: first do the dateAdd then select the subset with hard-coded dates)

    qry_myCalc:
    SELECT tblEquipment.idsAssetTag, tblEquipment.chrEquipmentName, tblSvcHistory.lngSvcIncrement, tblSvcHistory.dtmDateCompleted, Format(DateAdd('m',[tblSvcHistory]![lngSvcIncrement],[tblSvcHistory]![dtmDateCompleted]),"mm/dd/yy") AS endDate
    FROM tblEquipment INNER JOIN tblSvcHistory ON tblEquipment.idsAssetTag = tblSvcHistory.lngAssetTag;

    qry_myCalcSelect:
    SELECT myCalc.dtmDateCompleted, myCalc.chrEquipmentName, myCalc.lngSvcIncrement, myCalc.idsAssetTag
    FROM myCalc
    WHERE (((myCalc.dtmDateCompleted) Between #01/01/03# And #03/01/03#));



    let me know what you get returned from qry_myCalcSelect?

    i get nothing returned - but this could be 'cos i live in the "rest of the world" where we use the more logical dd/mm/yy date format ;-)

    i have had frightening moments with windows date format (as in regional settings) messing with access date settings EVEN WHEN USING EXPLICIT FORMATS IN ACCESS.

    in an accounting programme i wanted to add 30days to invoice date and had errors until i modified my regional settings to suit access' internal world-view. the fact that there is no default date format setting in tools¦options makes me worry!

    maybe it's worth trying some dateAdd experiments on a test form (add a couple of mm/dd/yy dates from text boxes and display the result in long format in a third text box) ...and repeat after a visit to regional settings.


    izy
    Last edited by izyrider; 12-19-02 at 05:49.

  7. #7
    Join Date
    Sep 2002
    Location
    MA
    Posts
    24
    Thanks again Izy,

    But I actually found something that works lte last night. Here's what I did based on a recommendation of someone else:

    begin code-------
    SELECT tblEquipment.idsAssetTag, tblEquipment.chrEquipmentName, tblSvcHistory.lngSvcIncrement, tblSvcHistory.dtmDateCompleted, DateAdd('m',[tblSvcHistory]![lngSvcIncrement],[tblSvcHistory]![dtmDateCompleted]) AS DateDue
    FROM tblEquipment INNER JOIN tblSvcHistory ON tblEquipment.idsAssetTag = tblSvcHistory.lngAssetTag
    WHERE (((DateAdd('m',[tblSvcHistory]![lngSvcIncrement],[tblSvcHistory]![dtmDateCompleted])) Between CDate([Beginning Date?]) And CDate([End Date?])));
    end cde-------------

    The Cdate function recognizes date literals according to the system settings. I think , from what i understand, the dateadd function is calulating the duedate in a format that my original string parameter did not understand.

    Does this make sense?

    Thanks again for the help

    Tim

Posting Permissions

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