By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,647 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

Problem with Type mismatch

P: n/a
Faz
I am trying to do a simple query between two tables which come from an
ODBC (oracle) database, so I cannot change the data type of these
values.

I am trying to do a join between a field (REFERENCE_2) which stores an
Invoice ID as a text string and the Invoice ID in the Invoices table
which of course is a number data type.

is there any way I can modify this query so that I can make this join
happen without getting the following error "Type mismatch in
expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data types.
Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Faz wrote:
I am trying to do a simple query between two tables which come from an
ODBC (oracle) database, so I cannot change the data type of these
values.

I am trying to do a join between a field (REFERENCE_2) which stores an
Invoice ID as a text string and the Invoice ID in the Invoices table
which of course is a number data type.

is there any way I can modify this query so that I can make this join
happen without getting the following error "Type mismatch in
expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data types.
Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.


Can't you create a query for each table. In one of them create an
expression like
Reference : Clng(Reference_2)

Now create a query that links the two.

Would that work?

Nov 13 '05 #2

P: n/a
af*****@hotmail.com (Faz) wrote in
news:26*************************@posting.google.co m:
I am trying to do a simple query between two tables which come
from an ODBC (oracle) database, so I cannot change the data
type of these values.

I am trying to do a join between a field (REFERENCE_2) which
stores an Invoice ID as a text string and the Invoice ID in
the Invoices table which of course is a number data type.

is there any way I can modify this query so that I can make
this join happen without getting the following error "Type
mismatch in expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data
types. Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.

What you call a limitation is actually the properly defined
behaviour. Oracle should not make the conversiuon implicitly.

As a workaround, you can add the reference_2 field to your query,
but embedded in a Visual |Basic function,

e.g. Clong([reference_2]) and instead of creating a join, make
the criteria for this field [invoices].[invoice ID]

so the sql would be SELECT <<,,fields list>> FROM table1, table2
WHERE Clong([reference_2]) = [invoices].[invoice ID];

Bob Quintal
Nov 13 '05 #3

P: n/a
I think you can modify your SQL to
...JOIN ... ON [Invoice ID] = CLong(REFERENCE_2)

You'll no longer be able to modify the design in the Access Query Grid
(although you can still use the SQL view), so I'd leave this step to as late
in the process as feasible.

HTH
- Turtle

"Faz" <af*****@hotmail.com> wrote in message
news:26*************************@posting.google.co m...
I am trying to do a simple query between two tables which come from an
ODBC (oracle) database, so I cannot change the data type of these
values.

I am trying to do a join between a field (REFERENCE_2) which stores an
Invoice ID as a text string and the Invoice ID in the Invoices table
which of course is a number data type.

is there any way I can modify this query so that I can make this join
happen without getting the following error "Type mismatch in
expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data types.
Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.

Nov 13 '05 #4

P: n/a
"MacDermott" <ma********@nospam.com> wrote in
news:Qv*****************@newsread3.news.atl.earthl ink.net:
I think you can modify your SQL to
...JOIN ... ON [Invoice ID] = CLong(REFERENCE_2)

You'll no longer be able to modify the design in the Access
Query Grid (although you can still use the SQL view), so I'd
leave this step to as late in the process as feasible.

HTH
- Turtle
Yes, that works, but, at least in Access97, any attempt to open
the query in design mode trashes the SQL.

Bob Quintal

"Faz" <af*****@hotmail.com> wrote in message
news:26*************************@posting.google.co m...
I am trying to do a simple query between two tables which
come from an ODBC (oracle) database, so I cannot change the
data type of these values.

I am trying to do a join between a field (REFERENCE_2) which
stores an Invoice ID as a text string and the Invoice ID in
the Invoices table which of course is a number data type.

is there any way I can modify this query so that I can make
this join happen without getting the following error "Type
mismatch in expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data
types. Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.



Nov 13 '05 #5

P: n/a
"Faz" <af*****@hotmail.com> wrote in message
news:26*************************@posting.google.co m...
I am trying to do a simple query between two tables which come from an
ODBC (oracle) database, so I cannot change the data type of these
values.

I am trying to do a join between a field (REFERENCE_2) which stores an
Invoice ID as a text string and the Invoice ID in the Invoices table
which of course is a number data type.

is there any way I can modify this query so that I can make this join
happen without getting the following error "Type mismatch in
expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data types.
Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.


Several people have suggested very workable solutions. My preference would
be to do the conversion on the Oracle side (TO_NUMBER). The easiest way
would probably be to create a view that would return the table intact but
REFERENCE_2 as a number. Better yet, do the join in the view. Let Oracle
do the work.
Nov 13 '05 #6

P: n/a
True, but you're generally given the option of whether to save changes, and
can decline.

"Bob Quintal" <bq******@generation.net> wrote in message
news:a5******************************@news.teranew s.com...
"MacDermott" <ma********@nospam.com> wrote in
news:Qv*****************@newsread3.news.atl.earthl ink.net:
I think you can modify your SQL to
...JOIN ... ON [Invoice ID] = CLong(REFERENCE_2)

You'll no longer be able to modify the design in the Access
Query Grid (although you can still use the SQL view), so I'd
leave this step to as late in the process as feasible.

HTH
- Turtle

Yes, that works, but, at least in Access97, any attempt to open
the query in design mode trashes the SQL.

Bob Quintal

"Faz" <af*****@hotmail.com> wrote in message
news:26*************************@posting.google.co m...
I am trying to do a simple query between two tables which
come from an ODBC (oracle) database, so I cannot change the
data type of these values.

I am trying to do a join between a field (REFERENCE_2) which
stores an Invoice ID as a text string and the Invoice ID in
the Invoices table which of course is a number data type.

is there any way I can modify this query so that I can make
this join happen without getting the following error "Type
mismatch in expression".

In Oracle we can do this query and oracle does not have this
limitation - it can query between fields of different data
types. Note I do not know VB - I can only use SQL.

Thanks for your help - I am really stuck with this one.


Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.