Learning about ms access query is the key to a good database management system as it is the heart of any database application.
There are many ways and questions to ask a database using queries and mastering the special conventions and criteria will pay dividend and avoid silly mistakes, illogical record set results and even errors.
One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and it’s conventions.
Take a look at the ms access query below showing orders before the year 2016 and the design of the
query which suggested a date range from 1st January 2016 to 31st December 2016.
The criteria for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range).
It’s missing the ‘And’ operator and better still adding the ‘Between’ operator will capture the correct range too. The # (hash sign) is the correct convention and good check to ensure it’s the right data type too (date/time in this case).
A revised example is shown below:
If the field is a genuine date/time data type, you do not need to enter the # (hash sign) as it will automatically fill this in for you when moving the cursor elsewhere in the gird (another convention checker for you).
If however you see double-quotes (” “) wrapped around the date expression, this suggests either you chosen the wrong field or the date field is actually a text data type and it will simply not work as expected.
One final point to consider and even check for you ms access query database and those queries is the regional setting of the operating system you are using. UK versus USA setting often throw up confusion and inaccuracies to so ensure you are using the correct date/time formats.
MS Access queries are very powerful indeed and there are many permutations available.
A macro is a tool that lets you automate tasks and integrate functionality into your forms, reports, and controls. For instance, if you incorporate a command button to a form, you link the button’s OnClick event to a macro. The macro will have the commands that you want the button to perform every time it is ticked.
In Access, it is useful to consider macros as a simplified programming language that you compose by building a list of actions to perform. When you create a macro, you choose every action from a drop-down list then fill in the needed information for every action. Macros allow you to add functionality to forms, reports, and controls without writing code in a Visual Basic for Applications (VBA) module. Macros give a subset of the commands that are accessible in VBA, and most people find it simpler to construct a macro than to write VBA code.
For instance, assume that you need to begin a report right from one of your data entry forms. You can add a button to your form then make a macro that starts the report. You can either have a standalone macro, which is then bound to the OnClick event of the button, or embed the macro directly into the OnClick event of the button itself. In any case, when you tap the button, the macro runs and starts the report.
You create a macro by using the Macro Builder as shown in the illustration below.
On the Create tab, in the Macro & Code group, click Macro.
Understand Macros
The term macro is frequently used to denote independent macro objects. A macro object can contain several macros- referred to as a macro group. A macro group is shown in the Navigation Pane as a single macro object though it contains multiple macros. Unquestionably, it is possible to make each macro in a separate macro object, but it bodes well to gather some related macros into a single macro object. The name in the Macro Name column distinguishes every macro.
A macro comprises individual macro actions. Most actions involve one or more arguments. Moreover, you can assign names to every macro in a macro group. You can also add conditions to control how each action is run. The succeeding sections will explain every feature in detail.
Macro names
If your macro object contains just a single macro, macro names are needless. You can simply refer to the macro by the name of the macro object. But in the case of a macro group, you need to give a unique name to every macro.
Arguments
An argument is a value that gives information to the action, for example, what string to show in a message box, which control to operate on, and so on. Some arguments are compulsory while others are discretionary.
When you choose the data macro that you wish to run in the macro designer, Access database identifies if the data requires parameters. If it does, it displays text boxes where you can type in the arguments.
Conditions
A condition determines specific criteria that must be met before an action will be run. 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.
One condition can control two or more actions. You can achieve this by writing an ellipsis in the Condition column for each succeeding 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 run.
In Access database 2010, 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 that is used by programmers.
To make Access database temporarily skip an action, input False as a condition. Temporarily skipping an action can be helpful when you are troubleshooting a macro.
MS access is a great database tool and has great built-in functions to help you create a database, explore more.
About Author: It is the leading website is about learning to build an Access database using the most effective techniques.In this Articles it talks about Microsoft access.To know more visit at