Results 1 to 7 of 7

Thread: Calculate weeks and days between two dates and automatically update stored cell value

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    8

    Calculate weeks and days between two dates and automatically update stored cell value

    Hi Everyone
    I am using an app to calculate weeks and days of pregnancy based on a given last menstrual date and it is working well.
    Here is an attachment and the used code:

    Dim DateFrom As Date
    Dim DateTo As Date
    Dim RawDays As Long
    Dim Weeks As Long
    Dim Days As Long

    DateFrom = CDate("01/01/2015")
    DateTo = CDate("11/11/2017")

    RawDays = DateDiff("d", DateFrom, DateTo)
    Weeks = DateDiff("w", DateFrom, DateTo)
    Days = RawDays - Weeks * 7
    Me.Gestational_Age.Text = Weeks & " week" & IIf(Weeks > 1, "s", "") & " " & Days & " day" & IIf(Days > 1, "s", "")

    The Gestational Age stored in MS Access database as for example "34weeks 2days".
    My question is how to update this cell value automatically to be "34weeks 5days" after 3 days and so on.
    Many thanks
    Attached Images Attached Images  

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Calculate weeks and days between two dates and automatically update stored cell v

    Is this really a VB.NET question? That form looks for like VB6 or VBA.

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    8

    Re: Calculate weeks and days between two dates and automatically update stored cell v

    The attachment is just to clarify the idea no more.
    My program is running on visual studio 2022.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Calculate weeks and days between two dates and automatically update stored cell v

    It's a very bad idea to store that string in your database. You should simply store the number of days. You can then display that any way you want to, when you want to, e.g.
    vb.net Code:
    1. Dim dayCount As Integer
    2.  
    3. '...
    4.  
    5. Dim weekCount = dayCount \ 7
    6.  
    7. dayCount = dayCount Mod 7
    8.  
    9. Me.Gestational_Age.Text = $"{weekCount} {If(weekCount = 1, "week", "weeks"} {dayCount} {If(dayCount = 1, "day", "days"}"
    Note the use of string interpolation rather than concatenation, improving readability. Note also the use of the If operator rather than the IIf function. That operator has been around for a decade and a half. Never use IIf.

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    8

    Re: Calculate weeks and days between two dates and automatically update stored cell v

    Quote Originally Posted by jmcilhinney View Post
    It's a very bad idea to store that string in your database. You should simply store the number of days. You can then display that any way you want to, when you want to, e.g.
    vb.net Code:
    1. Dim dayCount As Integer
    2.  
    3. '...
    4.  
    5. Dim weekCount = dayCount \ 7
    6.  
    7. dayCount = dayCount Mod 7
    8.  
    9. Me.Gestational_Age.Text = $"{weekCount} {If(weekCount = 1, "week", "weeks"} {dayCount} {If(dayCount = 1, "day", "days"}"
    Note the use of string interpolation rather than concatenation, improving readability. Note also the use of the If operator rather than the IIf function. That operator has been around for a decade and a half. Never use IIf.
    Thanks alot
    I agree and appreciate.
    Still how to update stored cell value daily?
    If Gestational age right now is stored as 25weeks 2days, so how to update it daily in datagridview and database.
    For example it must be 26weeks 0day after 5 days from now.
    Many thanks

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Calculate weeks and days between two dates and automatically update stored cell v

    if there is a Table in Access why not use SQL-Query ?

    here a query to work the weeks and days from a Date
    Code:
    SELECT Employees2.FirstName, Employees2.SomeDate, DateDiff("d",[Somedate],Now()) AS CountDays, ([CountDays] Mod 365)\7 AS nWeeks, ([CountDays] Mod 365) Mod 7 AS nDays
    FROM Employees2
    ORDER BY Employees2.FirstName;
    here a Image of the qeury results
    Name:  WeeksDays.jpg
Views: 183
Size:  37.8 KB

    the numbers will change as days go on, like JMC said in Post#4
    ... You should simply store the number of days.....
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    8

    Re: Calculate weeks and days between two dates and automatically update stored cell v

    Quote Originally Posted by ChrisE View Post
    if there is a Table in Access why not use SQL-Query ?

    here a query to work the weeks and days from a Date
    Code:
    SELECT Employees2.FirstName, Employees2.SomeDate, DateDiff("d",[Somedate],Now()) AS CountDays, ([CountDays] Mod 365)\7 AS nWeeks, ([CountDays] Mod 365) Mod 7 AS nDays
    FROM Employees2
    ORDER BY Employees2.FirstName;
    here a Image of the qeury results
    Name:  WeeksDays.jpg
Views: 183
Size:  37.8 KB

    the numbers will change as days go on, like JMC said in Post#4
    ... You should simply store the number of days.....
    I agree and appreciate
    Many thanks.

Posting Permissions

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



Click Here to Expand Forum to Full Width