Learning about ms access query is the key to a good database management system as it is at the heart of any database application.
There are many ways and questions to make a database using queries and mastering the conventions and special criteria will pay dividends and prevent silly mistakes, illogical recordset results and even bugs.
One of the most commonly used and error-prone (if misunderstood) criteria is the date/time data type and its conventions.
Take a look at the MS access query below which shows the orders before the year 2016 and the layout of the
query that suggested a date range of January 1, 2016 to December 31, 2016.
The criteria for the above shows >=#01/01/2016 < #12/31/2016# which will not actually return the correct range and will instead show what are known as logical errors (dates out of range).
The ‘AND’ operator is missing, and better yet, adding the ‘Between’ operator will also capture the correct range. The # (hash sign) is the correct convention and a good check to ensure it’s also the correct data type (date/time in this case).
Here is a revised example:
If the field is a genuine date/time data type, you don’t need to enter the # (pound sign), as it will auto-complete when you move the cursor to another part of the grid (another convention checker for you).
However, if you see double quotes (” “) around the date expression, this suggests that you chose the wrong field, or that the date field is actually a text data type and simply won’t work as expected.
One last point to consider and even check for your access query database and those queries is the locale of the operating system you are using. The UK vs. US setting often leads to confusion and inaccuracies so make sure you are using the correct date/time formats.
MS Access queries are very powerful and there are many permutations available.
A macro is a tool that allows you to automate tasks and integrate features into your forms, reports, and controls. For example, if you add a command button to a form, bind the button’s OnClick event to a macro. The macro will have the commands you want the button to execute each time it is checked.
In Access, it’s helpful to think of macros as a simplified programming language that is composed by creating a list of actions to perform. When you create a macro, you choose each action from a dropdown list and then fill in the information required for each action. Macros allow you to add functionality to forms, reports, and controls without writing code in a Visual Basic for Applications (VBA) module. Macros provide a subset of the commands that can be accessed in VBA, and most people find it easier to build a macro than to write VBA code.
For example, suppose you need to start a report directly from one of your data entry forms. You can add a button to your form and then create a macro that launches the report. You can have a separate macro, which is then bound to the button’s OnClick event, or embed the macro directly in the button’s OnClick event itself. In either case, when you tap the button, the macro runs and the report begins.
A macro is created using the Macro Builder, as shown in the following illustration.
On the Create tab, in the Macro and Code group, click Macro.
understand macros
The term macro is often used to denote stand-alone macro objects. A macro object can contain multiple macros, which is known as a macro group. A macro group is displayed in the navigation pane as a single macro object, even though it contains multiple macros. It is certainly possible to make each macro into a separate macro object, but it bodes well to bring some related macros together into a single macro object. The name in the Macro Name column distinguishes each macro.
A macro understood individual macro actions. Most actions involve one or more arguments. Also, you can assign names to each macro in a macro group. You can also add conditions to control how each action is executed. The following sections will explain each feature in detail.
macro names
If your macro object contains only one macro, the macro names are unnecessary. You can simply refer to the macro by the name of the macro object. But in the case of a group of macros, you must give each macro a unique name.
arguments
An argument is a value that provides information to the action, for example, which string to display in a message box, which control to operate on, and so on. Some arguments are mandatory while others are discretionary.
When you choose the data macro to run in the macro designer, the Access database identifies whether the data requires parameters. Doing so displays text boxes where you can type the arguments.
Terms
A condition determines specific criteria that must be met before an action is executed. You can use any expression that evaluates to True/False or Yes/No. The action will not be performed if the expression evaluates to False, No, or 0 (zero). If the expression evaluates to some other value, the action will be executed.
A condition can control two or more actions. You can accomplish this by typing an ellipsis in the Condition column for each subsequent action that you need the condition to apply to. If the expression evaluates to False, No, or 0 (zero), none of the actions are executed. If the condition evaluates to some other value, all actions are executed.
In the Access 2010 database, the Macro Designer layout looks a lot like a text editor. The three columns are replaced with actions and conditional statements presented in a familiar top-down format used by programmers.
To make the Access database temporarily skip an action, enter False as the condition. Temporarily skipping an action can be useful when you are troubleshooting a macro.
MS access is a great database tool and has great built-in features to help you create a database and explore more.
About the author: It is the leading website to learn how to build an Access database using the most effective techniques. In these articles he talks about Microsoft Access. To learn more, visit https://www.accessdatabasetutorial.com/
