Joining a Text field to a Number field 1

[davewhiting]

I have been given the task of creating queries on a database where the data entry forms return an index number into a Text field, which should be joined to a reference table which uses a number field for the index. I know the simplest (and best) solution is to have the administrators change the field to in the data table to a number field but they refuse and will not budge on their decision. As a workaround, I have created a query that takes the data from the data table and converts the reference fields using:

CLng(IIf(IsNull([FieldName]) Or [FieldName]="",0,[FieldName]))

This works fine for if the field itself is Null or is a zero length text (as it appears to have both), however (As I have pointed out as the reason why it shouldn't be a text field) there is some data that has been transferred into the table with the values instead of the index number, meaning there is text which cannot be converted. Is there an easy way to test for this as I currently cannot join to this query as it has at least one record with an error?

 

[Golom]

Look at the 'IsNumeric' function. Question is, what do you want to happen if a text value that cannot be converted to a number is found?

This converts it to zero.

[blue]CLng(IIF(NOT IsNumeric([FieldName] OR Trim$([FieldName] & "") = "",0,[FieldName]))[/blue]

 

[Golom]

Sorry. Missed a paren

[blue]CLng(IIF(NOT IsNumeric([FieldName]) OR Trim$([FieldName] & "") = "",0,[FieldName]))[/blue]

 

[davewhiting]

That's perfect.

If I'm reading it correctly, I take it the Trim$([FieldName] & "") = "" qualifier covers null and zero length text strings in one go? This may also prove quite useful.

To start with, I just want to be able to produce a query showing the data we know to be correct. After that I will use Not(IsNumeric()) to produce a list of records that need to be changed. Once that is done and the data has been corrected, then I can run the first query again to hopefully show the correct data.

 

[Golom]

[blue]Trim$([FieldName] & "")[/blue] takes care of NULL, zero length and empty (i.e. spaces only) strings.

 

[PHV]

Why not simply this ?
Val([FieldName] & "")

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

 

[davewhiting]

Thanks, that is also a brilliantly simple solution. I guess I've been quite lucky in my previous experience to not have to find a solution to this problem. As I said in my original post, my first solution would have been to change the tables so they are correct to start with.

Golom's reply gives me the opportunity to return a different value depending on what data is in the field, whereas yours is a much simpler solution to return 0 if there is text. Both will be of value. I will be able to use Val() for most reporting, but returning different values depending on the type of data will be most useful for investigating erroneous values.

Thank you both.