Outer Join Syntax: SQL Server vs. Access 2000

[K3V]

I'm modifying several queries in my VB 6.0 project to use an Access 2000 database instead of the current SQL Server database. (it's a backward move, I know… but it has to do with the end user's knowledge of Access vs. SQL Server, and the need for quick and unpredictably ad-hoc reports).

In the following VB recordset puplated from an Access DB, some Patient_ID's don't exist in the table X_INSUSPAN. The intention here is to use an outer join to display the Patient's information regardless of whether all the data is present in the database.

I have no knowledge of Access ...uh... "SQL", but clearly, my JOIN syntax is not working, because on the
"IfrsSelectedCaller.Fields(11) = 8 Then..."
statement, I get an EOF error.

Could someone help me see the error of my ways?
Maybe there's a much better way to do this, I don't know. Any help is appreciated by many.

Set rsSelectedCaller = objCaller.Execute("SELECT IIF(X_PATIENT.PATIENT_ID IS NULL,0,X_PATIENT.PATIENT_ID), IIF(X_PATIENT.LAST_NAME IS NULL,'No Data',X_PATIENT.LAST_NAME), iif(X_PATIENT.FIRST_NAME is null,'No Data',X_PATIENT.FIRST_NAME) " & _

", iif(X_DEMOSPAN.ADDRESS1 is null,'No Data',X_DEMOSPAN.ADDRESS1), iif(X_DEMOSPAN.ADDRESS2 is null,'No Data',X_DEMOSPAN.ADDRESS2) " & _

", iif(X_DEMOSPAN.CITY is null,'No Data',X_DEMOSPAN.CITY ), iif(X_DEMOSPAN.STATE is null,'ND',X_DEMOSPAN.STATE), iif(X_DEMOSPAN.ZIP_CODE,'00000-0000',X_DEMOSPAN.ZIP_CODE) " & _

", iif(X_DEMOSPAN.HOME_PHONE is null,'No Data',X_DEMOSPAN.HOME_PHONE), iif(X_DEMOSPAN.WORK_PHONE is null,'No Data',X_DEMOSPAN.WORK_PHONE) " & _

", iif(X_DEMOSPAN.OTH_PHONE is null,'No Data',X_DEMOSPAN.OTH_PHONE), iif(X_INSUSPAN.INSU_ID is null,0,X_INSUSPAN.INSU_ID) " & _

"FROM ((X_PATIENT left JOIN X_DEMOSPAN ON X_DEMOSPAN.PATIENT_ID = X_PATIENT.PATIENT_ID) left JOIN X_INSUSPAN ON ltrim(rtrim(X_INSUSPAN.PATIENT_ID)) = ltrim(rtrim(X_PATIENT.PATIENT_ID))) " & _

"where ltrim(rtrim(x_PATIENT.patient_id)) = " & CInt(Trim(cMedID)) & " order by x_PATIENT.first_name;")


'**note** rsSelectedCaller.Fields(11) is X_INSUSPAN.INSU_ID

If rsSelectedCaller.Fields(11) = 8 Then '<=== error is here
frmNEW.Label9.Caption = "County Funds"
ElseIf rsSelectedCaller.Fields(11) = 7 Then
frmNEW.Label9.Caption = "Medicaid"
ElseIf rsSelectedCaller.Fields(11) = 6 Then
frmNEW.Label9.Caption = "IPRS Funds"
Else
frmNEW.Label9.Caption = "No Data"
End If


Thanks in advance!
K3V

 

[cmmrfrds]

The fields collection is zero based and it doesn't look like there are 12 columns in the select list.

 

[K3V]

12 items are being selected, however I'm not assigning the first item to any variable or control... (at the moment I can't remember why I added item 1, "X_PATIENT.PATIENT_ID", probably for testing of some sort... but that's the 0 index, and is not in use)

Set rsSelectedCaller = objCaller.Execute("SELECT
1) IIF(X_PATIENT.PATIENT_ID IS NULL,0,X_PATIENT.PATIENT_ID),
2) IIF(X_PATIENT.LAST_NAME IS NULL,'No Data',X_PATIENT.LAST_NAME),
3) iif(X_PATIENT.FIRST_NAME is null,'No Data',X_PATIENT.FIRST_NAME) " & _
4) ", iif(X_DEMOSPAN.ADDRESS1 is null,'No Data',X_DEMOSPAN.ADDRESS1),
5) iif(X_DEMOSPAN.ADDRESS2 is null,'No Data',X_DEMOSPAN.ADDRESS2) " & _
6) ", iif(X_DEMOSPAN.CITY is null,'No Data',X_DEMOSPAN.CITY ),
7) iif(X_DEMOSPAN.STATE is null,'ND',X_DEMOSPAN.STATE),
8) iif(X_DEMOSPAN.ZIP_CODE,'00000-0000',X_DEMOSPAN.ZIP_CODE) " & _
9) ", iif(X_DEMOSPAN.HOME_PHONE is null,'No Data',X_DEMOSPAN.HOME_PHONE),
10) iif(X_DEMOSPAN.WORK_PHONE is null,'No Data',X_DEMOSPAN.WORK_PHONE) " & _
11) ", iif(X_DEMOSPAN.OTH_PHONE is null,'No Data',X_DEMOSPAN.OTH_PHONE),
12) iif(X_INSUSPAN.INSU_ID is null,0,X_INSUSPAN.INSU_ID) " & _

"FROM ((X_PATIENT left JOIN X_DEMOSPAN ON X_DEMOSPAN.PATIENT_ID = X_PATIENT.PATIENT_ID) left JOIN X_INSUSPAN ON ltrim(rtrim(X_INSUSPAN.PATIENT_ID)) = ltrim(rtrim(X_PATIENT.PATIENT_ID))) " & _
"where ltrim(rtrim(x_PATIENT.patient_id)) = " & CInt(Trim(cMedID)) & " order by x_PATIENT.first_name;")

Interestingly, on my home machine the same query produces an "overflow" error, rather than EOF... go figure.

Anyway, I'm still at a loss as to the appropriate syntax for Access outer joins on multiple tables.

Ideas?

 

[cmmrfrds]

Are you using the DAO or ADO library? In ADO the Execute method on the Recordset Object is not typically used to return a resultset instead it is used for Update, Delete, Insert. In fact, I don't think it will return a recordset, at least with the field list you expect, maybe informational data on the success of the update. If ADO, use the Open method of the recordset object to return results.

 

[K3V]

ADO... and yes, it does return a recordset. There are several queries in my app, all using ADO, and all working perfectly with the exception of those using multiple JOINs. An outer join, if I understand correctly, should return all rows from the initial table, regardless of the existance of matching records in the joined table... however, the third table in my select statement, (the 2nd Join), somehow gives me an EOF error. I'm certain that it's a simple matter of Access syntax. Can ANYONE correct my syntax? Is this a parenthesis issue? Incorrect nesting?

getting frustrated...

Thanks again,
K3V

 

[cmmrfrds]

I think the parens are okay.

where ltrim(rtrim(x_PATIENT.patient_id)) = " & CInt(Trim(cMedID))

Since the trim function is for character fields, why are you changing cMedID to an integer. An Access integer will hold up to 32xxx, so it is rather a small number.

 

[K3V]

the trim function is used for nothing more than to make sure there are no leading or trailing spaces... to make the values match, (if they do indeed match)... for example,

'54452'
is not the same as
' 54452 '
therefore, the "match" isn't really a match.

In any case, those weren't the parens I was referring to. My concern is with the nesting of multiple joins, as in the following:

"... FROM ((X_PATIENT left JOIN X_DEMOSPAN ON X_DEMOSPAN.PATIENT_ID = X_PATIENT.PATIENT_ID) left JOIN X_INSUSPAN ON X_INSUSPAN.PATIENT_ID = X_PATIENT.PATIENT_ID) where... "

(trim functions removed for clarity)

K3V

 

[cmmrfrds]

I was referring to the parens on the join.
I know the purpose of trim.

'54452'
If this is a real value in the table, then it is too large for an Access Integer. It will never match Cint('54452') i.e. a 2 byte number. Try Clng('54452') which will hold a 4 byte number, if you really want to make an integer value.

 

[K3V]

forgive me if i sounded condesending... I do appreciate your help. I'll give CLng(... a try, I hadn't considered the size of the type.

Thanks again
K3V

 

[PHV]

And this ?
"where CLng(Trim(x_PATIENT.patient_id)) = " & CLng(Trim(cMedID))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244