what access tool can you use to enter dates in a date/time field

Updated 2/5/2020

Microsoft Access engagement functions are a powerful way to retrieve and analyze data in your Access databases, only very few users know well-nigh these functions or how to use them. In this commodity, I volition depict a few ways you can leverage Microsoft Admission date functions in Admission 2019 and earlier versions.

1. Automatically Insert Today'south Date (Beginner)

If you find yourself constantly inbound whatever today'due south appointment is in your Access forms or tables, y'all are wasting valuable time. Access can do that for you!

The simplest style to insert today's mean solar day is to add a uncomplicated Date() office to the Default Value property of your table. Let's say we take a database that tracks applicants and case workers, and when cases are assigned, signed out, and airtight. Following this case, allow's say we would like to automate the date for when a case is received.

1. Locate the table containing the date field you lot wish to modify and open it to Design View (correct-click the tabular array and choose Design View). In our example, nosotros would need to open up the table that stores our case information.

2. Once in Design View, select the field you wish to modify. In our case, it volition be the DateReceived field.

3. In the Field Properties area, on the General Tab, locate the property for "Default Value" and type Appointment().

4. Switch to your table view and verify that for new records, today'south appointment is automatically populated in the field.

Once this holding is set for your field, users can skip past this field when doing information entry. Or, users can change the date as necessary. If the field is time-sensitive as well, you can also utilize the Now() function in place of the Date() function to store both the date and time information.

2. Preclude Information Entry for Dates After Today'south Date (Intermediate)

Date functions are likewise useful for controlling what dates are allowed to be entered into a given Date/Time field. Let's say we had a sales table where we only want users to enter a transaction occurring on or earlier today'southward date. Nosotros can reach this level of control by writing an expression in the field'due south Validation Rule holding box inside the table.

1. Open the table containing the field you desire to control to its Design View.

2. Select the field you wish to alter. In our example, the field is our "Invoice Date" field.

3. In the bottom portion of the screen, under Tabular array Properties, click inside the Validation Rule property box and enter <=Now(). This will ensure that users enter a date occurring on or before today's date. In other words, the engagement entered must be less than or equal to at present.

4. Type "Transaction must occur on or before today's date" within the Validation Text property box. That style, if a user enters a date occurring after any "at present" is, the user will be alerted that the "Transaction must occur on or before today's date." If the engagement entered is valid, the user will not receive whatever warnings and the record will be saved.

   

Other rules y'all can utilise include:

• >=Appointment (): Disallow engagement values in the past.

• [Terminate Engagement]>=[Showtime Date]: Ensure the end engagement value comes subsequently the starting time date.

• [RequiredDate]<=[OrderDate]+xxx: Requires an entered date to occur no more than than 30 days after the lodge engagement.

• >=#01/01/2019#: Requires a appointment entered on or after January i, 2019.

iii. Filter past Appointment Functions (Intermediate)

In add-on to using engagement functions for controlling and streamlining data entry, Microsoft Access date functions really shine when used equally query criteria to filter or limit query results by date or date range.

In this example, we will build a query to filter results and show merely records that oasis't been updated within the last six months.

1. Create a query using the Admission Query Designer (Create tab > click Query Design). This volition open a Prove Tabular array dialog box.

2. Select the table you wish to query. Click Add together and then shut the dialog box.

3. Add fields to your query past double-clicking or dragging the desired field down to your QBE grid. In our example, we want to brandish the Beginning Name, Last Proper noun, E-postal service Address, and LastUpdated fields.

4. After all the fields have been added, we will desire to add an expression to bear witness records final updated over six months agone (this is done underneath the LastUpdated field in the first criteria box). To accomplish this, we can utilize the DateDiff function to summate the divergence, in months, between the LastUpdated date and whatever at present is. Then it will evidence only those records that are over 6 months old. So, our expression would be DateDiff("m",[LastUpdated],Now())>6.

   

To explain what yous are seeing, the DateDiff part has three required function arguments: interval, date1, and date2. Here, the interval is "chiliad" because nosotros want to calculate in total months elapsed in guild to notice records older than half dozen months. Date1 and Date2 correspond the two dates for which we want to summate the difference.

In our example, Date1 is the information stored in the LastUpdated field. For Date2, since we desire to salve and reuse the query, we desire to reference the At present() role to summate using the organisation appointment and time. So, our DateDiff function is only calculating the departure between when the field was last updated and correct now. To limit our query results downwardly to show only those records older than half-dozen months, nosotros tin so tack on a > symbol and the number 6 after the DateDiff function.

Upon saving and rerunning this query, you lot might detect that Access volition display our written expression a scrap differently, but information technology is just another fashion of expressing the same affair.

4. Locate Today'south Birthdays (Intermediate)

In addition to locating records by a departure betwixt two dates, you can also query your database based on part of a date. If we want to locate contacts whose altogether is today, that is exactly what we will need to do—friction match part of the date (month and twenty-four hours) with today's month and twenty-four hour period.

1. Use the DatePart function DatePart(<<interval>>, <<appointment>>, <<firstdayofweek>>, firstweekofyear>>). Interval is a required string expression representing the interval of time you want returned. Engagement is another required variant representing the value or date you desire to evaluate. The last two arguments are optional, and are not necessary for our example.

2. An expression to extract the month from a engagement field would look similar DatePart("m",[Birthday]). "G" stands for month. [Birthday] is the name of the field that stores your contact'southward birthdays. To extract the 24-hour interval merely, supercede the "m" with "d".

3. Putting everything together, nosotros'd need to write an expression that sets those extracted values equal to today's month and twenty-four hours. For that, nosotros could apply the same DatePart part to excerpt the twenty-four hours and calendar month, and modify the Date argument to the Now() office. So, the complete query criteria would look like this:

In other words, the month of Birthday is equal to the month of right now, and the day of Birthday is equal to the day of now.

5. Store the Date a Tape was Last Modified or Updated Using a Information Macro (Advanced)

A Information Macro will automatically update a LastUpdated field to the current date and fourth dimension whenever a record is modified.

For this example, let's say we want to rails when an employee's contact information was final updated. We will build a query to locate employees whose information has not been updated recently (say, in the last year).

1. Open up the employee'south table (or any table you wish to track when records were terminal modified) to Design View and add together a new Date/Time field named "LastUpdated". This field will be used to store the date/fourth dimension information gathered whenever a record is updated.

2. Save the tabular array.

3. In the Table Tools | Design tab, click the Create Information Macros push and select Earlier Change. This will open up the Macro Designer for the Before Change property, meaning whatsoever actions you plan here will exist evaluated and applied before a record is saved to the database. Since we want to log when a record was last updated, nosotros want to apply our timestamp or date office to the LastUpdated field just before the record is saved.

4. Click the Add New Action drop menu and select the action SetField from the listing. That volition add the action, plus two required arguments for the Action, Proper name, and Value.

   v. In the Proper noun box, write the reference to the field you lot would like updated earlier the change of the record. In our example, nosotros would type "LastUpdated".

      6. In the Value box, write an expression for how you'd similar the field inverse. In our example, we'll need to type Now().

7. Click Save in the Macro Tools | Design tab and then click Close. The Macro should at present exist applied to our table.

eight. Salve the tabular array and test the data macro by locating a tape in the table and editing it. Upon moving off the record, Access should non only salve changes to the record, but also enter the current appointment and time into the LastUpdated field.

Over time, every bit more data is edited and entered, You tin can query this field to check and see which employees' data might be outdated, and request that those employees check and update their information as necessary.

Ready to Examination Your Skills?

If yous have never used Microsoft Access appointment functions, take a wait at this quick Beginner'southward Guide to Microsoft Access Built-in Date Functions that I created and shared on Pluralsight's YouTube channel.

If you lot want to learn more near Access, Pluralsight has the resources and courses you need to gain an edge in your career. Or, if y'all think yous're prepare, examination your skills in Access and come across how they stack upwardly!

shinngodge1980.blogspot.com

Source: https://www.pluralsight.com/blog/it-ops/microsoft-access-date-functions

Share this post