Wednesday, February 20, 2013

Microsoft Access - Right Function

Right Function

This function extracts a specified number of characters from the right side of the string.
 

Syntax:

Right(String,Length_of_characters) or Right([ColumnName],length_of_characters)

 

String expression from which the rightmost characters to be extracted.
 

Length argument tells how many characters to be extracted from the string argument. If length argument is 0 it will return a zero length string will be returned. If the string contains Null, Null is returned. If greater than or equal to the length of the string, the entire string is returned.
 

Examples:

Right(“Access”,4) will return the string “cess”. It will extract 4 rightmost characters from the string.
Right(“Access”,10) will return the string “Access”. It extracted full string because the length of Access string is 6 and the argument supplied is more than 6.




Monday, February 18, 2013

Microsoft Access - Mid$ Function

Mid$ Function

This Function will extract the specified number of characters from a string. This function is similar to Mid Function but executes 40% faster than it.


Syntax:

Mid$(string,start,length_of_Characters) or Mid$([ColumnName],start,lengthct_of_Characters) 

String argument from which we have to extract the characters from it.

Start is integer value which tells the character position in string from that position characters are extracted. If start is greater than the number of characters in string, this function will return a zero length string (“”).
 

Length is integer value from start position how many characters to be extracted. It is optional. If not specified, this function will extract the characters from start position to the end of the string.
 

Examples:

Mid$(“Hello World”,5) will return “o World”. It extracts characters from the position 5 specified in the start argument.
Mid$(“Hello World”, 4, 5) will return “lo Wo”. It will extract 5 characters mentioned in the length_of_characters argument from the position 4 mentioned in the start argument.




Microsoft Access - DStDev and DStDevP Function

DStDev and DStDevP  Function

This function calculates the Standard Deviation of a field (column) in a database for selected records, which satisfy user-specified criteria.
DStDevP function to evaluate the Standard Deviation for a population while DStDev is used to evaluate the Standard Deviation for a population sample.
 

Syntax:

DStDev(Expression, Domain,Criteria)
DStDevP(Expression,Domain,Criteria)

Expression is the column name or field name that you want to calculate the Standard Deviation.

Domain is the set of records from a table or query.
 
Criteria argument is the string expression that used to restrict the data on which the DStDevP or DStDev Function is performed.
 

Examples:

DStDev("Sales_Value","Dmnagrgtfun","Quarter=1") returns 543.543590104266. You can use round function  to round off to 2 decimal places.
Sales_value is the field name for which we need to calculate the Standard Deviation.
 
Dmnagrgtfn is the table where Sales_value column or field name exists.
 
For the Quarter=1 we need to calculate the Standard Deviation. So it is given as the criteria for DStDev  Function.

 



 
DStDevP("Sales_Value","Dmnagrgtfun","Quarter=1") returns 537.470286746907. You can use round function  to round off to 2 decimal places.
 
Sales_value is the field name for which we need to calculate the Standard Deviation.
Dmnagrgtfn is the table where Sales_value column or field name exists.
 

For the Quarter=1 we need to calculate the Standard Deviation. So it is given as the criteria for DStDevP  Function.
 

Microsoft Access - LTrim Function

LTrim Function

This function will remove the unwanted spaces at the beginning or leading spaces from the text string.


Syntax:

LTrim(String) or LTrim([ColumnName])

The string to be trimmed is supplied as an argument.
 

Examples:

LTrim("   Hello") returns “Hello”. It removed unwanted spaces at the beginning of the string.
LTrim("   Hello     World") returns “Hello     World”. It removed unwanted spaces only at the beginning of the string but not at the end or middle of the string.




Sunday, February 10, 2013

Microsoft Access - Space$ Function

Space$ Function

This function will return the string with number of spaces required in a string or expression. This function is similar to Space function but executes 40% faster than it.
 

Syntax:

Space$(number)
 

The argument for the Space$ function will be a number. It is an integer value. It specifies how many spaces you want in the string. This function is mainly used to formatting the data.
 

Examples:

"Hello" & Space$(3) & "World" will return “Hello   World”. The space$ function inserted three spaces between the strings “Hello” and “World”.




Sunday, February 3, 2013

Microsoft Access - InstrRev Function

InStrRev Function

This function will return the first position of one string within another. This function will start searching the string from Right hand side of the string.


Syntax:

InStrRev(String_being_searched,String_to_be_searched,start,compare) or
InStrRev(String_being_searched,[ColumnName],start,compare) or
InStrRev([ColumnName], String_to_be_searched,start,compare) or
InStrRev([ColumnName1],[ColumnName2],start,compare)


This function has the following arguments.
 

Start is an integer value it instructs InStrRev function where to start the search string1 within string2 from right hand side. This is optional. If mentioned you have to supply the compare argument. If omitted, -1 is used, which means that the search begins at the last character 
position. Is start argument is Null, an error occurs.
 

The String expression being searched is specified by String_being_searched argument.
 

The String that has to be sought is specified by String_to_be_searched. argument.



The compare argument is an integer indicating what kind of comparison to use when evaluating substrings. This is optional. The setting for this argument is as follows.
 
Value Description
-1 Performs a comparison using the setting from option compare statement.
0 Binary comparison
1 Textual comparison

If string_being_searched is 0, InstrRev function returns 0.
If String_being_searched is Null, InStrRev Function returns Null.
If String_to_be_searched is a zero-length string “” then InStrRev function will return start argument value.
If String_to_be_searched is Null, InStrRev Function returns Null.
If String_to_be_searched is not found within String_being_searched, InStrRev Function returns 0.
If String_to_be_searched is found within String_being_searched, InStrRev Function returns position at which the match was found.
If start is greater than the lenth of the String_to_be_searched, InStrRev function will return 0.
 

Examples:

InStrRev("Hello World","l") returns 10. It searches “l” in the string “Hello World” from right hand side of the string and match is found at the position 10. If you search it from the function Instr Function, it  will return 4.
InStrRev("Hello World","el",-1,1) returns 2. It searches “el” in the string “Hello World” from the position -1 i.e at the extreme right end as specified in the start argument and match is found at the position 2 and uses a textual comparison.




Wednesday, January 30, 2013

Microsoft Access - DAvg Function

DAvg Function

This function calculates the average of a field (column) in a database for selected records, which satisfy user-specified criteria.

Syntax:

DAvg(Expression,Domain,Criteria)

Expression is the column name or field name that you want to average.
Domain is the set of records from a table or query.
Criteria argument is the string expression that used to restrict the data on which the Davg Function is performed.
 

Example:


DAvg(“Sales_Value”,”Dmnagrgtfun”,”Quarter=1”) returns 1181.95555555556. you can use round function  to round off to 2 decimal places.
Sales_value is the field name for which we need to calculate the average.
Dmnagrgtfn is the table where Sales_value column or field name exists.
For the Quarter=1 we need to calculate the average. So it is given as the criteria for DAvg Function.



Sometimes you need to calculate use the same query for different quarters, for which you can specify a parameter like this.
DAvg("Sales_Value","Dmnagrgtfun","Quarter=" & [Enter Quarter] & "")




 






If you run this query it is prompt you  to enter the Enter Quarter like shown in the dialog box.


 








After supplying the parameter you can view the results of the query.