Results 1 to 14 of 14
  1. #1
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60

    Adding Months

    I cant appear to find the right way to do this...I can add a number, but not from a value in the table.

    I have a query (Calibration), and in it, I have two fields "Calibrated Date", and "Frequency".



    The frequency is the number of months that the calibration is due. The calibrated date is the date is was calibrated. What I would like to do is create a result that adds the number of months (frequency) from the calibrated date. For example, if the calibrated date is June 1 2021, and the frequency is 1, then the "Due Date" would be July 1, 2021.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    add the months using DateAdd:

    form:
    txtDue =dateAdd("m",txtFreq, txtCalibDate)

    or use an update query to do the same for table field.

  3. #3
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    I added this to the query.

    Due Date: DateAdd("m",[Frequency Time],[Calibrated Date])

    This only copies the date, and it is the same date as the calibrated date.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ranman's method is correct:

    ?DateAdd("m",3,date())
    5/10/2021

    You might check what values are in the other fields to see what's going wrong. Or post a sample db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    I want to make sure I understand. Is that a form?

    Let me explain a little more. The table of which the query is based on...is linked to an excel file on our network. I want to create a report/or query result, that shows the calibrated date, and Frequency and the tells you when its due (Calibrated Date+ Frequency Time).

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try this maybe (in your query):

    Due Date: DateAdd("m",CInt(Nz([Frequency Time])),CDate([Calibrated Date]))

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    OK, so now it appears the number in the linked table is text. I have to go around the text another way. Thank you!

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by cbuechner View Post
    OK, so now it appears the number in the linked table is text. I have to go around the text another way. Thank you!
    Use Val() ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    This might work:

    Due Date: DateAdd("m",[Frequency Time],DateValue([Calibrated Date]))

  10. #10
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    Digging around a little more. The linked table it is connected to is formatted as a date, but it only has a number (from a mass upload). It isn't the excel date format. It appears that we uploaded this number into a field that was formatted as a date. So its not changing the number we uploaded, but when we export it, it still comes out formatted as a date. Really complex. Basically, we mass uploaded data into a system, and the section that retains the date is formatted as a date, and rather than doing any converting, it just has the number we uploaded.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried my earlier post?

    Due Date: DateAdd("m",CInt(Nz([Frequency Time])),CDate([Calibrated Date]))

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    I entered this and it comes out as the same date as the calibration date.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please post a screen shot with the linked table showing the two existing fields (Frequncy Time and Calibrated Date)?

  14. #14
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by cbuechner View Post
    I added this to the query.

    Due Date: DateAdd("m",[Frequency Time],[Calibrated Date])

    This only copies the date, and it is the same date as the calibrated date.
    It sure appears that [Frequency Time] is zero if nothing is added to [Calibrated Date]

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

Similar Threads

  1. Age in .... months.
    By aaronjk99 in forum Access
    Replies: 5
    Last Post: 05-29-2017, 05:43 AM
  2. Replies: 13
    Last Post: 06-11-2015, 06:41 PM
  3. Replies: 3
    Last Post: 02-23-2014, 02:06 PM
  4. Criteria for last 12 months
    By libraccess in forum Queries
    Replies: 6
    Last Post: 05-07-2012, 07:33 PM
  5. Adding months to years in queries
    By TonyB in forum Queries
    Replies: 2
    Last Post: 07-29-2011, 09:29 AM

Tags for this Thread

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