Need some help with conversion of text to number

[tubbers]

I'm trying to compare a field from two separate tables.

DetectedFishLog.TagCode (numeric - contains ALL existing TagCodes)
MasterData.TagCode (text - contains only the TagCodes that have been reviewed)

Both fields are in this format:
7128.07
7128.15
7130.04
7132.02

I am trying to find out which TagCode from DetectedFishLog has not been entered in the MasterData table.

Any suggestions on how to do this? I would prefer if the MasterData.TagCode field was converted to a number but everything I've tried has resulted in the end being chopped off (7128.07 turns into 7128).

 

[SkipVought]

Hi,

Convert the numeric to string CStr([TagCoged])

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[PHV]

Perhaps something like this ?
SELECT D.*
FROM DetectedFishLog D LEFT JOIN MasterData M ON Format(D.TagCode,"#.##")=Trim(M.TagCode)
WHERE M.TagCode Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

SkipVought,

I went ahead and converted to a string and got the results that I needed. Thanks for your help.

 

[tubbers]

PHV,

I find that I have to make another conversion and was looking at your solution:

SELECT D.* 
FROM DetectedFishLog D LEFT JOIN MasterData M ON Format(D.TagCode,"#.##")=Trim(M.TagCode)
WHERE M.TagCode Is Null;

I tried using this but I wasn't sure what results I was getting other than there were a lot of duplicate records (my table contains about 800 records and the query returned roughly 7,000).

Also, is this code converting the text field to a numeric data type or converting the numeric field to a text data type?

Thanks for your help.

 

[PHV]

Format(D.TagCode,"#.##") converts the numeric D.TagCode field to text.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244