Solved

Criteria Expressions

Posted on 2010-08-16
6
276 Views
Last Modified: 2012-06-27
I have a list with two types of data.

1st Type: NO ALLOC. AVAIL. CONSTR.# 01068CRUH
2nd Type: NO ALLOC. AVAIL. CONSTR.# 00257

I want to write a expression that only gives me the 2nd type without the Letters on the end.

I tried
Between "NO ALLOC. AVAIL. CONSTR.# 00000" And "NO ALLOC. AVAIL. CONSTR.# 99999"

Did not work.  It still gave me the ones with the letters on the end.  Any suggestions?
0
Comment
Question by:mato01
  • 3
  • 2
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 33448996
Please show exact syntax of  query using:
 
Between "NO ALLOC. AVAIL. CONSTR.# 00000" And "NO ALLOC. AVAIL. CONSTR.# 99999"

Reason I ask, is because that does not appear to be a valid VBA expression to me.
0
 
LVL 7

Expert Comment

by:shaydie
ID: 33449028
You could just test to see if the last char in the string is a number...

SELECT Table.tItem, IsNumeric(Right([tItem],1)) AS Item
FROM Table
WHERE (((IsNumeric(Right([tItem],1)))=True));
0
 

Author Comment

by:mato01
ID: 33449187
Here is the syntax fo query:

UPDATE [TBL ALL REPORTS] SET [TBL ALL REPORTS].COMMENTS = "Order will be Approved"
WHERE ((([TBL ALL REPORTS].REPORT)="CONSTRAINT") AND (([TBL ALL REPORTS].[ERROR MESSAGE]) Between "NO ALLOC. AVAIL. CONSTR.# 00000" And "NO ALLOC. AVAIL. CONSTR.# 99999"));
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 7

Accepted Solution

by:
shaydie earned 250 total points
ID: 33449422
UPDATE [TBL ALL REPORTS] SET [TBL ALL REPORTS].COMMENTS = "Order will be Approved"
WHERE ((([TBL ALL REPORTS].REPORT)="CONSTRAINT") AND ((IsNumeric(Right([ERROR MESSAGE],1)))=True));
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33449502
UPDATE [TBL ALL REPORTS] SET [TBL ALL REPORTS].COMMENTS = "Order will be Approved"
WHERE [TBL ALL REPORTS].REPORT="CONSTRAINT" AND
ISNUMERIC(right([TBL ALL REPORTS].[ERROR MESSAGE],1);
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33449523
Better late than never :>)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now