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
Turning Off Access Warning on Action Queries
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:
Docmd.SetWarnings False
before your SQL code, but don't forget to turn the warnings back on again afterwards, adding the following code after your SQL code.
DoCmd.SetWarnings True
Using an Update Query to format a field Using the Mid, Left and Right Functions
Question
I have a table with a field where data is shown as a text with the following format:
123456789. My table as over 50,000 records.
I would like to run an update query to change the format to: 123-45-6789.
Answer
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.
