Quick Tips for Queries
in Microsoft Access
Maximum number of tables in a query = 32
Maximum number of fields you can sort by in a query = 10
Level of nested queries = 50
Queries sort from the left to the right, so arrange your columns accordingly.
To find Null values in a query enter Is Null in the criteria section.
To find values that are not null enter Not Is Null in the criteria section.
Remember zero length strings "" are not the same as Null.
Cannot see all of the information in a section of a query? Then right click and select ‘Zoom’
If you have written an action query in SQL as per below
DoCmd.RunSQL ("Delete * From ClientCost Where (ID = 'PriceList' ); ")
Microsoft Access will prompt you as to whether you want to delete rows of records. There is a way to bypass (turn off) this default message.
This is achieved by adding the following code:
before your SQL code, but don'’t forget to turn the warnings back on again afterwards, adding the following code after your SQL code.
Using an Update Query to
format a field Using the Mid, Left and Right Functions
I have a table with a field where data is shown as a text with the following
123456789. My table as over 50,000 records.
I would like to run an update query to change the format to: 123-45-6789.
You will need to use the left, right and mid functions to achieve this.
In the Update to row, enter the following code:
Left([SSN],3) & "-" & Mid([SSN],4,2) & "-" & Right([SSN],4)
NOTE: Make a copy of the table before you try this, just in case it is not
what you want. Once this query has been run it cannot be undone.
Run the query once you have made a copy of the table.
Only run it once as it will give a completely different result the second
time around as the data will have changed.