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

[IRISOS]

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

[jimatqsi]

Christoph,
Welcome to Bytes. I'm sure you'll find lots of help here.

There is a function called Instr. A call like this:

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

if (instr([fieldname],"^")>0 then do something

is what you need.

Does that help?

Jim

[Rabbit]

You could also use the LIKE operator.

WHERE field1 LIKE '*^*'

[jimatqsi]

or is it ALIKE? I always have to double-check that. Might be simpler in this case to stay away from that pitfall.

Jim

[Seth Schrock]

It is LIKE. I don't believe that Access has an ALIKE command.

[zmbd]

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

[jimatqsi]

Thanks, zmbd. It's surprising how hard it is to find that information online.

Jim

[NeoPa]

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!

[NeoPa]

Christoph,

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.