SUBSTR in query

[Guest]

Did not think this would be so difficult.
I just want to add a SUBSTR to capture the first 5 digits of a field.
AM I close???



SELECT
SUBSTR (MBR_GROUPNO, 1,5)
From tblMember

Or this one???

SELECT tblMember.MBR_GROUPNO
FROM tblMember
WHERE (((tblMember.MBR_GROUPNO)="need a substr"));

 

[Guest]

Way off! Try this ---
SELECT Left(MBR_GROUPNO,5) AS MBR_GROUP_5
From tblMember

 

[Marshall Barton]

Did not think this would be so difficult.
I just want to add a SUBSTR to capture the first 5 digits of a field.
AM I close???

SELECT
SUBSTR (MBR_GROUPNO, 1,5)
From tblMember

Or this one???

SELECT tblMember.MBR_GROUPNO
FROM tblMember
WHERE (((tblMember.MBR_GROUPNO)="need a substr"));

Access/Jet do not have a SubStr function.

Try using:

SELECT Mid(MBR_GROUPNO, 1, 5)
From tblMember

Or

SELECT MBR_GROUPNO
FROM tblMember
WHERE MBR_GROUPNO Like "abcde*"

 

[Guest]

The below code works!
Now, how can I specify a specific criteria, I tried adding the criteria,
also tried creating another query from this one. But the dependence is wrong.

I need to add only MBR_GROUP_5: Left(MBR_GROUPNO,5) = 70395 and 70396

SELECT Left(MBR_GROUPNO,5) AS MBR_GROUP_5, tblTrackingData.MEMBERNO
FROM ((tblMember INNER JOIN tblTrackingData ON tblMember.MEMBERNO =
tblTrackingData.MEMBERNO) INNER JOIN tblCaseLog ON tblTrackingData.ICNNO =
tblCaseLog.ICNNO) INNER JOIN tblCoordinators ON
tblTrackingData.TR_GRIEVANCECOORDINATOR = tblCoordinators.GC_RACF;

 

[John Spencer]

SELECT Left(MBR_GROUPNO,5) AS MBR_GROUP_5, tblTrackingData.MEMBERNO
FROM ((tblMember INNER JOIN tblTrackingData ON tblMember.MEMBERNO =
tblTrackingData.MEMBERNO) INNER JOIN tblCaseLog ON tblTrackingData.ICNNO =
tblCaseLog.ICNNO) INNER JOIN tblCoordinators ON
tblTrackingData.TR_GRIEVANCECOORDINATOR = tblCoordinators.GC_RACF
WHERE Left(MBR_GroupNo,5) in ("70395","70396")

OR you could possibly use

WHERE MBR_GroupNo Like "7039[56]*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

That did it - thanks

SELECT Left(MBR_GROUPNO,5) AS MBR_GROUP_5, tblTrackingData.MEMBERNO
FROM ((tblMember INNER JOIN tblTrackingData ON tblMember.MEMBERNO =
tblTrackingData.MEMBERNO) INNER JOIN tblCaseLog ON tblTrackingData.ICNNO =
tblCaseLog.ICNNO) INNER JOIN tblCoordinators ON
tblTrackingData.TR_GRIEVANCECOORDINATOR = tblCoordinators.GC_RACF
WHERE Left(MBR_GroupNo,5) in ("70395","70396")

OR you could possibly use

WHERE MBR_GroupNo Like "7039[56]*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The below code works!
Now, how can I specify a specific criteria, I tried adding the criteria,
also tried creating another query from this one. But the dependence is
wrong.

I need to add only MBR_GROUP_5: Left(MBR_GROUPNO,5) = 70395 and 70396

SELECT Left(MBR_GROUPNO,5) AS MBR_GROUP_5, tblTrackingData.MEMBERNO
FROM ((tblMember INNER JOIN tblTrackingData ON tblMember.MEMBERNO =
tblTrackingData.MEMBERNO) INNER JOIN tblCaseLog ON tblTrackingData.ICNNO =
tblCaseLog.ICNNO) INNER JOIN tblCoordinators ON
tblTrackingData.TR_GRIEVANCECOORDINATOR = tblCoordinators.GC_RACF;