Some Special Query Functions & Expressions In Ms Access & Their Applications

There are some built in MS Access query functions to help you set more criteria. These functions apply to different field types. There are some for Date field, Text field, Number fields, etc. Some of these functions include the Between, Year, DatePart, DateSerial, DateAdd, etc. Most times they are combined with AND, OR, etc.
special ms access query built in functions and their practical application

There are many of them but in this part 3 of this MS Access tutorial chapter (chapter 4), I will now list about 16 of these expressions below with the fields they can be applied to, their expression and then their descriptions.


This is the part 3 of chapter 4 of the MS Access tutorials. You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016 or study part 2 hereAlso see the full tutorial course content of this Microsoft Access Tutorial Series.


The table or database from which we will draw our reference is shown below.
Query expression reference table

EXPRESSIONS THAT CALCULATE DATE AND ALSO USE THE RESULT AS CRITERIA

1. FIELD: PREDICTED REG DATE
EXPRESSION: Between Date() And DateAdd("m",2,Date())
DESCRIPTION: This expression displays records of Student’s whose Registration date fell between today’s date and two months from today’s date.

2. FIELD: REG DATE
EXPRESSION: <Date()-20
DESCRIPTION:
This expression displays the records of students whose registration dates are less than 20 days old.

3. FIELD: REG DATE
EXPRESSION: Year([REG DATE])=2016
DESCRIPTION:
This expression displays the records of students that registered in the year 2016.

4. FIELD: REG DATE
EXPRESSION: DatePart("q",[REG DATE])=3
DESCRIPTION:
This expression displays the records of students that registered in the third quarter of the calendar.

5. FIELD: REG DATE
EXPRESSION: DateSerial(Year([REG DATE]),Month([REG DATE])+1,1)-1
DESCRIPTION:
This expression displays the records of students that registered on the last day of the month.


6. FIELD: REG DATE
EXPRESSION: Year([REG DATE])=Year(Now()) And Month([REG DATE])=Month(Now())
DESCRIPTION: This expression displays the records of students that registered in the current year and month.

EXPRESSIONS THAT USE TEXT AS CRITERIA

1. FIELD: COUNTRY
EXPRESSION: "India"
DESCRIPTION: This expression displays the records of students that came from India.

2. FIELD: COUNTRY
EXPRESSION: "India" Or "USA"
DESCRIPTION: This expression displays the records of students that came from India or USA.

3. FIELD: REG DATE
EXPRESSION: Between #1/1/2016#
DESCRIPTION: This expression displays the records of students that registered on 1-Jan-2016. Note how the dates were enclosed in a pair of hash (#) sign.

4. FIELD: REG DATE
EXPRESSION: Between #1/1/2016# And #12/1/2016#
DESCRIPTION: This expression displays the records of students that registered not earlier than 1-Jan-2016 and not later than 1-Dec-2016.

5. FIELD: COUNTRY
EXPRESSION: In ("USA","India")
DESCRIPTION: This expression displays the records of students that came from USA or India.

6. FIELD: COUNTRY
EXPRESSION: Not "USA"
DESCRIPTION: This expression displays the records of students that came from other countries apart from USA.

7. FIELD: FIRST NAME
EXPRESSION: Like "N*"
DESCRIPTION: This expression displays the records of students whose first name begins with letter “N”, regardless of other letters or characters after it.

8. FIELD: FIRST NAME
EXPRESSION: >"J"
DESCRIPTION: This expression displays the records of students whose first name starts with letter “K” to “Z”.

9. FIELD: COUNTRY
EXPRESSION: Right([COUNTRY],5)
DESCRIPTION: This expression displays the records of students that the number of letters of their country is less than or equal to 5.

10. FIELD: FIRST NAME
EXPRESSION: Len([FIRST NAME])>=Val(6)
DESCRIPTION: This expression displays the records of students that the number of letters of their first name is greater than or equal to 6.

NOTE:

There other built in functions and expressions for querying a database, but the ones shown above are some of the common functions and expressions.
Also note how the field names are enclosed in a pair of square brackets.

This is the end of this part 3 of chapter 4. 

Recommended MS Access Textbook


ms access textbook kindle format


Click Here to know more about the book.

In the next two parts (parts 4 and 5), I will illustrate now show the practical or business applications of these query functions to students’ result system and workers’ payroll system respectively. 

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!