EXPRESSIONS THAT CALCULATE DATE AND ALSO USE THE RESULT AS CRITERIA
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.
EXPRESSION: <Date()-20
DESCRIPTION: This expression displays the records of students whose registration dates are less than 20 days old.
EXPRESSION: Year([REG DATE])=2016
DESCRIPTION: This expression displays the records of students that registered in the year 2016.
EXPRESSION: DatePart("q",[REG DATE])=3
DESCRIPTION: This expression displays the records of students that registered in the third quarter of the calendar.
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.
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
EXPRESSION: "India"
DESCRIPTION: This expression displays the records of students that came from India.
EXPRESSION: "India" Or "USA"
DESCRIPTION: This expression displays the records of students that came from India or USA.
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.
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.
EXPRESSION: In ("USA","India")
DESCRIPTION: This expression displays the records of students that came from USA or India.
EXPRESSION: Not "USA"
DESCRIPTION: This expression displays the records of students that came from other countries apart from USA.
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.
EXPRESSION: >"J"
DESCRIPTION: This expression displays the records of students whose first name starts with letter “K” to “Z”.
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.
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:
Recommended MS Access Textbook
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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!