By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,626 Members | 1,929 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,626 IT Pros & Developers. It's quick & easy.

VBA to test the presence of "^" in a table

P: 1
Dear All,

I am new to VBA in a MS Access. Here is the problem: I have a table “Tbl_ABC” which contains a column “Code”. Sometimes, when we get the external data delivery, the product identification code contains special characters like “^”, which basically says that the information provided is outdated. Hence we can’t upload the data series, but instead we should get an alert by email that the data delivery needs to be reviewed.

I have been able to implement the VBA sending out the alert, but I am not able to figure out how the code for testing the table “Tbl_ABC” should like look like, and the way the if .. else.. is going to interact.

Any input how to get this done would be greatly appreciated. Thank you, christoph
Apr 16 '14 #1
Share this Question
Share on Google+
8 Replies

Expert 100+
P: 1,240
Welcome to Bytes. I'm sure you'll find lots of help here.

There is a function called Instr. A call like this:
Expand|Select|Wrap|Line Numbers
  1. Instr("ABC^xyz","^") 
would return a value of 4, indicating the character position of the thing you were looking for. You can look for strings of any length. So
Expand|Select|Wrap|Line Numbers
  1. if (instr([fieldname],"^")>0 then do something
is what you need.

Does that help?

Apr 16 '14 #2

Expert Mod 10K+
P: 12,430
You could also use the LIKE operator.
Expand|Select|Wrap|Line Numbers
  1. WHERE field1 LIKE '*^*'
Apr 16 '14 #3

Expert 100+
P: 1,240
or is it ALIKE? I always have to double-check that. Might be simpler in this case to stay away from that pitfall.

Apr 16 '14 #4

Seth Schrock
Expert 2.5K+
P: 2,951
It is LIKE. I don't believe that Access has an ALIKE command.
Apr 16 '14 #5

Expert Mod 5K+
P: 5,397
Access 2007/2010 can use the ANSI-92 syntax, so that ALIKE can be used; however, beaware that you also go from "*" to "%" for the wild cards.

To toggle between the Standard ACCESS-SQL and ANSI-92 SQL syntax:
Close all database objects.
Click the File-Ribbon Tab, (ACC2007:Office button), then Access Options.
Click Object Designers.
Clear/select the check box labeled "This database" (and if you wish, "Default for new databases") under SQL Server compatible syntax (ANSI 92) in the Query design section.
Click OK.

I just posted some links showing the differences between the ACCESS and ANSI - SQL syntax here:
Recordset Returns No Records; SQL Returns Records - Post#5
Apr 16 '14 #6

Expert 100+
P: 1,240
Thanks, zmbd. It's surprising how hard it is to find that information online.

Apr 16 '14 #7

Expert Mod 15k+
P: 31,769
ALIKE is available in Access and has been for a number of versions (I just tested it on 2003).

ANSI-92 is a separate but related issue. This specifies that the ANSI-92 special characters are used when LIKE is used.

Using ALIKE will force the use of ANSI-92 special characters even when the database is using ANSI-89 as standard.

Another difference is that LIKE is also supported in VBA whereas ALIKE is unfortunately not.

It's a way of making you code more portable as it works in the same way for old and new alike (Pun not intended until I realised and then thought - hey, why not, and decided to intend it after all).

Perhaps I should use it more myself as it has those benefits!
Apr 17 '14 #8

Expert Mod 15k+
P: 31,769

It's hard to tell, from what you've posted, what it is precisely that you're looking for. Is it to tell if any record has a carat (^) within the table? Is it to select each record that has?

These scenarios require different solutions. Knowing what to suggest is hard when the question doesn't say what it is you want.
Apr 17 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.