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 1307
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: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |