Results 1 to 9 of 9
  1. #1
    WAG1 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    8

    Needing code for calculating Calibration Due date.




    Hello All

    This is my first time here and “surprise, surprise” I have aproblem that I just cannot seem to figure out. I have a calibration database used for tracking the calibration statusof all measuring devices.In the tablethat supports the form there are three fields among many others that are tocontrol the dates showing on the form.The table is tbl-1-calibration1 and the date fields are as follows:

    CalDate is a Date/Time field in the table and a bound fieldin the form

    CalInt1 is a number field and these numbers are in a tablecalled tbl-1-CalInt. In the form theCalInt1 number is looked up using a combo box.

    CalDue is a Date/Timefield(this field needs to be calculatedbased on theCalDate field times the number of months in the CalInt1 field.

    The form that I use for entering data is calledfrm-1-Calibration1.

    I actually had this almost working one time weeks ago and itworked perfectly for all months from 1 thru 12. Anything past 12 months the date would remain fixed at the 12 month date.

    To make a long story short can someone here point me to somecode that will work. I have been allover the internet looking for a solution and have wound up back at square one still needing a solution.


    Any help that you could provide would be greatly appreciated.


    Thanks


    WAG1


  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So, if you have a CalDate for a gas sniffer (detector) on 4/20/2016 (m/d/yyyy), and CalInt1 equals 13 months, then CalDue should be 5/20/2017?

    You could try
    Code:
    Dateadd("m",CalInt1,CalDate)
    How are you initiating the calculation? The after update event of the combo box? A button? In the code for the after update event/button, I would check to ensure that both values (CalDate, CalInt1) are available before doing the calculation.

  3. #3
    WAG1 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    8
    This is the code that I now have in the AfterUpdate event of the combo box

    Private Sub cboCalInt1_AfterUpdate()
    Dateadd("m",CalInt1,CalDate)
    End Sub

    As soon as I enter the number of months from the pull-down I get the following

    Compile error:
    Syntax error

    Not sure why as I did a cut and paste.

    WAG1

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Private Sub cboCalInt1_AfterUpdate()
    Dateadd("m",CalInt1,CalDate)
    End Sub
    You forgot the left part.... the control name!!


    Assuming that you did not change the name Access assigned to the control that is bound to the field "CalDue", try:
    Code:
    Private Sub cboCalInt1_AfterUpdate()
        Me.CalDue = Dateadd("m",CalInt1,CalDate)
    End Sub

  5. #5
    WAG1 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    8
    Steve

    You have my calculated field working almost perfect. One last issue that I really hope you have the answer too. My table, tbl-1-CalInt is a single column called CalInt1 and has the following data

    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 24, 36, 48, 60, 72, 144 Right now with you help the form is working perfect for months 1 thru 12. Past the 12th It still only increases the date 1 month at a time.


    Example: starting with 4/15/2016 twelve months would give me 4/15/2017. the next number is 24 months which should give me 4/15/2018 but instead renders 5/15/2017 then 36 Months should give me 4/15/2019 but instead renders 6/15/2017. From looking at the table and the dates being produced it appears that the calculation is using the ID column of the table instead of the CalInt1 column. When I use the ID column numbers I get dates that match what they should have produced. Is there some way to get it to use the correct set of numbers.

    WAG1

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have my calculated field working
    Actually, it would be a calculated control, not a calculated field - they are two different things. (Forms have controls, tables have fields)


    What is the SQL of the combo box ROW Source?

    If the combo box ROW Source is a value list, only have the values you need:
    (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 24, 36, 48, 60, 72, 144)

    I would not use a table as the combo box ROW Source.

    If the row source is a query,
    1) you could remove the PK field from the query.
    or
    2) You could use the Column property in the DateAdd function:
    Code:
    Private Sub CalInt1_AfterUpdate()
        Me.CalDue = DateAdd("m", Me.CalInt1.Column(1), Me.CalDate)
    End Sub
    The columns in the combo box are zero based.. first column is index 0, second column is index 1, etc

  7. #7
    WAG1 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    8
    Steve

    Good News! I now have the calculated controls working exactly like they are supposed to. I can't thank you enough for you assistance. Have a great holiday weekend!

  8. #8
    WAG1 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2016
    Posts
    8
    Steve

    Good News! I now have the calculated controls working exactly like they are supposed to. I can't thank you enough for you assistance. Have a great holiday weekend!

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help....
    Hope you enjoy the holiday weekend.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-29-2016, 04:03 AM
  2. Replies: 7
    Last Post: 02-11-2015, 07:56 PM
  3. Replies: 3
    Last Post: 11-26-2014, 01:01 PM
  4. Database for annual calibration
    By ScottXe in forum Access
    Replies: 2
    Last Post: 08-25-2014, 08:30 AM
  5. Calculating % in VBA Code For Excel
    By mkc80 in forum Access
    Replies: 1
    Last Post: 09-16-2012, 05:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums