473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
8 1304
jimatqsi
1,271 Expert 1GB
Christoph,
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?

Jim
Apr 16 '14 #2
Rabbit
12,516 Expert Mod 8TB
You could also use the LIKE operator.
Expand|Select|Wrap|Line Numbers
  1. WHERE field1 LIKE '*^*'
Apr 16 '14 #3
jimatqsi
1,271 Expert 1GB
or is it ALIKE? I always have to double-check that. Might be simpler in this case to stay away from that pitfall.

Jim
Apr 16 '14 #4
Seth Schrock
2,965 Expert 2GB
It is LIKE. I don't believe that Access has an ALIKE command.
Apr 16 '14 #5
zmbd
5,501 Expert Mod 4TB
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
jimatqsi
1,271 Expert 1GB
Thanks, zmbd. It's surprising how hard it is to find that information online.

Jim
Apr 16 '14 #7
NeoPa
32,556 Expert Mod 16PB
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
NeoPa
32,556 Expert Mod 16PB
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.
Apr 17 '14 #9

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

Similar topics

3
by: user | last post by:
I created a window with var mywindow = window.open( "some/local.html", "mywindow", "width=..., "height=...., ..." ); The window shows up. It contains a table. <table> <tr> <td>begin</td>
3
by: Claudio Lapidus | last post by:
Hello Now perhaps this is a bit dumb, but... I just populated a new table via \copy. After that, I realize that perhaps is a good thing to have a row identifier in it, so I try clapidus=>...
1
by: gaozj111 | last post by:
Hello: Now i want to rename the table schema for example: there is one table ,whose name is A.TEST,the A is the schema name, now i want to rename the A TO B. I want to get the B.TEST table.
1
by: Michael Charney | last post by:
Using VBA how can I test to see if a table has been created? Situation: I create temp tables that are used by users and then deleted when they log off. How can I tell if a table is still in use...
4
by: Martin Eyles | last post by:
Hi! I am trying to make several 1x3 tables on a webpage, where all three cells have black backgrounds and white text. However, I want the space around each of the cells, and the whole box to be...
5
by: Christian Traber | last post by:
Hi! just playing with tablespaces... - moved a existing table to a new tablespace - tried to move it back to default tablespace (ALTER TABLE accounts SET TABLESPACE pg_default;) Got the...
4
by: Ying Lu | last post by:
Hello, I have a table named "USER" under MySQL database. When I am trying to move tables from MySQL to PostgreSQL, I found that I could not create a table namely "USER". I guess "USER" is a key...
1
by: Timothy Perrigo | last post by:
(PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6) I'm working on a function which creates and populates a temporary table, then returns the number of records it has inserted. I'm getting an error,...
4
by: smanojgroup | last post by:
I have a view defined as select * on base table. When base table is altered to add new column, new column does not appear when selected from view. Here is what I did to test. db2 "create table...
3
by: moltendorf | last post by:
I copied the files from my "test" database on my old server (MySQL was not running) to my new server ("./mysql/data/test" folder), and after starting the server, SHOW TABLES; shows all of the tables...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.