I need to list all records in the Asset table which have a corresponding record in the ITEqpt table.
The problem is that the key fields in the two tables are recorded slightly differently - the ReferenceData in the Assets table typically contains a substring of the EquipmentID value in the ITEqpt table.
How do I make a report with a join like
ReferenceData = substring in EquipmentID?
> I need to list all records in the Asset table which have a corresponding > record in the ITEqpt table.
> The problem is that the key fields in the two tables are recorded slightly > differently - the ReferenceData in the Assets table typically contains a > substring of the EquipmentID value in the ITEqpt table.
> How do I make a report with a join like
> ReferenceData = substring in EquipmentID?
> -- > Sriram
Hello,
If the lenght always is 5, then you can use this:
Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))
> I need to list all records in the Asset table which have a corresponding
> record in the ITEqpt table.
> The problem is that the key fields in the two tables are recorded slightly
> differently - the ReferenceData in the Assets table typically contains a
> substring of the EquipmentID value in the ITEqpt table.
> How do I make a report with a join like
> ReferenceData = substring in EquipmentID?
> -- > Sriram
Hello,
If the lenght always is 5, then you can use this:
Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))
Assuming that the first numeric character is never followed by non-numeric characters, the Val function will work.
On clng(ReferenceData) = Val(EquipmentID)
If the assumption is incorrect, then you can use 26 nested Replace functions:
On Replace(Replace(Replace(EquipmentID,"A",""),"B",""),"C","") etc.
or write a function that loops through each character in the EquipmentID, appending it to a variable if numeric.
>> I need to list all records in the Asset table which have a
>> corresponding record in the ITEqpt table.
>> The problem is that the key fields in the two tables are recorded
>> slightly differently - the ReferenceData in the Assets table
>> typically contains a substring of the EquipmentID value in the
>> ITEqpt table. How do I make a report with a join like
>> ReferenceData = substring in EquipmentID?
>> --
>> Sriram
> Hello,
> If the lenght always is 5, then you can use this:
> Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))
No, it isn't any easy solution, and the VAL function can't be used because the Val function stops reading the string, at the first character it can't recognize as part of a number.
>> I need to list all records in the Asset table which have a corresponding
>> record in the ITEqpt table.
>> The problem is that the key fields in the two tables are recorded slightly
>> differently - the ReferenceData in the Assets table typically contains a
>> substring of the EquipmentID value in the ITEqpt table.
>> How do I make a report with a join like
>> ReferenceData = substring in EquipmentID?
>> -- >> Sriram
> Hello,
> If the lenght always is 5, then you can use this:
> Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))
