SeQueL 4 - Queries - and their Hidden Functions!
by Clark Anderson
A friend recently exclaimed "Can you really use this function in SQL!  In 
this article of my series I will explore and demonstrate many of the standard 
VBA built-in functions that can be used in SQL.  I did use the Len()  
function in the previous article, "SeQueL 3 - Queries - JOIN from Afar!"  

For starters, you can discover a lot of possibilities by creating a new form 
in Access in the design view adding a data entry control, like a TextBox.  
Open its properties. Under the Data tab, locate Control Source.  Click in its 
entry field, then click on the "More" button with the ellipses (...), to open 
the Expression Builder.  In the lower left window, double click on the 
Functions Folder plus, then click on the Build-In-Functions.  The lower 
middle window will list Function Categories and the lower right window lists 
the Function names in the highlighted category.  Double clicking on the 
function name or selecting and clicking the Paste button will paste the 
selected Function with arguments in the top Expression Builder window.  If 
you select a function, then click the Help button, an explanation with some 
examples will be available.  For some reason, my system cannot find the help 
file, VEENLR3.HLP.  It asks me if I want to find it.  Mine is located in the 
\Program Files\Common Files\Microsoft Shared\VBA directory.  To save you some 
of this effort, I have listed almost all of these functions below.

TestTable2 contains the field DateOfYear with a series of dates.  The first 
query uses some of the date functions.
(DateDiff(), Date(), DatePart(), DateValue() & Month()) as well as some 
ProgramFlow functions (Choose() & IIF()). 

DateDiff() determines the difference between two dates.  Its first argument 
is the interval, "d" for days.  The second is Date() to provide the current 
date.  The third argument is the field, DateOfYear.  The DaysFromNow values 
in the table sampling indicates the January & February dates were almost 300 
days before the query was run.  

The DatePart() function with its first argument, "w" for weekday, and second 
argument, DateOfYear, provides the DayOfWeek.  It starts with 1 for Sunday 
through 7 for Saturday.  

The DatePart() function is used again within the Immediate IF, IIF(), 
function to indicate which dates are on a Weekend and which are on a Weekday.  
The IIF() first argument is a Boolean expression:  (Is the day of week a 
Sunday, 1, or a Saturday ,7.)  If the Boolean expression is true, the second 
argument, "Weekend", becomes the result of the IIF() function, otherwise the 
third argument, "Weekday", is the result.  

For the final column of this query, the DateValue() function converts the 
DateOfYear to a numeric representation that is used by the Month() function 
to extract the numeric month value, 1 to 12.  This numeric month is used as 
the first argument, Index, for the CHOOSE() function to "choose" which of its 
list of arguments to provide to the data record.

SELECT 
DateOfYear, 
DateDiff ("d", Date(),DateOfYear ) AS DaysFromNow, 
DatePart("w",DateOfYear) AS DayOfWeek, 
IIF(DatePart("w",DateOfYear)=1 OR 
DatePart("w",DateOfYear)=7,"Weekend","Weekday") AS DayType, 
CHOOSE(Month 
(DateValue(DateOfYear)),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep"
,"Oct","Nov","Dec") AS Month
FROM TestTable2;

|DateOfYear|DaysFromNow|DayOfWeek|DayType|Month|
|01/28/2000|   -292    |    6    |Weekday| Jan |
|01/29/2000|   -291    |    7    |Weekend| Jan |
|01/30/2000|   -290    |    1    |Weekend| Jan |
|01/31/2000|   -289    |    2    |Weekday| Jan |
|02/01/2000|   -288    |    3    |Weekday| Feb |
|02/02/2000|   -287    |    4    |Weekday| Feb |
|02/03/2000|   -286    |    5    |Weekday| Feb |
|02/04/2000|   -285    |    6    |Weekday| Feb |

The forth column could also have been achieved with the SQL phrase:

SWITCH(DatePart("w",DateOfYear) IN(1,7),"Weekend",DatePart("w",DateOfYear) 
IN(2,3,4,5,6),"Weekday") AS DayType, 

This uses the SWITCH() function that is made up of a series of argument 
pairs.  Each pair has a Boolean expression followed by a resultant 
expression. The Boolean expressions use DatePart() with the familiar IN() 
operator. 
This next query uses the very powerful FORMAT$() function and the DateAdd() 
function.  FORMAT$() creates a string based on the DateOfYear.  The second 
argument is the format.  The "dddd" represents the full name of the day.  The 
"mmmm" is the full name of the month.  The "dd" is the 2 digit day of the 
month.  The "yyyy" is the 4 digit year and the spaces and comma are literal 
characters that will appear in the resultant string.

The next query data field uses the same format specification, but, its input 
argument depends on the function DateAdd().  The first argument of DateAdd() 
is an interval specification, "yyyy", year.  The second argument is -300 
years, and the third argument is the DateOfYear.  The resulting third column 
is the shorter formatted date 3 centuries prior to the DateOfYear.  The 3 
character "ddd" and "mmm" format specifications provide the abbreviated day 
and month names.

SELECT DateOfYear, 
FORMAT$( DateOfYear ,"dddd mmmm dd, yyyy") AS FullDate, 
FORMAT$(DateAdd ("yyyy",-300 , DateOfYear) ,"ddd mmm dd, yyyy") AS OldDate
FROM TestTable2;

|DateOfYear|         FullDate          |    OldDate     |
|01/26/2000|Wednesday January 26, 2000 |Tue Jan 26, 1700|
|01/27/2000|Thursday January 27, 2000  |Wed Jan 27, 1700|
|01/28/2000|Friday January 28, 2000    |Thu Jan 28, 1700|
|01/29/2000|Saturday January 29, 2000  |Fri Jan 29, 1700|
|01/30/2000|Sunday January 30, 2000    |Sat Jan 30, 1700|
|01/31/2000|Monday January 31, 2000    |Sun Jan 31, 1700|
|02/01/2000|Tuesday February 01, 2000  |Mon Feb 01, 1700|
|02/02/2000|Wednesday February 02, 2000|Tue Feb 02, 1700|

This next query combines the Switch() function with the IsNumeric() data 
inspection function and several of the Text manipulation functions (Len(), 
Left$(), Right$() and Ucase$()) to format some postal codes the way the USA 
and Canada want them.  The first Switch() Boolean expression checks to see if 
the postal code is an expected length.  USA expects 5 digits, a hyphen and 4 
digits for a total of 10 characters.  Canada expects 3 alphanumeric 
characters a space and 3 more alphanumeric characters, for a total of 7.  The 
first result expression takes the input string as is but applies Ucase$() to 
assure that any letters in a Canadian postal code are uppercase.  The second 
Boolean expression verifies that all characters are numeric and that the 
length is 9.  Its resultant expression inserts a hyphen between the left and 
right portions if the zip code.  The third Boolean expression identifies 
postal codes consisting of 5 numeric digits so that its resultant expression 
can append a hyphen and 4 zeros.  The final Boolean expression inspects the 
input postal code to determine that it is not a number.  We will assume that 
it has the correct alternating number and letter pattern for the Canadian 
postal code.  Its length is also checked to be 6 characters.  When the 
criteria in this Boolean expression are satisfied, a space is inserted 
between the left hand three characters and the right hand three characters.  
The Ucase$() function is used again to assure that all letters in the 
resultant are upper case.  The query result table shows that the last input 
postal code met none of the Switch function criteria and was not formatted.

SELECT PostalCode, 
SWITCH(LEN(PostalCode) IN(10,7),
UCASE$(PostalCode),
IsNumeric(PostalCode) AND LEN(PostalCode)=9,
LEFT$(PostalCode,5) & "-" & RIGHT$(PostalCode,4),
IsNumeric(PostalCode) AND LEN(PostalCode)=5,
PostalCode & "-0000",
NOT IsNumeric(PostalCode) AND LEN(PostalCode)=6,
UCASE$(LEFT$(PostalCode,3) & " " & RIGHT$(PostalCode,3)))
AS fmtPostalCode FROM TestZip1;

|   PostalCode    |    fmtPostalCode    |
| 80538-1200      | 80538-1200          |
| 80502           | 80502-0000          |
| 805032110       | 80503-2110          |
| k1a0b1          | K1A 0B1             |
| A1C 5X4         | A1C 5X4             |
| 123             |                     |

There are many more functions available for use in SQL.  I will list all of 
the functions I can think of below.  I will admit, I personally have had no 
use for the Math and Financial functions.  I know they are there if I ever do 
need them.  Writing this article has stretched my knowledge of Program Flow 
functions.  I have used the IIF() function many times and now can see good 
uses for the Choose() function.  

Many of the SQL Aggregate functions have been very useful to me.  They are 
readily available in the query design window along with the Group By and 
Where summary operators and the First() and Last() functions.  They are used 
to achieve a combined result from groups of records.

I hope you enjoy more newly discovered functionality in SQL!

Addendum List of SQL Built In functions with arguments:
Text
Asc («stringexpr»)
Chr («charcode»)
Chr$ («charcode»)
Format («expr»[, «fmt»[, «firstweekday»[, «firstweek»]]]) 
Format$ («expr»[, «fmt»[, «firstweekday»[, «firstweek»]]]) 
Instr ([«start»,] «stringexpr1», «stringexpr2»[, «compare»]) 
LCase («stringexpr») 
LCase$ («stringexpr») 
Left («stringexpr», «n») 
Left$ («stringexpr», «n») 
Len («stringexpr») 
LTrim («stringexpr») 
LTrim$ («stringexpr») 
Mid («stringexpr», «start»[, «length»]) 
Mid$ («stringexpr», «start»[, «length»]) 
Right («stringexpr», «n») 
Right$ («stringexpr», «n») 
RTrim («stringexpr») 
RTrim$ («stringexpr») 
Space («number») 
Space$ («number») 
StrComp («stringexpr1», «stringexpr2»[, «compare»]) 
String («number», «charcode»[, «stringexpr»]) 
String$ («number», «charcode»)
Trim («stringexpr»)
Trim$ («stringexpr»)
UCase («stringexpr»)
UCase$ («stringexpr»)

Math
Abs («number»)
Atn («number»)
Cos («angle»)  in Radians = Degrees*(Pi/180)
Exp («number») 
Fix («number»)
Int («number»)
Log («number») Base e
Rnd («number»)
Sgn («number»)
Sin («angle»)  in Radians = Degrees*(Pi/180)
Sqr («number»)
Tan («angle»)  in Radians = Degrees*(Pi/180)

Financial
DDB («cost», «salvage», «life», «period») 
FV («rate», «nper», «pmt»[, «pv»[, «due»]]) 
IPmt («rate», «period», «nper», «pv»[, «fv»[, «due»]]) 
IRR («ValueArray»[, «guess»])
MIRR («ValueArray», «FinanceRate», «ReinvestRate») 
NPer («rate», «pmt», «pv»[, «fv»[, «due»]]) 
NPV («rate», «valueArray») 
Pmt («rate», «nper», «pv»[, «fv»[, «due»]]) 
PPmt («rate», «period», «nper», «pv»[, «fv»[, «due»]]) 
PV («rate», «nper», «pmt»[, «fv»[, «due»]]) 
Rate («nper», «pmt», «pv»[, «fv»[, «due»[, «guess»]]]) 
SLN («cost», «salvage», «life») 
SYD («cost», «salvage», «life», «period») 

Inspection
IsDate («varexpr»)
IsEmpty («varexpr»)
IsNull («varexpr»)
IsNumeric («varexpr»)
VarType («varexpr»)

SQLAggregate
Avg («expr»)
Count («expr»)
Max («expr»)
Min («expr»)
StDev («expr»)
StDevP («expr»)
Sum («expr»)
Var («expr»)
VarP («expr»)

ProgramFlow
Choose («indexnum», «varexpr») 
IIf («expr», «truepart», «falsepart»)
Switch («varexpr1», «varexpr1», «varexpr2», «varexpr2») 

Conversion
Asc («stringexpr»)
CBooI («expr»)
CCur («expr»)
CDate («date»)
CDbl («expr»)
Chr («charcode»)
Chr$ («charcode»)
CInt («expr») 
CLng («expr») 
CSng («expr») 
CStr («expr») 
CVar («expr») 
CVDate («expr») 
DateSerial («year», «month», «day»)
DateValue («stringexpr»)
Day («number») 
Hex («number») 
Hex$ («number») 
Hour («number») 
Minute («number») 
Month («number») 
Oct («number») 
Oct$ («number») 
Second («number») 
Str («number») 
Str$ («number») 
TimeSerial («hour», «minute», «second»)
TimeValue («stringexpr»)
Val («stringexpr»)
Weekday («number») 
Year («number») 


Date/Time
CDate («date»)
CVDate («expr»)
Date ()
Date$ ()
DateAdd («interval», «number», «date») 
DateDiff («interval», «date1», «date2»[, «firstweekday»[, «firstweek»]]) 
DatePart («interval», «date»[, «firstweekday»[, «firstweek»]]) 
DateSerial («year», «month», «day») 
DateValue («stringexpr») 
Day («number») 
Hour («number») 
IsDate («varexpr») 
Minute («number») 
Month («number») 
Now ()
Second («number») 
Time ()
Time$ ()
Timer ()
TimeSerial («hour», «minute», «second») 
TimeValue («stringexpr»)
Weekday («number»)
Year («number»)

The DateAdd(), DateDiff() & DatePart() interval argument has these settings:
Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second