How to Join char field to integer field ? 
Author Message
 How to Join char field to integer field ?

The SQL is like this:

SQL = "SELECT CA.*, SC.Authority_URL FROM CA_Registry CA LEFT OUTER JOIN
SC_Registry SC ON SC.TaggingEntityID = CA.SC_Code ORDER BY
CA.TaggingEntityID"

I can't change this, and it is clearly an error but CA.SC_Code was designed
in an Access table as text, and SC.TaggingEntityID is a Number (Integer).
I'd like to write in such a way that this will work regardless of Access or
SQL Server, but the task at hand uses only Access.

I've tried to Coerce the text field, used in the comparison above, such as:
ON SC.TaggingEntityID = CAST(CA.SC_Code AS INT) ORDER
ON SC.TaggingEntityID = CONVERT(INT,CA.SC_Code) ORDER
ON SC.TaggingEntityID = CInt(CA.SC_Code) ORDER
(etc)

I can't seem to find something that works.  Most everything yields a type
mismatch error..

Compounding this is the fact that CA.SC_Code has been populated with a
zero-length string (not NULL) in some cases.

Any clues ?



Mon, 21 Jun 2004 05:23:29 GMT  
 How to Join char field to integer field ?
HI
Use Cstr() function for ineteger field.
Don't build this query automatically, you have to write it manually.
In SQL Server, you can make use of Convert and Cast fucntions.

Harinatha Reddy Gorla
        Software Engineer
Smart Software Technologies,
Hyderabad, India

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 21 Jun 2004 20:39:33 GMT  
 How to Join char field to integer field ?
Harinatha -

Your reply was helpful.  Thanks for taking time to reply!

Best regards - Lee Gillie



Quote:
> HI
> Use Cstr() function for ineteger field.
> Don't build this query automatically, you have to write it manually.
> In SQL Server, you can make use of Convert and Cast fucntions.

> Harinatha Reddy Gorla
> Software Engineer
> Smart Software Technologies,
> Hyderabad, India

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Tue, 22 Jun 2004 00:50:02 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. way to find max var char field lengths of fields in SQL Server table

2. problem with char field and special ascii char

3. Reading a field and using that field to place a number in another field

4. Treat char field from SQL Server as hypertext

5. Displaying first N chars of memo field

6. field num chars - section

7. field num chars - section

8. ODBC driver to Trim Informix SE Char Fields

9. SELECT - field includes quote char?

10. Fetching Fields > 255 Chars with ODBC

11. Broken DBCS chars in Win95 TextBox fields

12. Access memo field won't accept more than 255 chars

 

 
Powered by phpBB® Forum Software