Select Query - Data type

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

Is there a way in SQL or VBA to change the data type of a
link field prior to running any time of query?

Someone thought this might be the best way to handle my
issue. One field has a data type of text and the other is
a number data type. I keep receiving the mismatch
expressions error and I have to make a copy of the query
change the data types and save before the unmatched
results will appear. The other problem lies because I
want this unmatched query to be a standard report updating
each time the query is updated. Any suggestions on how to
go into SQL/VBA and change the data types of a linked
table? I'm trying to normalize my linked tables.
 
You can't change the type of a field in a linked table.... Well, perhaps you
can, but you should not.

What you probably need to do is to convert the data type in the join clause of
the query statement. For Example:

SELECT *
FROM TableA INNER JOIN TableB
ON TableA.NumberField = Val(TableB.TextField)
WHERE ...
ORDER BY ...

NOTE THAT you cannot do this in the query design grid, but MUST switch to the
SQL (text) view to set up this join.

You can use Val, Str, CStr, DateValue, TimeValue, CLng, CDate, CDbl, etc to
convert field types. HOWEVER, you may still get errors if either of your join
fields ever contains a NULL.
 
Back
Top