Chapter 3 - Access Built-in Function

Access Instr Function

October 6, 2016 by Wyman W

This Access tutorial explains how to use Access INSTR Function in Expression and VBA, and explain difference among vbUseCompareOption, vbBinaryCompare and vbTextCompare.

Access INSTR Function

Access INSTR function is used to search a substring (part of the string) within a string (string is a text) and return the position of the first occurrence.

For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.

If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, number “0” will return.

INSTR is always used to test whether a string contains a substring, as always we just want to know “True” or “False” but not the actual position, so we can simply test whether the returned value is “0”.

INSTR Function can be used in Access Expression as well as VBA. For Excel spreadsheet, since there is no INSTR function, consider using Search Function or Find Function.

Syntax of Access INSTR Function

InStr( [start], string, substring, [compare] )

[start is] optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.

string is the full string

substring is part of the string you want to search

[compare] is optional. It is the type of comparison to perform. It can be one of the following values:

Constant	Value	Description
vbUseCompareOption	-1	Use setting of Option Compare statement defined at Module level. In the absence of any Compare Option, BinaryCompare is used (case sensitive)
vbBinaryCompare	0	Binary comparison, turn text into a sequence to compare. Since the sequence is case sensitive, you can compare text using this option if you want the comparison to be case sensitive, which means you cannot search “aaa” within “AAA”
vbTextCompare	1	Performs a case insensitive textual comparison.
vbDatabaseCompare	2	Performs a comparison based on information in your database (case insensitive), it is the default value when you create a database.

Note that if you use [compare], you have to also define [start], otherwise Access mistakenly thinks your string is start.

Below are the error handling of invalid parameters.

If	InStr returns
string1 is zero-length	0
string1 is Null	Null
string2 is zero-length	start
string2 is Null	Null
string2 is not found	0
string2 is found within string1	Position at which match is found
start > string2	0

Example of Access INSTR Function

VBA Code	Result	Explanation
INSTR(3,”123123″,”2″)	5	The first occurrence of “2” in “123123” starting from position 3 is at position 5
INSTR(“FINANCE”,”f”)	0	By default, comparison is case sensitive, therefore “f” cannot be found in “Finance” and return 0
INSTR(1, “FINANCE”, “f”, vbTextCompare)	1	vbTextCompare turns the comparison into case insensitive. Defining[compare] requires you to also define [start]

Alternative of Access VBA INSTR Function – using Wildcard

In Access VBA, if you just want to check if a text contains specific substring, you can use wildcard to return TRUE or FALSE.

VBA supports the below three Wildcard characters. Note that Wildcard is case sensitive.

Wildcard	Meaning	Example
*	Represents one or more characters (any character)	J* any text that starts with J J starts with any text but ends with J J* any text that has J in the middle
?	Represents one character (any character)	J? 2 characters that start with J ?J 2 characters that end with J ?J? 3 characters with J in the middle
~	Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ?	J~** any text that starts with J* ~*J any text that starts with and ends with J ~?~ any text that starts with ? and ends with *

The below example returns a TRUE Msgbox:

Public Sub example()
    If "123456" Like "*23*" Then
        MsgBox (True)
    Else: MsgBox (False)
    End If
End Sub

Outbound References

https://support.office.com/en-us/article/InStr-Function-85D3392C-3B1C-4232-BB18-77CD0CB8A55B

Access Excel INT Function to get the integer of number

October 4, 2016 by Wyman W

This Access / Excel tutorial explains how to use INT Function to get the integer part of a number.

Access Excel INT Function to get the integer of number

When you have a number with decimal places, you can separate the number into integer part using INT Function, and then work around to get the decimal part. INT Function can be used in Access and Excel, and also VBA.

Syntax of INT Function

INT(number)

INT Function only contains one argument, which is the number from which you want to get the integer part.

The syntax for Access, Excel and VBA are same.

Example of INT Function

Example for positive numbers

Formula	Result	Explanation
INT(11.4)	11	Integer part of 11.4 is 11
INT(11.6)	11	Integer part of 11.6 is 11 also ,regardless whether the decimal part is over 0.5
11.6-INT(11.6)	0.6	Return the decimal part of 11.6

Unlike positive number, handling of negative number is a little bit confusing, it returns the first whole number less than your input number. For example, -5.1 would become -6. In the below examples, I will demonstrate how to return 5 or -5 instead of -6.

Formula	Result	Explanation
INT(-5.1)	-6	The first whole number less than -5.1 is -6
INT(abs(-5.1))	5	To return the true integer part using absolute function
IF(A1<0,-INT(ABS(A1)),INT(ABS(A1)))	-5	Suppose A1 is -5.1, to return the true integer part and then add the sign back

Example of VBA INT Function

INT Function is particularly useful in custom rounding, below is a custom MRound function that I created in my previous post, click here to read more.

Function wMRound(pValue As Double, multiple) As Double
    Dim negNumber As Boolean
    If pValue < 0 Then
        pValue = 0 - pValue
        negNumber = True
    End If
    If multiple < 0 Then
        multiple = 0 - multiple
    End If
    
    Select Case pValue / multiple - Int(pValue / multiple)
        Case Is < 0.5
            result = Int(pValue / multiple) * multiple
        Case Is >= 0.5
            result = Int(pValue / multiple) * (multiple) + multiple
    End Select
        
    If negNumber = True Then
        wMRound = 0 - result
    Else
        wMRound = result
    End If
End Function

Outbound References

https://support.office.com/en-us/article/INT-function-A6C4AF9E-356D-4369-AB6A-CB1FD9D343EF

Access Mid Function to extract substring

September 26, 2016 by Wyman W

This Access tutorial explains how to use Access MID Function to extract substring from a string.

Access MID Function

MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string. Other than Access, Mid Function can also be used in Excel worksheet, Excel / Access VBA.

Access MID Function is commonly used with Instr Function (see below example), while Excel MID Function can be used with Search Function and Find Function.

Syntax of Access MID Function

MID( text, start_position, number_of_characters )

text	The text string from which you want to extract the characters
start_position	The position of the first character you want to extract. Positions start at 1.
number_of_character	The number of characters to return.

Example of Access MID Function

Formula	Result	Explanation
MID(“Access Excel.Tips”,1,6)	Access	Extract substring from position 1 to 6
MID(“Peter,Gilbert”,1,InStr(1,”Peter,Gilbert”,”,”)-1)	Peter	Extract substring from position 1 to (comma position-1)
MID(“Peter,Gilbert”, InStr(1,”Peter,Gilbert”, “,”) + 1, Len(“Peter,Gilbert”))	Gilbert	Extract substring from (comma position+1) to (length of whole string)

Outbound References

https://support.office.com/en-us/article/Mid-Function-427E6895-822C-44EE-B34A-564A28F2532C

Access Excel VBA Chr Function to convert ASCII value to character

April 5, 2015February 9, 2015 by Wyman W

This Access Excel VBA tutorial explains how to use Chr Function to convert ASCII value to character, and convert from character to ASCII value with ASC Function

What is Access Excel VBA Chr Function?

Access Excel VBA Chr Function converts ASCII value to a character. ASCII (American Standard Code for Information Interchange) uses 8-bit code units, an old encoding system which stores mainly numbers, lowercase letters a to z, uppercase letters A to Z, basic punctuation symbols, control codes. Many old systems still use this encoding system. 8 bit means the computer memory uses “8” digits with 1 and 0 combination (binary) to represent a character, 8 bits memory is equal to 1 byte.

Chr Function is applicable to: Excel VBA, Access, Access VBA

Refer to the below table, Excel Char Function will convert the “Dec” value to “Char” value.

Dec	Hex	Oct	Char	Description
0	0	000		null
1	1	001		start of heading
2	2	002		start of text
3	3	003		end of text
4	4	004		end of transmission
5	5	005		enquiry
6	6	006		acknowledge
7	7	007		bell
8	8	010		backspace
9	9	011		horizontal tab
10	A	012		new line
11	B	013		vertical tab
12	C	014		new page
13	D	015		carriage return
14	E	016		shift out
15	F	017		shift in
16	10	020		data link escape
17	11	021		device control 1
18	12	022		device control 2
19	13	023		device control 3
20	14	024		device control 4
21	15	025		negative acknowledge
22	16	026		synchronous idle
23	17	027		end of trans. block
24	18	030		cancel
25	19	031		end of medium
26	1A	032		substitute
27	1B	033		escape
28	1C	034		file separator
29	1D	035		group separator
30	1E	036		record separator
31	1F	037		unit separator
32	20	040		space
33	21	041	!
34	22	042	“
35	23	043	#
36	24	044	$
37	25	045	%
38	26	046	&
39	27	047	‘
40	28	050	(
41	29	051	)
42	2A	052	*
43	2B	053	+
44	2C	054	,
45	2D	055	–
46	2E	056	.
47	2F	057	/
48	30	060	0
49	31	061	1
50	32	062	2
51	33	063	3
52	34	064	4
53	35	065	5
54	36	066	6
55	37	067	7
56	38	070	8
57	39	071	9
58	3A	072	:
59	3B	073	;
60	3C	074	<
61	3D	075	=
62	3E	076	>
63	3F	077	?
64	40	100	@
65	41	101	A
66	42	102	B
67	43	103	C
68	44	104	D
69	45	105	E
70	46	106	F
71	47	107	G
72	48	110	H
73	49	111	I
74	4A	112	J
75	4B	113	K
76	4C	114	L
77	4D	115	M
78	4E	116	N
79	4F	117	O
80	50	120	P
81	51	121	Q
82	52	122	R
83	53	123	S
84	54	124	T
85	55	125	U
86	56	126	V
87	57	127	W
88	58	130	X
89	59	131	Y
90	5A	132	Z
91	5B	133	[
92	5C	134	\
93	5D	135	]
94	5E	136	^
95	5F	137	_
96	60	140	`
97	61	141	a
98	62	142	b
99	63	143	c
100	64	144	d
101	65	145	e
102	66	146	f
103	67	147	g
104	68	150	h
105	69	151	i
106	6A	152	j
107	6B	153	k
108	6C	154	l
109	6D	155	m
110	6E	156	n
111	6F	157	o
112	70	160	p
113	71	161	q
114	72	162	r
115	73	163	s
116	74	164	t
117	75	165	u
118	76	166	v
119	77	167	w
120	78	170	x
121	79	171	y
122	7A	172	z
123	7B	173	{
124	7C	174	\|
125	7D	175	}
126	7E	176	~
127	7F	177	DEL

Extended Character Set

Dec	Hex	Unicode	Char	Name
128	80	U+20AC	€	Euro Sign
129	81			Undefined
130	82	U+201A	‚	Single Low-9 Quotation Mark
131	83	U+0192	ƒ	Latin Small Letter F With Hook
132	84	U+201E	„	Double Low-9 Quotation Mark
133	85	U+2026	…	Horizontal Ellipsis
134	86	U+2020	†	Dagger
135	87	U+2021	‡	Double Dagger
136	88	U+02C6	ˆ	Modifier Letter Circumflex Accent
137	89	U+2030	‰	Per Mille Sign
138	8A	U+0160	Š	Latin Capital Letter S With Caron
139	8B	U+2039	‹	Single Left-pointing Angle Quotation Mark
140	8C	U+0152	Œ	Latin Capital Ligature Oe
141	8D			Undefined
142	8E	U+017D	Ž	Latin Capital Letter Z With Caron
143	8F			Undefined
144	90			Undefined
145	91	U+2018	‘	Left Single Quotation Mark
146	92	U+2019	’	Right Single Quotation Mark
147	93	U+201C	“	Left Double Quotation Mark
148	94	U+201D	”	Right Double Quotation Mark
149	95	U+2022	•	Bullet
150	96	U+2013	–	En Dash
151	97	U+2014	—	Em Dash
152	98	U+02DC	˜	Small Tilde
153	99	U+2122	™	Trade Mark Sign
154	9A	U+0161	š	Latin Small Letter S With Caron
155	9B	U+203A	›	Single Right-pointing Angle Quotation Mark
156	9C	U+0153	œ	Latin Small Ligature Oe
157	9D			Undefined
158	9E	U+017E	ž	Latin Small Letter Z With Caron
159	9F	U+0178	Ÿ	Latin Capital Letter Y With Diaeresis
160	A0	U+00A0		No-break Space
161	A1	U+00A1	¡	Inverted Exclamation Mark
162	A2	U+00A2	¢	Cent Sign
163	A3	U+00A3	£	Pound Sign
164	A4	U+00A4	¤	Currency Sign
165	A5	U+00A5	¥	Yen Sign
166	A6	U+00A6	¦	Broken Bar
167	A7	U+00A7	§	Section Sign
168	A8	U+00A8	¨	Diaeresis
169	A9	U+00A9	©	Copyright Sign
170	AA	U+00AA	ª	Feminine Ordinal Indicator
171	AB	U+00AB	«	Left-pointing Double Angle Quotation Mark
172	AC	U+00AC	¬	Not Sign
173	AD	U+00AD		Soft Hyphen
174	AE	U+00AE	®	Registered Sign
175	AF	U+00AF	¯	Macron
176	B0	U+00B0	°	Degree Sign
177	B1	U+00B1	±	Plus-minus Sign
178	B2	U+00B2	²	Superscript Two
179	B3	U+00B3	³	Superscript Three
180	B4	U+00B4	´	Acute Accent
181	B5	U+00B5	µ	Micro Sign
182	B6	U+00B6	¶	Pilcrow Sign
183	B7	U+00B7	·	Middle Dot
184	B8	U+00B8	¸	Cedilla
185	B9	U+00B9	¹	Superscript One
186	BA	U+00BA	º	Masculine Ordinal Indicator
187	BB	U+00BB	»	Right-pointing Double Angle Quotation Mark
188	BC	U+00BC	¼	Vulgar Fraction One Quarter
189	BD	U+00BD	½	Vulgar Fraction One Half
190	BE	U+00BE	¾	Vulgar Fraction Three Quarters
191	BF	U+00BF	¿	Inverted Question Mark
192	C0	U+00C0	À	Latin Capital Letter A With Grave
193	C1	U+00C1	Á	Latin Capital Letter A With Acute
194	C2	U+00C2	Â	Latin Capital Letter A With Circumflex
195	C3	U+00C3	Ã	Latin Capital Letter A With Tilde
196	C4	U+00C4	Ä	Latin Capital Letter A With Diaeresis
197	C5	U+00C5	Å	Latin Capital Letter A With Ring Above
198	C6	U+00C6	Æ	Latin Capital Ligature Ae
199	C7	U+00C7	Ç	Latin Capital Letter C With Cedilla
200	C8	U+00C8	È	Latin Capital Letter E With Grave
201	C9	U+00C9	É	Latin Capital Letter E With Acute
202	CA	U+00CA	Ê	Latin Capital Letter E With Circumflex
203	CB	U+00CB	Ë	Latin Capital Letter E With Diaeresis
204	CC	U+00CC	Ì	Latin Capital Letter I With Grave
205	CD	U+00CD	Í	Latin Capital Letter I With Acute
206	CE	U+00CE	Î	Latin Capital Letter I With Circumflex
207	CF	U+00CF	Ï	Latin Capital Letter I With Diaeresis
208	D0	U+00D0	Ð	Latin Capital Letter Eth
209	D1	U+00D1	Ñ	Latin Capital Letter N With Tilde
210	D2	U+00D2	Ò	Latin Capital Letter O With Grave
211	D3	U+00D3	Ó	Latin Capital Letter O With Acute
212	D4	U+00D4	Ô	Latin Capital Letter O With Circumflex
213	D5	U+00D5	Õ	Latin Capital Letter O With Tilde
214	D6	U+00D6	Ö	Latin Capital Letter O With Diaeresis
215	D7	U+00D7	×	Multiplication Sign
216	D8	U+00D8	Ø	Latin Capital Letter O With Stroke
217	D9	U+00D9	Ù	Latin Capital Letter U With Grave
218	DA	U+00DA	Ú	Latin Capital Letter U With Acute
219	DB	U+00DB	Û	Latin Capital Letter U With Circumflex
220	DC	U+00DC	Ü	Latin Capital Letter U With Diaeresis
221	DD	U+00DD	Ý	Latin Capital Letter Y With Acute
222	DE	U+00DE	Þ	Latin Capital Letter Thorn
223	DF	U+00DF	ß	Latin Small Letter Sharp S
224	E0	U+00E0	à	Latin Small Letter A With Grave
225	E1	U+00E1	á	Latin Small Letter A With Acute
226	E2	U+00E2	â	Latin Small Letter A With Circumflex
227	E3	U+00E3	ã	Latin Small Letter A With Tilde
228	E4	U+00E4	ä	Latin Small Letter A With Diaeresis
229	E5	U+00E5	å	Latin Small Letter A With Ring Above
230	E6	U+00E6	æ	Latin Small Ligature Ae
231	E7	U+00E7	ç	Latin Small Letter C With Cedilla
232	E8	U+00E8	è	Latin Small Letter E With Grave
233	E9	U+00E9	é	Latin Small Letter E With Acute
234	EA	U+00EA	ê	Latin Small Letter E With Circumflex
235	EB	U+00EB	ë	Latin Small Letter E With Diaeresis
236	EC	U+00EC	ì	Latin Small Letter I With Grave
237	ED	U+00ED	í	Latin Small Letter I With Acute
238	EE	U+00EE	î	Latin Small Letter I With Circumflex
239	EF	U+00EF	ï	Latin Small Letter I With Diaeresis
240	F0	U+00F0	ð	Latin Small Letter Eth
241	F1	U+00F1	ñ	Latin Small Letter N With Tilde
242	F2	U+00F2	ò	Latin Small Letter O With Grave
243	F3	U+00F3	ó	Latin Small Letter O With Acute
244	F4	U+00F4	ô	Latin Small Letter O With Circumflex
245	F5	U+00F5	õ	Latin Small Letter O With Tilde
246	F6	U+00F6	ö	Latin Small Letter O With Diaeresis
247	F7	U+00F7	÷	Division Sign
248	F8	U+00F8	ø	Latin Small Letter O With Stroke
249	F9	U+00F9	ù	Latin Small Letter U With Grave
250	FA	U+00FA	ú	Latin Small Letter U With Acute
251	FB	U+00FB	û	Latin Small Letter U With Circumflex
252	FC	U+00FC	ü	Latin Small Letter U With Diaeresis
253	FD	U+00FD	ý	Latin Small Letter Y With Acute
254	FE	U+00FE	þ	Latin Small Letter Thorn
255	FF	U+00FF	ÿ	Latin Small Letter Y With Diaeresis

Why do you need Access Excel VBAChr Function?

1. To import special characters. However, Windows may not be able to show those characters.

2. Start a new line in a Cell with Char(10) in worksheet (must need to set Wrap Text first) and MsgBox message

Syntax of Access Excel VBA Chr Function

CHR( ascii_value )

ascii_value is the ASCII value used to retrieve the character.

Note that Chr Function also exists for worksheet but the name is Char. You can convert the character back into ASCII value using Code Function in worksheet / ASC Function in VBA

Example of Access Excel VBA Chr Function

Formula	Result	Explanation
Chr(65)	A
Chr(90)	Z
Range(“A1”).Value = “line one” & Chr(10) & “line two”	line one line two	Start a new line with Char(10)
Range(“A1”).Value = Replace(“line one,line two”, “,”, Chr(10))	line one line two	Replace comma with new line

To start a new line in VBA MsgBox

msgbox_03

Public Sub test1()
    sInput = MsgBox("Are you sure to delete the data?" & Chr(10) & "Data will be deleted permanently")
End Sub

Outbound References

http://www.techonthenet.com/excel/formulas/char.php

Access Round Function and custom Round Function

March 17, 2019December 26, 2014 by Wyman W

This tutorial explains how to use Access Round Function (round to even logic) and create a custom Round Function to simulate Excel Round Function.

You may also want to read:

VBA Excel Access roundup rounddown Function

Excel Access MRound custom Function round to nearest multiple

Access Round Function – Round to even logic

Access Round Function is completely different from Excel Round Function.

For Excel Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down.

For Access Round Function, as well as Access VBA, Excel VBA, the Round Function uses “Round to even” logic. Integer rounds up if decimal part >=0.6, round down if <=0.4. For case decimal part exactly =0.5, , then round down, otherwise round up(round up to even integer), same logic applies to negative number (See example).

I summarize the Access round to even logic below.

If decimal part >=0.6 Then
   Round up
ElseIf decimal part <=0.4 Then
   Round down
ElseIf decimal part = 0.5 Then
   If integer part is even number Then
        Round down
   ElseIf integer part is odd number Then
        Round up
  End If
End If

Round to even logic has other names:

-unbiased rounding

-convergent rounding

-statistician’s rounding

-Dutch rounding

-Gaussian rounding

-odd-even rounding

-bankers’ rounding

-broken rounding

Syntax of Access Round Function – Round to even logic

Round ( expression, [ decimal_places ] )

If [decimal_places] is omitted, default is 0 (round to integer)

Example of Access Round Function – Round to even logic

Round([Number],0)

Number	Access Round	Excel Round	Check Diff
0.1	0	0
0.2	0	0
0.3	0	0
0.4	0	0
0.5	0	1	integer part is even number, round down
0.6	1	1
0.7	1	1
0.8	1	1
0.9	1	1
1	1	1
1.1	1	1
1.2	1	1
1.3	1	1
1.4	1	1
1.5	2	2	integer part is odd number, round up
1.6	2	2
1.7	2	2
1.8	2	2
1.9	2	2
2	2	2
2.1	2	2
2.2	2	2
2.3	2	2
2.4	2	2
2.5	2	3	integer part is even number, round down
2.6	3	3
2.7	3	3
2.8	3	3
2.9	3	3
3	3	3
3.1	3	3
3.2	3	3
3.3	3	3
3.4	3	3
3.5	4	4	integer part is odd number, round up
3.6	4	4
3.7	4	4
3.8	4	4
3.9	4	4
4	4	4
4.1	4	4
4.2	4	4
4.3	4	4
4.4	4	4
-4.5	4	5	integer part is even number, round down
-4.6	5	5
-4.7	5	5
-4.8	5	5
-4.9	5	5
-5	5	5
-5.1	5	5
-5.2	5	5
-5.3	5	5
-5.4	5	5
-5.5	6	6	integer part is odd number, round up

VBA Code of custom Access Round Function – simulate Excel Round function

Some custom Round Function you can find on the Internet do not consider cases for negative number , but this one does.

Function wRound(pValue As Double, digit) As Double
    Dim ExpandedValue
    Dim IntPart As Long
    Dim FractionPart As Double
    Dim negNumber As Boolean
    If pValue < 0 Then
        pValue = 0 - pValue
        negNumber = True
    End If
    
    ExpandedValue = pValue * (10 ^ digit) 'Retrieve integer part of the number
    IntPart = Int(ExpandedValue)
    FractionPart = ExpandedValue - IntPart 'Retrieve the fraction part of the number
    If FractionPart < 0.5 Then
        result = IntPart / 10 ^ digit
    ElseIf FractionPart >= 0.5 Then
        result = (IntPart + 1) / 10 ^ digit
    End If
    
    If negNumber = True Then
        wRound = 0 - result
    Else
        wRound = result
    End If
End Function

Syntax of custom Access Round Function – simulate Excel Round function

wRound(pValue, digit)

pValue	the decimal value you want to convert
digit	the number of decimal places you want to convert to

Outbound References

http://www.techonthenet.com/access/functions/numeric/round.php