If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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 |
#3
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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 |
#4
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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 |
#5
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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 |
#6
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
***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 |
#7
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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 |
#8
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
"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 -- |
#9
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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. -- |
#10
|
|||
|
|||
Complicated issue re 'Type mismatch in JOIN expression'
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. -- |
|
Thread Tools | |
Display Modes | |
|
|