Conver string into number during join

[Eric]

Hi: When i run my query it join with Mid(tpr.AccountNum,1,5) which is
like that 00000 is it possible before i join thru mid i will convert
the AccountNum into number as ti.tech is an integer in my database of
same size.

Thanks,

SELECT ti.CORP, ti.TECH, ti.TECHCONT, tj.LstVldTech, [tpr].[Status] AS
Expr1, tpr1.TicketNum, tpr1.AccountNum
FROM ([select tpr.*, Mid(tpr.AccountNum,1,5) as tprCorp from
tbl_PPVResearch AS tpr]. AS tpr1 INNER JOIN tech_id AS ti ON
tpr1.tprCorp = ti.CORP) INNER JOIN tbl_validDisputes AS tj ON ti.TECH =
tj.LstVldTech
WHERE ((([tpr].[Status])="Complete") AND
((tpr1.TicketNum)=[tj].[ticketnum]));

 

[Brendan Reynolds]

You can convert the string to an integer using the CInt function ...

CInt(Mid(tpr.AccountNum, 1, 5))

If possible, though, it would be better to change one of those columns. Two
columns which represent the same thing should have the same data type.

 

[Douglas J. Steele]

If the number is going to be larger than 32,767, use CLng instead of CInt.

(Brendan's right, though, that they should be the same type)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can convert the string to an integer using the CInt function ...

CInt(Mid(tpr.AccountNum, 1, 5))

If possible, though, it would be better to change one of those columns.
Two columns which represent the same thing should have the same data type.

--
Brendan Reynolds
Access MVP

Hi: When i run my query it join with Mid(tpr.AccountNum,1,5) which is
like that 00000 is it possible before i join thru mid i will convert
the AccountNum into number as ti.tech is an integer in my database of
same size.

Thanks,

SELECT ti.CORP, ti.TECH, ti.TECHCONT, tj.LstVldTech, [tpr].[Status] AS
Expr1, tpr1.TicketNum, tpr1.AccountNum
FROM ([select tpr.*, Mid(tpr.AccountNum,1,5) as tprCorp from
tbl_PPVResearch AS tpr]. AS tpr1 INNER JOIN tech_id AS ti ON
tpr1.tprCorp = ti.CORP) INNER JOIN tbl_validDisputes AS tj ON ti.TECH =
tj.LstVldTech
WHERE ((([tpr].[Status])="Complete") AND
((tpr1.TicketNum)=[tj].[ticketnum]));

 

[Brendan Reynolds]

Hmm, well, when Eric said 'integer' I assumed he meant 'Integer' but you're
right, of course, that may not necessarily be the case!

--
Brendan Reynolds
Access MVP

If the number is going to be larger than 32,767, use CLng instead of CInt.

(Brendan's right, though, that they should be the same type)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can convert the string to an integer using the CInt function ...

CInt(Mid(tpr.AccountNum, 1, 5))

If possible, though, it would be better to change one of those columns.
Two columns which represent the same thing should have the same data
type.

--
Brendan Reynolds
Access MVP

Hi: When i run my query it join with Mid(tpr.AccountNum,1,5) which is
like that 00000 is it possible before i join thru mid i will convert
the AccountNum into number as ti.tech is an integer in my database of
same size.

Thanks,

SELECT ti.CORP, ti.TECH, ti.TECHCONT, tj.LstVldTech, [tpr].[Status] AS
Expr1, tpr1.TicketNum, tpr1.AccountNum
FROM ([select tpr.*, Mid(tpr.AccountNum,1,5) as tprCorp from
tbl_PPVResearch AS tpr]. AS tpr1 INNER JOIN tech_id AS ti ON
tpr1.tprCorp = ti.CORP) INNER JOIN tbl_validDisputes AS tj ON ti.TECH =
tj.LstVldTech
WHERE ((([tpr].[Status])="Complete") AND
((tpr1.TicketNum)=[tj].[ticketnum]));