Query to Strip Leading Zero from Text String. 2

[hefly]

I have a two digit number stored as text. Some numbers have a leading zero.

01
11
23
02


When there is a leading zero (number is stored as text), how can I use an access query to strip the zero from the text sring?

Thank you.

Hefly

 

[SkipVought]

hi,

Convert to a number CInt()

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hefly]

How do you do that in a query?

I am trying to link to another table where the number stored as text (without the leading zero).

 

[Remou]

You can use SQL view, it is not possible to create such a query in design view:

[tt]SELECT * FROM test1
INNER JOIN test2
ON Val(test1.ID)=Val(test2.ID)[/tt]

http://lessthandot.com

 

[hefly]

Ooops.

I get a "data type mismatch" test1.id is text and test2.id is numerical.

Any other suggestions?

Thanks.

Hefly

 

[MajP]

The problem has nothing to do with the fact that one field is numeric and the other is text. The problem is that you have null values in one of those fields.
val(Null) throws an error.

Val(NZ(test1.ID,0))=Val(NZ(test2.ID,0))

 

[Remou]

You can use CInt, as suggested by SkipVought, it will also deal with Nulls, you need it on both sides, unless you know that the numeric side does not have any nulls.

[tt]SELECT * FROM test1
INNER JOIN test2
ON CInt(test1.ID)=CInt(test2.ID)[/tt]

http://lessthandot.com

 

[hefly]

Remou:

Thank you very much. I tried the solution using CInt() but I get another error: "Invalid use of Null."

Hefly

 

[MajP]

My solution as posted works. Not sure what Remou is thinking, but he is wrong about Cint converting a null. From the helpfile to show this

Each function coerces an expression to a specific data type.

Syntax
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
The required expression argument is any string expression or numeric expression.

CINT fails on a Null.

 

[hefly]

I try the following query and I get "data type mismatch."

SELECT test1.Section, test2.Sec
FROM test1 INNER JOIN test2 ON test1.Section = test2.Sec
WHERE ((CInt(test1.Section) Is Not Null) AND (CInt(test2.Sec) Is Not Null));

And the following expression returns the error "Invalid use of Null"

SELECT test1.Section, test2.Sec
FROM test1 INNER JOIN CInt(test2 ON test1.Section) = CInt(test2.Sec)
WHERE ((CInt(test1.Section) Is Not Null) AND (CInt(test2.Sec) Is Not Null));

 

[MajP]

What do I have to do? I already explained this in the post, that is going to fail. Why will you not use the solution I provided? It works as I have stated.
val(NZ(test1.ID,0))=Val(NZ(test2.ID,0))
or
CINT(NZ(test1.ID,0))= CINT(NZ(test2.ID,0))

Your other solution is to first build a query/ies that removes the nulls and then join them.

 

[hefly]

Skip & Remou:

Thanks for your help... I found something that worked!

SELECT CInt(test1.Section) AS Expr1, CInt(test2.sec) AS Expr2
FROM test1 INNER JOIN test2 ON CInt(test1.Section)=CInt(test2.sec)
WHERE (((test1.Section) Is Not Null));

 

[hefly]

MajP

Thanks for providing a solution...and an explanation. I got it now. I applied your solution improperly at first and got an error.


Hefly

 

[PHV]

Why not simply this ?

SELECT test1.Section, test2.sec
FROM test1 INNER JOIN test2 ON Val(test1.Section)=Val(test2.sec)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry, disregard my previous post.