Days left in the year from a date (1 Viewer)

[WineSnob]

I have a control on a form that the user will input a date. I need to know how many days are left in the year to use that number as variable in my code. So far I found things about DateDiff but I only have the start date not the end date.

Which would be StartDate + # of days left in the year = EndDate

I am looking for # of days left in the year
thanks

 

[pbaldy]

You can use the DateSerial() function for the end date, using Year(StartDate) for the Year argument.

 

[Beetle]

You can use DateSerial to determine the last day of the year, then use that in your DateDiff function;

DateDiff("d", [YourDate], DateSerial(Year([YourDate],12,31))

 

[pbaldy]

You can use DateSerial to determine the last day of the year, then use that in your DateDiff function;

DateDiff("d", [YourDate], DateSerial(Year([YourDate],12,31))

Right concept, wrong execution.

 

[WineSnob]

The Year(StartDate) gives me 2013. I am trying to get 266 from the date $/9/2012.
Currently I am using the following but want to change it so the user only has to enter one date value.
For nStartDate = (Forms![Annual Vendor Budget Input]![argdate]) To (Forms![Annual Vendor Budget Input]![argenddate]) + 30
With argdate as the starting date (ex. today 4/9/2012)
I need the number 266 (# of days left in the year) nDaysLeft
So I can change the code to read:
For nStartDate = (Forms![Annual Vendor Budget Input]![argdate]) To (Forms![Annual Vendor Budget Input]![argenddate]) + nDaysLeft

I hope that explanation is clear .

 

[WineSnob]

Clarification:
I want the code to read:
For nStartDate = (Forms![Annual Vendor Budget Input]![argdate]) To (Forms![Annual Vendor Budget Input]![argdate]) + nDaysLeft
With (Forms![Annual Vendor Budget Input]![argdate]) as the only input date.

 

[pbaldy]

What exactly is your code? You'd need to use whatever field or form control that holds the date you want to start from.

 

[raskew]

Hi -

Try playing with this:

x = date()
? x
4/9/2012
y = dateserial(year(x), 13, 0)
? y
12/31/2012
? datediff("d", x, y)
266

Best wishes - Bob

 

[WineSnob]

Here is the entire code. It works the way it is but requires the user to input 12/31 of the year in [argdate]. Note the comment in the code where I want to eliminate the user having to input the last day of the year in the form textbox [argenddate]. I thought is would easy to get the number of days left in the year and use that value to set the last day.

Function fnFirstDayInMonth(Optional dtmDate As Date = 0) As Date
Dim rst As Recordset
Dim qdf As QueryDef
Set db = CurrentDb()
Dim MonthDays As Integer


Dim nStartDate As Date
For nStartDate = (Forms![Annual Vendor Budget Input]![argdate]) To (Forms![Annual Vendor Budget Input]![argenddate]) + MonthDays
' I am trying to eliminate this textbox [argenddate]on the form

dmonth = DatePart("m", nStartDate)
Monthtxt = MonthName(Month(nStartDate))

fnFirstDayInMonth = DateSerial(Year(nStartDate), Month(nStartDate), 1)

MonthDays = DateSerial(Year(nStartDate), Month(nStartDate) + 1, 1) - DateSerial(Year(nStartDate), Month(nStartDate), 1)

Debug.Print "nStartDate " & nStartDate & " # of Days in StartDate " & MonthDays & " Value of fnFirstDayinMonth " & fnFirstDayInMonth & " Month#:" & dmonth & "Monthtxt: " & Monthtxt


nStartDate = nStartDate + MonthDays

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Vendor Budget]")
With rst
.AddNew
![IncomeDate] = fnFirstDayInMonth
![VENDOR] = Forms![Annual Vendor Budget Input]!CmbVendor
![Budget Income] = Forms![Annual Vendor Budget Input]!BudgetIncome
![Actual Income] = Forms![Annual Vendor Budget Input]!BudgetIncome
![Year] = Forms![Annual Vendor Budget Input]!xyear
![Month] = dmonth
![Month Text] = Monthtxt
.Update
End With
rst.Close



Next nStartDate
MsgBox " Records Added for " & Forms![Annual Vendor Budget Input]!CmbVendor & " for " & Forms![Annual Vendor Budget Input]!xyear

End Function

 

[pbaldy]

Why does the user have to key it in when you can hard-code it into the DateSerial function?

 

[WineSnob]

I guess that is what I am struggling on how to do that. Can you guide me as to how to do it? Thanks Paul !

 

[pbaldy]

DateSerial(Year(Forms![Annual Vendor Budget Input]![argdate]),12,31)

 

[Beetle]

@pbaldy

Right concept, wrong execution.

How so. OP said he wants the number of days left in the year based on a date input by the user -

I am looking for # of days left in the year
thanks

which is exactly what my suggestion returns.

?DateDiff("d",Date(),DateSerial(Year(Date()),12,31))
265

 

[pbaldy]

@pbaldy

How so.

Check your parentheses in the post I quoted.

 

[WineSnob]

Thanks to Paul and Sean. I used Pauls solution with date serial because I have never seen code with the ? mark in front and dont know how to use it. Thanks to both.

 

[pbaldy]

Happy to help. The ? is how you test code in the VBA Immediate window. You wouldn't include it in actual use.

 

[Beetle]

@pbaldy

I stand corrected. There is indeed a typo (missing paren) in my previous post. Thanks for catching that.

@Winesnob

I have never seen code with the ? mark in front and dont know how to use it.

That was an example from the immediate window (Ctrl + G) in the VBA editor. It's a convenient way to test things and see what result is returned. When doing so, you precede it with a question mark.

 

[WineSnob]

So I can LEARN something new what is the correct syntax for
?DateDiff("d",Date(),DateSerial(Year(Date()),12,31 ))
Do i put it in a module as a sub or directly in the immediate window?

 

[pbaldy]

Directly in the immediate window:

 

[WineSnob]

Got it. I love to learn something new. Makes me feel like a new baby. Very much Appreciated. Thanks again Paul MVP Lifetime!!!