Complicated issue re 'Type mismatch in JOIN expression'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear all

This is a complicated issue for me so I will try and make sense!

At work we have a 'helpdesk' ODBC server which records various information including dates and times of calls. Unfortunately this is recorded on the server as an Integer so to see the date as a normal date we have a formula for excel which converts this field into a regular date field.

I have an Excel conversion spreadsheet which by using the 'Get Data' tool to pull in everyday the helpdesk date then in the second column I have the formula which calculates this field into a regular date.

So in my Access database I have two tables. A linked table to the ODBC helpdesk server AND a linked table to my Excel conversation spreadsheet. I have a query which has the two tables linked by the "helpdesk (interger) date format field"
as they are the same field but one is directly on the odbc server the second is in the excel conversion spreadsheet but that is getting the field from the odbc server as well.

Now the problem I'm getting when linking by that field is 'Type mismatch in JOIN expression' although its the same field!

Does anyone know why I am having this problem? I've formatted the fields in the same format so really cannot understand.

Hope someone can help!

Regards
 
Whenever I have had this problem it has been down to the two fields being
different types on the different tables. Even one extra digit on a text
file (i.e. one of 4 chars and the other of 5) can make a difference.

But if as you say they are exactly the same I am at a loss........



Adam said:
Dear all

This is a complicated issue for me so I will try and make sense!

At work we have a 'helpdesk' ODBC server which records various information
Click to expand...
including dates and times of calls. Unfortunately this is recorded on the
server as an Integer so to see the date as a normal date we have a formula
for excel which converts this field into a regular date field.
I have an Excel conversion spreadsheet which by using the 'Get Data' tool
Click to expand...
to pull in everyday the helpdesk date then in the second column I have the
formula which calculates this field into a regular date.
So in my Access database I have two tables. A linked table to the ODBC
Click to expand...
helpdesk server AND a linked table to my Excel conversation spreadsheet. I
have a query which has the two tables linked by the "helpdesk (interger)
date format field"
as they are the same field but one is directly on the odbc server the
Click to expand...
second is in the excel conversion spreadsheet but that is getting the field
from the odbc server as well.
Now the problem I'm getting when linking by that field is 'Type mismatch
Click to expand...
in JOIN expression' although its the same field!
Does anyone know why I am having this problem? I've formatted the fields
Click to expand...
in the same format so really cannot understand.
 
I am not sure why you have to use Excel to convert an Integer (Long?) to a
date value. An Integer/Long can be treated as a Date value, meaning the
number of days since 30/Dec/1899. For example:

?Format(Date(), "0")
38149
?Format(38149, "dd/mmm/yyyy")
11/Jun/2004

I am sure you can design an algorithm to convert the Integer/Long from your
ODBC Source to an appropriate Date value in Access.

You can also use CLng to convert a Date Value to a Long value.

--
HTH
Van T. Dinh
MVP (Access)




Adam said:
Dear all

This is a complicated issue for me so I will try and make sense!

At work we have a 'helpdesk' ODBC server which records various information
Click to expand...
including dates and times of calls. Unfortunately this is recorded on the
server as an Integer so to see the date as a normal date we have a formula
for excel which converts this field into a regular date field.
I have an Excel conversion spreadsheet which by using the 'Get Data' tool
Click to expand...
to pull in everyday the helpdesk date then in the second column I have the
formula which calculates this field into a regular date.
So in my Access database I have two tables. A linked table to the ODBC
Click to expand...
helpdesk server AND a linked table to my Excel conversation spreadsheet. I
have a query which has the two tables linked by the "helpdesk (interger)
date format field"
as they are the same field but one is directly on the odbc server the
Click to expand...
second is in the excel conversion spreadsheet but that is getting the field
from the odbc server as well.
Now the problem I'm getting when linking by that field is 'Type mismatch
Click to expand...
in JOIN expression' although its the same field!
Does anyone know why I am having this problem? I've formatted the fields
Click to expand...
in the same format so really cannot understand.
 
Hi,

I have this field calculated in Excel simply because I'm strong in Excel but weak in Access!

The formula's in excel are as follows
A1 - Integer Date
131335448
B1 - Calculation for Day
=A1-(D1*65536)-(C1*256)
C1 - Calculation for Month
=TRUNC(((A1)-(D1*65536))/256)
D1 - Calculation for Year
=TRUNC(A1/65536)

So in the excel conversation spreadsheet the Date Integer is pulled into Row A using get data then the above formulas are pulled down in columns B,C & D.


--
Adam
-----------
Windows 98 + Office Pro 97


Van T. Dinh said:
I am not sure why you have to use Excel to convert an Integer (Long?) to a
date value. An Integer/Long can be treated as a Date value, meaning the
number of days since 30/Dec/1899. For example:

?Format(Date(), "0")
38149
?Format(38149, "dd/mmm/yyyy")
11/Jun/2004

I am sure you can design an algorithm to convert the Integer/Long from your
ODBC Source to an appropriate Date value in Access.

You can also use CLng to convert a Date Value to a Long value.

--
HTH
Van T. Dinh
MVP (Access)




Adam said:
Dear all

This is a complicated issue for me so I will try and make sense!

At work we have a 'helpdesk' ODBC server which records various information
Click to expand...
including dates and times of calls. Unfortunately this is recorded on the
server as an Integer so to see the date as a normal date we have a formula
for excel which converts this field into a regular date field.
I have an Excel conversion spreadsheet which by using the 'Get Data' tool
Click to expand...
to pull in everyday the helpdesk date then in the second column I have the
formula which calculates this field into a regular date.
So in my Access database I have two tables. A linked table to the ODBC
Click to expand...
helpdesk server AND a linked table to my Excel conversation spreadsheet. I
have a query which has the two tables linked by the "helpdesk (interger)
date format field"
as they are the same field but one is directly on the odbc server the
Click to expand...
second is in the excel conversion spreadsheet but that is getting the field
from the odbc server as well.
Now the problem I'm getting when linking by that field is 'Type mismatch
Click to expand...
in JOIN expression' although its the same field!
Does anyone know why I am having this problem? I've formatted the fields
Click to expand...
in the same format so really cannot understand.
Click to expand...
 
But you're joining the tables using the A1 field, the integer, right?

I'm not sure what the problem is. All integers are not equal, of course - a
Jet integer is not the same thing as a SQL Server integer. ODBC isn't my
area of expertise, so I'm just speculating that perhaps the problem may lie
somewhere in that area - anyone with more ODBC experience care to comment on
that?

In the meantime, you could try using a conversion expression in the join.
For example, instead of "ON Table1.FieldA = Table2.FieldB" you can use
something like "ON CDate(Table1.FieldA) = CDate(Table2.FieldB)".

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Adam said:
Hi,

I have this field calculated in Excel simply because I'm strong in Excel but weak in Access!

The formula's in excel are as follows
A1 - Integer Date
131335448
B1 - Calculation for Day
=A1-(D1*65536)-(C1*256)
C1 - Calculation for Month
=TRUNC(((A1)-(D1*65536))/256)
D1 - Calculation for Year
=TRUNC(A1/65536)

So in the excel conversation spreadsheet the Date Integer is pulled into
Click to expand...
Row A using get data then the above formulas are pulled down in columns B,C
& D.
 
***From Debug Window (using your calcs)***
Raw = 131335448
YourYear = Int(Raw/2^16)
?YourYear
2004
YourMonth = Int((Raw - YourYear * 2 ^ 16) / 2 ^ 8)
?YourMonth
5
YourDay = Raw - YourYear * 2 ^ 16 - YourMonth * 2 ^ 8
?YourDay
24
***

I have not seen this date representation before. If you can explain what
the number 131335448 represents, there may be other more efficient ways to
convert this to a Date value!
--
HTH
Van T. Dinh
MVP (Access)




Adam said:
Hi,

I have this field calculated in Excel simply because I'm strong in Excel but weak in Access!

The formula's in excel are as follows
A1 - Integer Date
131335448
B1 - Calculation for Day
=A1-(D1*65536)-(C1*256)
C1 - Calculation for Month
=TRUNC(((A1)-(D1*65536))/256)
D1 - Calculation for Year
=TRUNC(A1/65536)

So in the excel conversation spreadsheet the Date Integer is pulled into
Click to expand...
Row A using get data then the above formulas are pulled down in columns B,C
& D.
 
Whoa! Wait a minute! Look at that number ...

131335448

That number will not fit in a Jet Integer field. The maximum value of a Jet
Integer is 32767. That number would have to be stored in a Long Integer
field.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
...
I have this field calculated in Excel simply because I'm strong in Excel but weak in Access!

The formula's in excel are as follows
A1 - Integer Date
131335448
B1 - Calculation for Day
=A1-(D1*65536)-(C1*256)
C1 - Calculation for Month
=TRUNC(((A1)-(D1*65536))/256)
D1 - Calculation for Year
=TRUNC(A1/65536)
Click to expand...

Use the DATESERIAL (or other) function to convert your Excel columns
B, C and D into a date on which to join e.g.

SELECT T1.* FROM
MyJetTable T1
INNER JOIN
MyExcelTable T2
ON T1.MyDateCol =
DATESERIAL(T2.MyYearCol, T2.MyMonthCol, T2.MyDayCol)

Jamie

--
 
Van/Brendan,

It's not just the value of a cell that determines the Excel data type,
its cell format is also used.

As a demo, try the following:

CREATE TABLE
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
(
MyNumCol INTEGER,
MyTextCol VARCHAR(10),
MyDateCol TIMESTAMP
)
;

INSERT INTO
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
(MyNumCol, MyTextCol, MyDateCol)
VALUES (37000, 37000, 37000)
;

SELECT
MyNumCol, MyTextCol, MyDateCol
FROM
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
;

Jamie.

--
 
Thanks Jamie. I ran your three queries, then linked the resulting Excel
range. When I open the linked table in design view, Access sees the
'MyNumber' field as Double. Which means ... um, I'm not sure what it means?
Just out of curiosity, I tried joining this table in a query with an
ODBC-linked SQL Server table, using the 'MyNumber' field in the linked Excel
table and a SQL Server int field in the SQL Server table. Somewhat to my
surprise, the query executed without error, I was unable to reproduce the
type mismatch error.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
...
I ran your three queries, then linked the resulting Excel
range. When I open the linked table in design view, Access sees the
'MyNumber' field as Double. Which means ... um, I'm not sure what it means?
Click to expand...

Jet always sees a 'numeric' Excel column as double.
Just out of curiosity, I tried joining this table in a query with an
ODBC-linked SQL Server table, using the 'MyNumber' field in the linked Excel
table and a SQL Server int field in the SQL Server table. Somewhat to my
surprise, the query executed without error
Click to expand...

I hadn't tried that before! I guess there is some coercing going on
here and probably goes some way to explaining why a Excel numeric is
always double. As a demo, the following works for both SQL Server and
Jet (MS Access):

CREATE TABLE DropMe
(
MyIntCol INTEGER,
MyFloatCol FLOAT
)
;

INSERT INTO DropMe
(MyIntCol, MyFloatCol)
VALUES (37000, 37000)
;

SELECT T1.*
FROM DropMe T1
INNER JOIN DropMe T2
ON T1.MyIntCol = T2.MyFloatCol
;

Jamie.

--
 
Back
Top