small list of query showing expressions in Access database Ms Access Gurus

If you are helped, please return the favor and help support this site, thank you.

Home Articles Code Tools

SQL for Query to list Query Expressions in Access database

SQL for a query to show expressions and fields with aliases from the MSysObjects and MSysQueries tables

Quick Jump

Download SQL Results Steps Reference Backstory Share Donate

Goto Top  

Download

Download zipped TXT file that you can copy from to get SQL to make queries showing expressions and fields with aliases: SQL_QueryExpressions_s4p__TXT.zip

If you have trouble with the downloads, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

SQL

q_QueryExpressions_s4p

Here is SQL for a query to show expressions and fields with aliases from the MSysObjects and MSysQueries tables.

SELECT MSysQueries.Name1 AS FldAlias
, MSysObjects.Name AS ObjName
, MSysQueries.Expression
FROM MSysObjects
INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
WHERE ( ( (MSysQueries.Name1) Is Not Null)
AND ( (MSysObjects.Name) Not Like "[~,{]*")
AND ( (MSysQueries.Expression) Is Not Null)
AND ( (MSysQueries.Attribute)=6) )
ORDER BY MSysQueries.Name1
, MSysObjects.Name;

Goto Top  

Results

q_QueryExpressions_s4p

This is run on MyContacts, which is a free download from MsAccessGurus for keeping track of contact information

image to show query expressions from MyContacts

Goto Top  

Steps to create a new query from SQL

  1. Create ribbon
  2. from Queries group, choose Query Design
  3. right-click in top pane for sources and, from shortcut menu choose SQL View, or switch to SQL View using the Home or Query Design ribbon
  4. copy the SQL and paste
    be sure not to keep any characters before the word SELECT, or after the terminating semi-colon ;
  5. switch to Datasheet View to look at results
  6. save the query if you want

Goto Top  

Reference

MyContacts

Download MyContacts

Goto Top  

Backstory

Getting a list of the expressions that your database uses can be very helpful.

If you like this page, please let me know, thank you. Donations are always appreciated

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/SQL_QueryExpressions.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/SQL_QueryExpressions.htm

Get Help with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Data structure is the most important thing to get right! With good structure and relationships, the rest is downhill. I'm happy to help. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top