VBA Coding question (1 Viewer)

Verlooy Jan · Nov 17, 2019

I have a small question

How to test in VBA if a field contains a specific text at the end?
In Excel i would use : =Right(field;5)
How to evaluate in aVBA IF statement those 5 last chars ?
I tried several options but got errors all the time.
In general how to use a Excel function in VBA code?

Thanks on beforehand

Gasman · Nov 17, 2019

Use a comma , instead of a semicolon.
Other than that it is the same

CJ_London · Nov 17, 2019

what have you tried? the VBA for right(field;5) is the same in access vba

the equivalent of the excel IF function is IIF

vba_php · Nov 17, 2019

Gasman said:
Use a comma , instead of a semicolon.
Other than that it is the same

not quite. further code is needed than just "=Right(field,5)"

Verlooy Jan said:
In general how to use a Excel function in VBA code?

generally you would write:

Code:

if right([B]yourTextStringHere[/B], 5) = [B]SpecificTextYourLookingFor[/B] then
     msgbox "String Found" '(or whatever action you want)
else
     msgbox "String Not Found" '(or whatever action you want)
end if

and Jan, try reposting your question in this forum:

https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12

this is the wrong place to ask questions.

Gasman · Nov 17, 2019

Verlooy Jan said:
I have a small question

How to test in VBA if a field contains a specific text at the end?
In Excel i would use : =Right(field;5)
How to evaluate in aVBA IF statement those 5 last chars ?
I tried several options but got errors all the time.
In general how to use a Excel function in VBA code?

Thanks on beforehand

In fact that is also incorrect, you have to use the comma, the same as Access.

CJ_London · Nov 17, 2019

you have to use the comma

in certain parts of the world you use semi colons instead of comma's - and the decimal notation can be different (dots for thousand separators, commas for dp), given the OP's name, I suspect they are from one of those parts.

isladogs · Nov 17, 2019

Welcome to AWF.
I moved the thread to Modules and VBA as its a coding question

Just wondering whether a semicolon is required for the user's location
e.g. in Italy a comma is used instead of a decimal point and a decimal point instead of a comma. e.g.12.456,50 instead of 12,456.50

In which case a semicolon replaces the the comma as a separator in functions

Gasman · Nov 17, 2019

CJ_London said:
in certain parts of the world you use semi colons instead of comma's - and the decimal notation can be different (dots for thousand separators, commas for dp), given the OP's name, I suspect they are from one of those parts.

Ah yes CJ, sorry, I forgot about that.

theDBguy · Nov 17, 2019

Verlooy Jan said:
In general how to use a Excel function in VBA code?

You would only have to do that if the Excel function doesn't exist in Access. In your case though, Access also has an equivalent Right() function.

VBA Coding question (1 Viewer)

Verlooy Jan

New member

Gasman

Enthusiastic Amateur

CJ_London

Super Moderator

vba_php

Forum Troll

Gasman

Enthusiastic Amateur

CJ_London

Super Moderator

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread