Access Instr Function

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:

ConstantValueDescription
vbUseCompareOption-1Use setting of Option Compare statement defined at Module level. In the absence of any Compare Option, BinaryCompare is used (case sensitive)
vbBinaryCompare0Binary 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”
vbTextCompare1Performs a case insensitive textual comparison.
vbDatabaseCompare2Performs 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.

IfInStr returns
string1 is zero-length0
string1 is NullNull
string2 is zero-lengthstart
string2 is NullNull
string2 is not found0
string2 is found within string1Position at which match is found
start > string20

Example of Access INSTR Function

VBA CodeResultExplanation
INSTR(3,”123123″,”2″)5The first occurrence of “2” in “123123” starting from position 3 is at position 5
INSTR(“FINANCE”,”f”)0By default, comparison is case sensitive, therefore “f” cannot be found in “Finance” and return 0
INSTR(1, “FINANCE”, “f”, vbTextCompare)1vbTextCompare 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.

WildcardMeaningExample
*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

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

FormulaResultExplanation
INT(11.4)11Integer part of 11.4 is 11
INT(11.6)11Integer part of 11.6 is 11 also ,regardless whether the decimal part is over 0.5
11.6-INT(11.6)0.6Return 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.

FormulaResultExplanation
INT(-5.1)-6The first whole number less than -5.1 is -6
INT(abs(-5.1))5To return the true integer part using absolute function
IF(A1<0,-INT(ABS(A1)),INT(ABS(A1)))-5Suppose 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

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 )
textThe text string from which you want to extract the characters
start_positionThe 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

FormulaResultExplanation
MID(“Access Excel.Tips”,1,6)AccessExtract substring from position 1 to 6
MID(“Peter,Gilbert”,1,InStr(1,”Peter,Gilbert”,”,”)-1)PeterExtract substring from position 1 to (comma position-1)
MID(“Peter,Gilbert”, InStr(1,”Peter,Gilbert”, “,”) + 1, Len(“Peter,Gilbert”))GilbertExtract 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

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.

DecHexOctCharDescription
00000null
11001start of heading
22002start of text
33003end of text
44004end of transmission
55005enquiry
66006acknowledge
77007bell
88010backspace
99011horizontal tab
10A012new line
11B013vertical tab
12C014new page
13D015carriage return
14E016shift out
15F017shift in
1610020data link escape
1711021device control 1
1812022device control 2
1913023device control 3
2014024device control 4
2115025negative acknowledge
2216026synchronous idle
2317027end of trans. block
2418030cancel
2519031end of medium
261A032substitute
271B033escape
281C034file separator
291D035group separator
301E036record separator
311F037unit separator
3220040space
3321041!
3422042
3523043#
3624044$
3725045%
3826046&
3927047
4028050(
4129051)
422A052*
432B053+
442C054,
452D055
462E056.
472F057/
48300600
49310611
50320622
51330633
52340644
53350655
54360666
55370677
56380708
57390719
583A072:
593B073;
603C074<
613D075=
623E076>
633F077?
6440100@
6541101A
6642102B
6743103C
6844104D
6945105E
7046106F
7147107G
7248110H
7349111I
744A112J
754B113K
764C114L
774D115M
784E116N
794F117O
8050120P
8151121Q
8252122R
8353123S
8454124T
8555125U
8656126V
8757127W
8858130X
8959131Y
905A132Z
915B133[
925C134\
935D135]
945E136^
955F137_
9660140`
9761141a
9862142b
9963143c
10064144d
10165145e
10266146f
10367147g
10468150h
10569151i
1066A152j
1076B153k
1086C154l
1096D155m
1106E156n
1116F157o
11270160p
11371161q
11472162r
11573163s
11674164t
11775165u
11876166v
11977167w
12078170x
12179171y
1227A172z
1237B173{
1247C174|
1257D175}
1267E176~
1277F177DEL

Extended Character Set

DecHexUnicode Open in a new window...Char Zoom...Name
12880U+20ACEuro Sign
12981Undefined
13082U+201ASingle Low-9 Quotation Mark
13183U+0192ƒLatin Small Letter F With Hook
13284U+201EDouble Low-9 Quotation Mark
13385U+2026Horizontal Ellipsis
13486U+2020Dagger
13587U+2021Double Dagger
13688U+02C6ˆModifier Letter Circumflex Accent
13789U+2030Per Mille Sign
1388AU+0160ŠLatin Capital Letter S With Caron
1398BU+2039Single Left-pointing Angle Quotation Mark
1408CU+0152ŒLatin Capital Ligature Oe
1418DUndefined
1428EU+017DŽLatin Capital Letter Z With Caron
1438FUndefined
14490Undefined
14591U+2018Left Single Quotation Mark
14692U+2019Right Single Quotation Mark
14793U+201CLeft Double Quotation Mark
14894U+201DRight Double Quotation Mark
14995U+2022Bullet
15096U+2013En Dash
15197U+2014Em Dash
15298U+02DC˜Small Tilde
15399U+2122Trade Mark Sign
1549AU+0161šLatin Small Letter S With Caron
1559BU+203ASingle Right-pointing Angle Quotation Mark
1569CU+0153œLatin Small Ligature Oe
1579DUndefined
1589EU+017EžLatin Small Letter Z With Caron
1599FU+0178ŸLatin Capital Letter Y With Diaeresis
160A0U+00A0 No-break Space
161A1U+00A1¡Inverted Exclamation Mark
162A2U+00A2¢Cent Sign
163A3U+00A3£Pound Sign
164A4U+00A4¤Currency Sign
165A5U+00A5¥Yen Sign
166A6U+00A6¦Broken Bar
167A7U+00A7§Section Sign
168A8U+00A8¨Diaeresis
169A9U+00A9©Copyright Sign
170AAU+00AAªFeminine Ordinal Indicator
171ABU+00AB«Left-pointing Double Angle Quotation Mark
172ACU+00AC¬Not Sign
173ADU+00AD­Soft Hyphen
174AEU+00AE®Registered Sign
175AFU+00AF¯Macron
176B0U+00B0°Degree Sign
177B1U+00B1±Plus-minus Sign
178B2U+00B2²Superscript Two
179B3U+00B3³Superscript Three
180B4U+00B4´Acute Accent
181B5U+00B5µMicro Sign
182B6U+00B6Pilcrow Sign
183B7U+00B7·Middle Dot
184B8U+00B8¸Cedilla
185B9U+00B9¹Superscript One
186BAU+00BAºMasculine Ordinal Indicator
187BBU+00BB»Right-pointing Double Angle Quotation Mark
188BCU+00BC¼Vulgar Fraction One Quarter
189BDU+00BD½Vulgar Fraction One Half
190BEU+00BE¾Vulgar Fraction Three Quarters
191BFU+00BF¿Inverted Question Mark
192C0U+00C0ÀLatin Capital Letter A With Grave
193C1U+00C1ÁLatin Capital Letter A With Acute
194C2U+00C2ÂLatin Capital Letter A With Circumflex
195C3U+00C3ÃLatin Capital Letter A With Tilde
196C4U+00C4ÄLatin Capital Letter A With Diaeresis
197C5U+00C5ÅLatin Capital Letter A With Ring Above
198C6U+00C6ÆLatin Capital Ligature Ae
199C7U+00C7ÇLatin Capital Letter C With Cedilla
200C8U+00C8ÈLatin Capital Letter E With Grave
201C9U+00C9ÉLatin Capital Letter E With Acute
202CAU+00CAÊLatin Capital Letter E With Circumflex
203CBU+00CBËLatin Capital Letter E With Diaeresis
204CCU+00CCÌLatin Capital Letter I With Grave
205CDU+00CDÍLatin Capital Letter I With Acute
206CEU+00CEÎLatin Capital Letter I With Circumflex
207CFU+00CFÏLatin Capital Letter I With Diaeresis
208D0U+00D0ÐLatin Capital Letter Eth
209D1U+00D1ÑLatin Capital Letter N With Tilde
210D2U+00D2ÒLatin Capital Letter O With Grave
211D3U+00D3ÓLatin Capital Letter O With Acute
212D4U+00D4ÔLatin Capital Letter O With Circumflex
213D5U+00D5ÕLatin Capital Letter O With Tilde
214D6U+00D6ÖLatin Capital Letter O With Diaeresis
215D7U+00D7×Multiplication Sign
216D8U+00D8ØLatin Capital Letter O With Stroke
217D9U+00D9ÙLatin Capital Letter U With Grave
218DAU+00DAÚLatin Capital Letter U With Acute
219DBU+00DBÛLatin Capital Letter U With Circumflex
220DCU+00DCÜLatin Capital Letter U With Diaeresis
221DDU+00DDÝLatin Capital Letter Y With Acute
222DEU+00DEÞLatin Capital Letter Thorn
223DFU+00DFßLatin Small Letter Sharp S
224E0U+00E0àLatin Small Letter A With Grave
225E1U+00E1áLatin Small Letter A With Acute
226E2U+00E2âLatin Small Letter A With Circumflex
227E3U+00E3ãLatin Small Letter A With Tilde
228E4U+00E4äLatin Small Letter A With Diaeresis
229E5U+00E5åLatin Small Letter A With Ring Above
230E6U+00E6æLatin Small Ligature Ae
231E7U+00E7çLatin Small Letter C With Cedilla
232E8U+00E8èLatin Small Letter E With Grave
233E9U+00E9éLatin Small Letter E With Acute
234EAU+00EAêLatin Small Letter E With Circumflex
235EBU+00EBëLatin Small Letter E With Diaeresis
236ECU+00ECìLatin Small Letter I With Grave
237EDU+00EDíLatin Small Letter I With Acute
238EEU+00EEîLatin Small Letter I With Circumflex
239EFU+00EFïLatin Small Letter I With Diaeresis
240F0U+00F0ðLatin Small Letter Eth
241F1U+00F1ñLatin Small Letter N With Tilde
242F2U+00F2òLatin Small Letter O With Grave
243F3U+00F3óLatin Small Letter O With Acute
244F4U+00F4ôLatin Small Letter O With Circumflex
245F5U+00F5õLatin Small Letter O With Tilde
246F6U+00F6öLatin Small Letter O With Diaeresis
247F7U+00F7÷Division Sign
248F8U+00F8øLatin Small Letter O With Stroke
249F9U+00F9ùLatin Small Letter U With Grave
250FAU+00FAúLatin Small Letter U With Acute
251FBU+00FBûLatin Small Letter U With Circumflex
252FCU+00FCüLatin Small Letter U With Diaeresis
253FDU+00FDýLatin Small Letter Y With Acute
254FEU+00FEþLatin Small Letter Thorn
255FFU+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

FormulaResultExplanation
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

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)
NumberAccess RoundExcel RoundCheck Diff
0.100
0.200
0.300
0.400
0.501integer part is even number, round down
0.611
0.711
0.811
0.911
111
1.111
1.211
1.311
1.411
1.522integer part is odd number, round up
1.622
1.722
1.822
1.922
222
2.122
2.222
2.322
2.422
2.523integer part is even number, round down
2.633
2.733
2.833
2.933
333
3.133
3.233
3.333
3.433
3.544integer part is odd number, round up
3.644
3.744
3.844
3.944
444
4.144
4.244
4.344
4.444
-4.545integer part is even number, round down
-4.655
-4.755
-4.855
-4.955
-555
-5.155
-5.255
-5.355
-5.455
-5.566integer 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)
pValuethe decimal value you want to convert
digitthe number of decimal places you want to convert to

 

Outbound References

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