convert data type

[Dartagnan]

I am getting a "type mismatch in expression" when I try to run a query. I get the error because one table has the Data Type, for loan number, as number and the other table has the Data Type, for loan number, as text.

Is there a way I can convert the data type while I am building my query?

 

[Parra]

I would try to add a calculated field in the query.

Loan Number (data type: text)
In the calculated field I would just add this field, but in the properties I would change it to number. And then base your query off this one and not the other.

I don't know if this would work but this is what I would try.

 

[Russell Hauf]

The easiest way is to add an intermediate query. So say the table with Loan Number formatted as text is called tblLoanText. Make a query (say qryLoanText) that brings in all fields EXCEPT for Loan Number. For Loan Number (say it is called LoanNum), use a blank column and do something like this:

LoanNum2: Val(LoanNum)

Then join your numeric loan number table to the query you just created.

HTH,

Russell

 

[Dartagnan]

Convert Data Type

This is a little out of my league. Is there a command like CSTR or CLNG that would do the trick?

 

[Russell Hauf]

It really is not out of your league. If you can create a query where you join 2 tables, then you can do this.

For the query I was talking about, just select all the fields in the table that you want to use (all of them, if you want them all, but don't choose *). Then just change the Loan Number field (again, for example purposes I'll say it is named LoanNum).

So maybe your query looks like this before changes:

</p>
The only change you'd need to make would be to the LoanNum field, something like this:

</p>
Save the query, and instead of joining the table to the other table, join the table to the query you just saved (which will have LoanNum - renamed as Loan2 - as numeric).

HTH,

Russell