Type Mismatch in Expression

  • Thread starter Thread starter Dennis
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...
 
Back
Top