Trim String

[Gale Coleman]

Hello all

Using Access 2000.

I have a column (Casenum) in a Table (ClientsW). The casenum column holds
values such as 04-1001001 and 04E-1001002. I would like to be able to put
the casenum field into a string and trim the "E" off any records that have
the "E". I don't want to delete them, just take it off temporarily for a
query so I can tell which numbers are missing from the sequence.

Can anyone point me in the right direction? I am finding things on Google,
but nothing that is helping yet.

Thanks,

gale

 

[Dirk Goldgar]

Hello all

Using Access 2000.

I have a column (Casenum) in a Table (ClientsW). The casenum column
holds values such as 04-1001001 and 04E-1001002. I would like to be
able to put the casenum field into a string and trim the "E" off any
records that have the "E". I don't want to delete them, just take it
off temporarily for a query so I can tell which numbers are missing
from the sequence.

Can anyone point me in the right direction? I am finding things on
Google, but nothing that is helping yet.

Thanks,

gale

This may be too simplistic for your real needs, but you might start with
something like

SELECT Replace(Casenum, "E", "") As XCasenum
FROM ClientsW;

The original released version of Access 2000 doesn't support using the
Replace function in queries, but that was fixed in a later service pack.
If your version is not up to date on SPs, though, you can create this
"wrapper" function in a standard module:

'----- start of code -----
Function fReplace( _
pExpression As String, _
pFind As String, _
pReplace As String)
As String

fReplace = Replace(pExpression, pFind, pReplace)

End Function
'----- end of code -----

Then call fReplace instead of Replace in your query.

 

[Arvin Meyer]

Off the top of my head, in a new query column (all on 1 line):

TempCaseNum: IIf(Mid([CaseNum],3,1)="E", Left([CaseNum],2) &
Right([CaseNum],8),[CaseNum)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Gale Coleman]

Great! They both work wonders.

Now I need to find out how to write a query that will check for missing
numbers in the sequence!!! If you have any ideas, let me know.

Back to google!!

Gale

 

[Arvin Meyer]

This works on a sequential autonumber. It finds the next missing number:

SELECT TOP 1 DCount("LotID","tblLot","LotID<=" & [LotID]) AS Expr1
FROM tblLot
WHERE (((DCount("LotID","tblLot","LotID<=" & [LotID]))<>[LotID]))
ORDER BY DCount("LotID","tblLot","LotID<=" & [LotID]);
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Gale Coleman]

Not working for me, I get an error.

Gale

 

[Douglas J. Steele]

It would probably help if you said what the error was....

In any case, I suspect the problem is that Arvin's sample assumes a numeric
ID, while you have a text ID.

Try changing

SELECT TOP 1 DCount("LotID","tblLot","LotID<=" & [LotID]) AS Expr1
FROM tblLot
WHERE (((DCount("LotID","tblLot","LotID<=" & [LotID]))<>[LotID]))
ORDER BY DCount("LotID","tblLot","LotID<=" & [LotID]);

to

SELECT TOP 1 DCount("LotID","tblLot","LotID<='" & [LotID] & "'") AS Expr1
FROM tblLot
WHERE (((DCount("LotID","tblLot","LotID<= '" & [LotID]) & "'")<>[LotID]))
ORDER BY DCount("LotID","tblLot","LotID<= '" & [LotID] & "'");

where, exagerated for clarity, that's

SELECT TOP 1 DCount("LotID","tblLot","LotID<=' " & [LotID] & " ' ") AS Expr1
FROM tblLot
WHERE (((DCount("LotID","tblLot","LotID<= ' " & [LotID]) & " ' ")<>[LotID]))
ORDER BY DCount("LotID","tblLot","LotID<= ' " & [LotID] & " ' ");

Alternatively, you could use:

SELECT TOP 1 DCount("LotID","tblLot","LotID<=" & Chr$(34) & [LotID] &
Chr$(34)) AS Expr1
FROM tblLot
WHERE (((DCount("LotID","tblLot","LotID<=" & Chr$(34) & [LotID] &
Chr$(34)))<>[LotID]))
ORDER BY DCount("LotID","tblLot","LotID<=" & Chr$(34) & [LotID] & Chr$(34));

(obviously you need to replace LotID, tblLot, etc with the appropriate
names.

 

[Gale]

Thanks Douglas,

I got it figured out now.

Gale