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]));

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


"Eric" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]));
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"Brendan Reynolds" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> "Eric" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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]));
>>
>
>

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:O$(E-Mail Removed)...
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Brendan Reynolds" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>> "Eric" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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]));
>>>
>>
>>
>
>