473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with Type mismatch

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
6 1841
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
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
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
"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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: LJgrnl | last post by:
I've got a type mismatch error that's driving me nutty. Variable blnNoData has the initial value False. If a recordset comes back empty (both .EOF and ..BOF are true) then blnNoData is set to...
1
by: Mark | last post by:
Hi - I tried this in VS.Net, and also in the Web Matrix code below: - but I am getting a type mismatch error. The sql statement runs perfectly from within the Access Query Designer. Can anyone...
4
by: Mike | last post by:
I am getting a type mismatch error when I do a bulk insert. ---Begin Error Msg--- Server: Msg 4864, Level 16, State 1, Line 1 Bulk insert data conversion error (type mismatch) for row 1, column...
4
by: Andy_Khosravi | last post by:
Hello, I'm having a problem with the MID function within Access 97. I have been trying to build a function to check to make sure that a field on a form does not have any spaces or dashes. This...
5
by: junglist | last post by:
Hi guys, I've been trying to implement an editable datagrid and i have been succesful up to the point where i can update my datagrid row by row. However what used to happen was that once i updated...
6
by: Howard Kaikow | last post by:
I'm doing a VB 6 project in which I am trying to protect against type mismatch errors. Is the process any different in VB .NET? Here's what I'm doing in VB 6. I have an ActiveX DLL. The...
9
by: Prasad | last post by:
HI, I am a beginner in VC++.. I am trying to write a Win32 console application in visual studio.. I am using following header files.. #include <STRING> using namespace std; #include...
21
by: Wisdo | last post by:
Hi All, char to char ** is compile error. why? if i hava a string array. yes. it's not safe and it's better to use vector<stringinstead. but my point is the language feature. char...
8
by: Net | last post by:
Hi Please help. I have a database which requires a message to come up when certain part numbers are added to it. I have solved part of this by using a conditional macro eg = 54125 and using a msg...
15
by: sara | last post by:
I have a Memo field in a table to hold notes from a conversation a social worker has had with a client (this is for a non-profit). If the user needs to update the memo field, I need to find the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.