Complicated issue re 'Type mismatch in JOIN expression'

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
--
Adam
-----------
Windows 98 + Office Pro 97

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" wrote in message
...
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
--
Adam
-----------
Windows 98 + Office Pro 97

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" wrote in message
...
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
--
Adam
-----------
Windows 98 + Office Pro 97

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" wrote:

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" wrote in message
...
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
--
Adam
-----------
Windows 98 + Office Pro 97

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" wrote in message
...
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" wrote:

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" wrote in message
...
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
--
Adam
-----------
Windows 98 + Office Pro 97

***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" wrote in message
...
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

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.


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
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" wrote in message
...
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" wrote:

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" wrote in message
...
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
--
Adam
-----------
Windows 98 + Office Pro 97

"Adam" wrote ...

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)

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.


"Jamie Collins" wrote in message
om...
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.

--