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
8 1304
Christoph,
Welcome to Bytes. I'm sure you'll find lots of help here.
There is a function called Instr. A call like this:
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
You could also use the LIKE operator.
or is it ALIKE? I always have to double-check that. Might be simpler in this case to stay away from that pitfall.
Jim
It is LIKE. I don't believe that Access has an ALIKE command.
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
Thanks, zmbd. It's surprising how hard it is to find that information online.
Jim
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!
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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>
|
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=>...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |