• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5501
  • Last Modified:

MS-ACCESS Select substring

Hello EVeryone,

I'm working on a a critical report and I was forced to used MS-ACCESS over ORACLE.  Now I'm stuck with a basic select statement.  Anyway, to make a long story short, I want to select the first two digits of a column in access -- how can I do this.  For eg, the column always contains a 4 character field like 4443 (this is not a number).  All I want to do is select the first two characters, in this case '44'.  Any ideas?    Please this is an urgent call -- I've attached 200 points to this question.  Thanks for your time and help!!

ITorBust
0
itorbust
Asked:
itorbust
2 Solutions
 
rherguthCommented:
The expression you're looking for is LEFT()

An example:
SELECT Left(FirstName, 2) as left2 FROM Contacts;

To do this through the interface, you could just click new query in Access and then select SQL View from the View menu and type in your SQL.   If you then switch back to design view, you'll see how to set the expression with the GUI.  I suspect you're more comfortable just typing the SQL anyway.

-- Bob
0
 
namasi_navaretnamCommented:
Access equalent of substring is MID or MID$ function.

Select Mid$('Namasi, 2, 2)  //returns "am"
0
 
arnaudduplessyCommented:
This is for first two characters from the left:

SELECT FieldName
FROM TableName
WHERE Left([FieldName],2)="Criteria";
---------------------------------------------------
This is for mid three characters starting a the second character from the left:
SELECT FieldName
FROM TableName
WHERE  Mid([FieldName],2,3)="Criteria";
----------------------------------------------------
This is for first two characters from the right:
SELECT FieldName
FROM TableName
WHERE Right([FieldName],2)="Criteria";

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now