Type Mismatch in Expression

D

Dennis

I have two tables, one generates a sequential auto number and the other has
this number as a text field. I am trying to link the number/text fields of
the two tables.

Can I change the query some way so that it recognizes the two as the same or
change the table with text to a number and still keep the format. Followingis
the Query SQL and the table field data from each:

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW] ON [TBL CSPL
NUMBER TRACKING].CSPL_NO = [TBL IQREVIEW].CSPL_NO;

TBL NMP CSPL NUMBERTRACKING TABLE/CSPL_NO table info:
Data Type: AUTONUMBER; Field Size: Long Integer; New Values: Increment;
Format: “N-“00000.

TBL IQREVIEW DOWNLOAD AP-913 CRITICAL COMPONENT TABLE/CSPL_NO table info:
Data Type: Text; Field Size: 10; Input Mask: >â€N-“#####; Allow Zero Length:
Yes; Indexed: No; Unicode Compression: Yes; IME Mode: No Control; IME
Sentence Mode: None.

any help would be appreciated!

Thank you,

Dennis
 
J

John Spencer

You can try

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW]
ON [TBL CSPL NUMBER TRACKING].CSPL_NO = VAL([TBL IQREVIEW].CSPL_NO);

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dennis

Still received the same error message, also when I go to the Design veiw I
get the following: Microsoft Office Access can"t represent the join
expression (my expression)... in Design view.

John Spencer said:
You can try

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW]
ON [TBL CSPL NUMBER TRACKING].CSPL_NO = VAL([TBL IQREVIEW].CSPL_NO);

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have two tables, one generates a sequential auto number and the other has
this number as a text field. I am trying to link the number/text fields of
the two tables.

Can I change the query some way so that it recognizes the two as the same or
change the table with text to a number and still keep the format. Followingis
the Query SQL and the table field data from each:

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW] ON [TBL CSPL
NUMBER TRACKING].CSPL_NO = [TBL IQREVIEW].CSPL_NO;

TBL NMP CSPL NUMBERTRACKING TABLE/CSPL_NO table info:
Data Type: AUTONUMBER; Field Size: Long Integer; New Values: Increment;
Format: “N-“00000.

TBL IQREVIEW DOWNLOAD AP-913 CRITICAL COMPONENT TABLE/CSPL_NO table info:
Data Type: Text; Field Size: 10; Input Mask: >â€N-“#####; Allow Zero Length:
Yes; Indexed: No; Unicode Compression: Yes; IME Mode: No Control; IME
Sentence Mode: None.

any help would be appreciated!

Thank you,

Dennis
Click to expand...
Click to expand...
 
J

John Spencer

Well, that is true. The only joins that the design view can present are those
that are exactly TableName.FieldName = TableName2.FieldName2.

Val might be causing an error if CSPL_NO cannot be converted to a number - for
instance if it is null.

You can try a cartesian query.

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO
, [TBL CSPL NUMBER TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] , [TBL IQREVIEW]
WHERE [TBL CSPL NUMBER TRACKING].CSPL_NO =
IIF(IsNumeric([TBL IQREVIEW].CSPL_NO),Val([TBL IQREVIEW].CSPL_NO),Null)

You might even be able to do that this way

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO
, [TBL CSPL NUMBER TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW]
ON [TBL CSPL NUMBER TRACKING].CSPL_NO =
IIF(IsNumeric([TBL IQREVIEW].CSPL_NO),Val([TBL IQREVIEW].CSPL_NO),Null)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Still received the same error message, also when I go to the Design veiw I
get the following: Microsoft Office Access can"t represent the join
expression (my expression)... in Design view.

John Spencer said:
You can try

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW]
ON [TBL CSPL NUMBER TRACKING].CSPL_NO = VAL([TBL IQREVIEW].CSPL_NO);

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have two tables, one generates a sequential auto number and the other has
this number as a text field. I am trying to link the number/text fields of
the two tables.

Can I change the query some way so that it recognizes the two as the same or
change the table with text to a number and still keep the format. Followingis
the Query SQL and the table field data from each:

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW] ON [TBL CSPL
NUMBER TRACKING].CSPL_NO = [TBL IQREVIEW].CSPL_NO;

TBL NMP CSPL NUMBERTRACKING TABLE/CSPL_NO table info:
Data Type: AUTONUMBER; Field Size: Long Integer; New Values: Increment;
Format: “N-“00000.

TBL IQREVIEW DOWNLOAD AP-913 CRITICAL COMPONENT TABLE/CSPL_NO table info:
Data Type: Text; Field Size: 10; Input Mask: >â€N-“#####; Allow Zero Length:
Yes; Indexed: No; Unicode Compression: Yes; IME Mode: No Control; IME
Sentence Mode: None.

any help would be appreciated!

Thank you,

Dennis
Click to expand...
Click to expand...
Click to expand...
 
D

Dennis

First one worked perfect, I will try the second out of curiousity!
Your Good!
Thank You!

John Spencer said:
Well, that is true. The only joins that the design view can present are those
that are exactly TableName.FieldName = TableName2.FieldName2.

Val might be causing an error if CSPL_NO cannot be converted to a number - for
instance if it is null.

You can try a cartesian query.

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO
, [TBL CSPL NUMBER TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] , [TBL IQREVIEW]
WHERE [TBL CSPL NUMBER TRACKING].CSPL_NO =
IIF(IsNumeric([TBL IQREVIEW].CSPL_NO),Val([TBL IQREVIEW].CSPL_NO),Null)

You might even be able to do that this way

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO
, [TBL CSPL NUMBER TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW]
ON [TBL CSPL NUMBER TRACKING].CSPL_NO =
IIF(IsNumeric([TBL IQREVIEW].CSPL_NO),Val([TBL IQREVIEW].CSPL_NO),Null)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Still received the same error message, also when I go to the Design veiw I
get the following: Microsoft Office Access can"t represent the join
expression (my expression)... in Design view.

John Spencer said:
You can try

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW]
ON [TBL CSPL NUMBER TRACKING].CSPL_NO = VAL([TBL IQREVIEW].CSPL_NO);

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dennis wrote:
I have two tables, one generates a sequential auto number and the other has
this number as a text field. I am trying to link the number/text fields of
the two tables.

Can I change the query some way so that it recognizes the two as the same or
change the table with text to a number and still keep the format. Followingis
the Query SQL and the table field data from each:

SELECT DISTINCT [TBL IQREVIEW].CSPL_NO, [TBL CSPL NUMBER
TRACKING].CSPL_PKG_STATUS
FROM [TBL CSPL NUMBER TRACKING] INNER JOIN [TBL IQREVIEW] ON [TBL CSPL
NUMBER TRACKING].CSPL_NO = [TBL IQREVIEW].CSPL_NO;

TBL NMP CSPL NUMBERTRACKING TABLE/CSPL_NO table info:
Data Type: AUTONUMBER; Field Size: Long Integer; New Values: Increment;
Format: “N-“00000.

TBL IQREVIEW DOWNLOAD AP-913 CRITICAL COMPONENT TABLE/CSPL_NO table info:
Data Type: Text; Field Size: 10; Input Mask: >â€N-“#####; Allow Zero Length:
Yes; Indexed: No; Unicode Compression: Yes; IME Mode: No Control; IME
Sentence Mode: None.

any help would be appreciated!

Thank you,

Dennis
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trim leading zeroes / change data type 2
How to Populate a Table based on Entries in Another 2
"data type mismatch" 6
missing records in form 1
Number to Number "Data Type Mismatch in Expression" in Join 1
UNION QUERY NOT WORKING 3
linked tables query 9
Data type mismatch 6

Top