dates (1 Viewer)

benjamin.weizmann · Nov 20, 2017

hi

please, what are the most elegant ways to do those?

*from now to accept the date for start of the day
*from now to accept the date for start of the week
*from now to accept the date for start of the month
*from now to accept the date for start of the year

thanks u
Ben

isladogs · Nov 20, 2017

Depends what you mean by 'from now' in each case.
Do you mean based on the current date OR the next date from now onwards matching that criteria?

For example is

from now to accept the date for start of the year

01/01/2017 or 01/01/2018?

Either way you'll use default Access functions based on the current date i.e. Date()

Other questions you need to answer
1. When is the start of the day? Midnight / 09:00 etc?
2. Which day does your week start on? e.g. Sun / Mon

Once you've decided all of that, you could start by looking up these questions in Access help yourself
I think you'll find all these answers there very easily

benjamin.weizmann · Nov 20, 2017

ridders said:
Depends what you mean by 'from now' in each case.
Do you mean based on the current date OR the next date from now onwards matching that criteria?

For example is 01/01/2017 or 01/01/2018?

Either way you'll use default Access functions based on the current date i.e. Date()

Other questions you need to answer
1. When is the start of the day? Midnight / 09:00 etc?
2. Which day does your week start on? e.g. Sun / Mon

Once you've decided all of that, you could start by looking up these questions in Access help yourself
I think you'll find all these answers there very easily

0. based on the current date
1. Midnight
2. Sun

thanks!

The_Doc_Man · Nov 20, 2017

Hint: Look up the DatePart, DateDiff, and DateAdd functions to see the full syntax, which allows you to specify the day that starts a week.

Using DatePart, you can specify a returned part of the day of the month, day of the week, month of the year, or year number ("dd", "w", "m", or "yyyy").

https://support.office.com/en-us/article/DatePart-Function-26868a79-5505-4e5a-8905-6001372223fa

isladogs · Nov 20, 2017

I hadn't answered because I wanted you to use the Access help to find the answers.
Doc has given you enough info for you to do so.

You'll also find answers on sites like AccessWeb
http://access.mvps.org/access/

benjamin.weizmann · Nov 20, 2017

ridders said:
I hadn't answered because I wanted you to use the Access help to find the answers.
Doc has given you enough info for you to do so.

You'll also find answers on sites like AccessWeb
http://access.mvps.org/access/

I understood how I take apart dates
but how I combine them to new one date?

isladogs · Nov 20, 2017

Ben
As I've said in a previous post, there is nothing in your replies to indicate you've tried to find a solution yourself based on advice given

There are various solutions which are given in MANY places including Access help and Access Web. This is taken direct from Access help

Displaying Specific Dates
The current month:

DateSerial(Year(Date()), Month(Date()), 1)

The next month:

DateSerial(Year(Date()), Month(Date()) + 1, 1)

The last day of the current month:

DateSerial(Year(Date()), Month(Date()) + 1, 0)

The last day of the next month:

DateSerial(Year(Date()), Month(Date()) + 2, 0)

The first day of the previous month:

DateSerial(Year(Date()), Month(Date())-1,1)

The last day of the previous month:

DateSerial(Year(Date()), Month(Date()),0)

The first day of the current quarter:

DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

The last day of the current quarter:

DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

The first day of the current week (assuming Sunday = day 1):

Date() - WeekDay(Date()) + 1

The last day of the current week:

Date() - WeekDay(Date()) + 7

Here are some solutions, there are others...

1. start of the day
=Date & "00:00:00"

2. start of the week
Date() - WeekDay(Date()) + 1 (taken from Access help)

3. start of the month
=DateSerial(Year(Date()), Month(Date()) + 1, 0) (taken from Access help)

OR

=FirstOfMonth(Date)

- based on function from Access Web

Code:

Function FirstOfMonth(InputDate As Date)
'  Return a date that is the first day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer

    If IsNull(InputDate) Then
        FirstOfMonth = Null
    Else
        D = Day(InputDate)
        M = Month(InputDate)
        Y = Year(InputDate)
        FirstOfMonth = DateSerial(Y, M, 1)
    End If
End Function

4. start of the year
="01/01/" & Year(Date)
or "1/1/" & Year(Date)

benjamin.weizmann · Nov 20, 2017

ridders said:
Ben
As I've said in a previous post, there is nothing in your replies to indicate you've tried to find a solution yourself based on advice given

There are various solutions which are given in MANY places including Access help and Access Web. This is taken direct from Access help

Here are some solutions, there are others...

1. start of the day
=Date & "00:00:00"

2. start of the week
Date() - WeekDay(Date()) + 1 (taken from Access help)

3. start of the month
=DateSerial(Year(Date()), Month(Date()) + 1, 0) (taken from Access help)

OR

=FirstOfMonth(Date)

- based on function from Access Web

Code:

Function FirstOfMonth(InputDate As Date) ' Return a date that is the first day of the month of the date passed Dim D As Integer, M As Integer, Y As Integer If IsNull(InputDate) Then FirstOfMonth = Null Else D = Day(InputDate) M = Month(InputDate) Y = Year(InputDate) FirstOfMonth = DateSerial(Y, M, 1) End If End Function

4. start of the year
="01/01/" & Year(Date)
or "1/1/" & Year(Date)

I indeed tried

DateAdd ( "y", -DatePart ( "y", date), date )
start of year

DateAdd ( "w", -DatePart ( "w", date), date )
start of week

isladogs · Nov 20, 2017

Well I've given you the answers now ....

Pat Hartman · Nov 20, 2017

And if you don't yet have enough functions, here's a sample database where they're all built and shown in use on the forms included.

isladogs · Nov 20, 2017

Hi Pat

Thanks for uploading that sample database
It will be a very useful reference & worth adding to sample db or the repository

I see you have an Easter calculator supplied by Mile-O.
Some time ago I posted another function to do this based on code by Chip Pearson originally for Excel. Its somewhat simpler
I just adapted it for Access & added it to the repository

See https://www.access-programmers.co.uk/forums/showthread.php?t=294630

Pat Hartman · Nov 20, 2017

I'm sure at one time an earlier version was in the samples but lots of stuff got deleted in the hack I told you about.

moke123 · Nov 20, 2017

hi pat,
is it just me or is there something funky with the sample you posted?
I can download and unzip it however once its open it wont allow you to click on anything. Using the bypass you can click the warning but then nothing else. Open it again and you can open a form in design view but once you try form view it disappears and you cant click anything.

isladogs · Nov 21, 2017

It worked for me ... and I tried almost all tabs ...
Trusted location?

Pat Hartman · Nov 21, 2017

What version of Access are you using? This sample should work with anything that supports the .accdb format since the app was originally built with A2K.

Are you using a 32-bit version of Office? The app will not work with 64-bit because it uses an API call. I might have an older version that I converted for a client.

Try downloading the file again and make sure you unzip it before you use it. Sometimes working within the zip file causes issues.

If you have two monitors, check your second monitor for a popup form. There is an about form that opens when the app opens and you have to accept the terms.

isladogs · Nov 21, 2017

Actually, Moke is correct.
There is something odd about this sample database after all but its fixable.

When I run it in 32-bit Office 2010 or 2016 on a dual monitor setup, it ALWAYS opens the startup form frmAbout on the secondary monitor even though the Access window is on the primary monitor. I tried dragging it to the primary monitor & reopening but it does the same thing again.
Odd as there's no code in that form to place it in a specific position on the screen

I also had a look at the code.
All of the API calls appeared to have been disabled so I tried running it in 64-bit office 2016 on a laptop (so no dual monitor).
The code compiles fine.
However, the app beeps and the startup form refused to appear so it appears not to work if you have a single screen monitor.

I confirmed this by disabling the secondary monitor on my desktop.

FIX:
Open the db whilst holding down the Shift key.
Open frmAbout in design view & and save it.
Close & reopen normally

It then works fine on both 32-bit & 64 bit Access with a single monitor or dual monitors

Hope that helps

moke123 · Nov 21, 2017

thanks Colin.
Your fix worked.

isladogs · Nov 21, 2017

You're welcome.

Pat Hartman · Nov 21, 2017

Wow. That is bizarre. It must have happened with the last change I made because no one else has complained.

I'll do the same thing to the original and post an update. Thanks for solving the problem ridders.

isladogs · Nov 21, 2017

Hi Pat

No problem.
If you haven't done so already, do have a look at the alternative Easter calculator I posted

dates (1 Viewer)

Registered User.

MVP / VIP

Registered User.

Immoderate Moderator

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Super Moderator

Attachments

MVP / VIP

Super Moderator

AWF VIP

MVP / VIP

Super Moderator

MVP / VIP

AWF VIP

MVP / VIP

Super Moderator

Attachments

MVP / VIP

Similar threads

Users who are viewing this thread