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.

Faz wrote:
[color=blue]
> 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.[/color]

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?

afazeel@hotmail.com (Faz) wrote in
news:2663ec5.0405281742.22ab322c@posting.google.co m:
[color=blue]
> 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.
>[/color]
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

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" <afazeel@hotmail.com> wrote in message
news:2663ec5.0405281742.22ab322c@posting.google.co m...[color=blue]
> 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.[/color]

"MacDermott" <macdermott@nospam.com> wrote in
news:Qv_tc.3544$Yd3.1137@newsread3.news.atl.earthl ink.net:
[color=blue]
> 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
>[/color]
Yes, that works, but, at least in Access97, any attempt to open
the query in design mode trashes the SQL.

Bob Quintal

[color=blue]
> "Faz" <afazeel@hotmail.com> wrote in message
> news:2663ec5.0405281742.22ab322c@posting.google.co m...[color=green]
>> 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.[/color]
>
>
>[/color]

"Faz" <afazeel@hotmail.com> wrote in message
news:2663ec5.0405281742.22ab322c@posting.google.co m...[color=blue]
> 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.[/color]

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.

True, but you're generally given the option of whether to save changes, and
can decline.

"Bob Quintal" <bquintal@generation.net> wrote in message
news:a52488844d4767bfa9ad86f19963d864@news.teranew s.com...[color=blue]
> "MacDermott" <macdermott@nospam.com> wrote in
> news:Qv_tc.3544$Yd3.1137@newsread3.news.atl.earthl ink.net:
>[color=green]
> > 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
> >[/color]
> Yes, that works, but, at least in Access97, any attempt to open
> the query in design mode trashes the SQL.
>
> Bob Quintal
>
>[color=green]
> > "Faz" <afazeel@hotmail.com> wrote in message
> > news:2663ec5.0405281742.22ab322c@posting.google.co m...[color=darkred]
> >> 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.[/color]
> >
> >
> >[/color]
>[/color]