Sub query nested query

[mandls]

I need to add a sub query or nested query in a module. Can someone take a look and let me know if it's even possible.

Here is what I have so far. When I try to run it it doesn t do anything.

HELP

Dim strsqr As String
strsqu = " And (((tblProgram.ProgramID) IN (Select tblProgram.ProgramID From tblClient INNER JOIN (tblClientUsage LEFT JOIN tblProgram ON tblClientUsage.ProgramID = tblProgram.ProgramID))))"

With qry
.SQL = "SELECT tblProgram.ProgramID, tblClient.ClientID,tblClient.ClientFirstname, tblClient.ClientIDHistory, tblClient.ClientLastname, tblClient.ClientSSnum, tblClient.ClientAge, tblClient.ClientDOB, tblClient.Diabled," _
& " tblClient.IsChild, tblClient.ResidesInJacksonCoun, tblClient.ClientReq4Services, tblClient.ReferredBy, tblClient.AnnualIncome, tblClient.IntakeCreated, tblClient.ClientClosed," _
& " tblClient.ClientIntakeNotes, tblClient.DeclinedServices, tblClient.WaitingList, LK_MaritalStatus.MaritalStatus, LK_Race.Race, LK_IntakeIssues.IntakeIssues, LK_ClientStatus.ClientStatus," _
& " [StaffFname] & ' ' & [StaffLname] AS Intake_Worker, LK_Ethnicity.Ethnicity, tblClient.Gender, tblClient.ClientIDHistory, tblClient.ClientOpened, LK_Service.Service, tblClient.FemaleHead, LK_Annual_Income.Annual_Income_Text, #" & Me!txtStartDate & "# as StartDate, #" & Me!txtEndDate & "# as EndDate" _
& " FROM LK_Ethnicity RIGHT JOIN (((((((LK_MaritalStatus RIGHT JOIN tblClient ON LK_MaritalStatus.MaritalStatusID = tblClient.MaritalStatusID) LEFT JOIN LK_Race ON tblClient.ClientRaceID = LK_Race.RaceID) LEFT JOIN" _
& " LK_IntakeIssues ON tblClient.IntakeIssueID = LK_IntakeIssues.IntakeIssuesID) LEFT JOIN LK_ClientStatus ON tblClient.ClientStatusID = LK_ClientStatus.ClientStatusID) LEFT JOIN tblStaff ON tblClient.IntakeWorker = tblStaff.StaffID)" _
& " LEFT JOIN LK_Service ON tblClient.Service_ID = LK_Service.ServiceID)LEFT JOIN LK_Annual_Income ON tblClient.Annual_Income_Frame = LK_Annual_Income.Annual_Income_ID) ON LK_Ethnicity.Ethnicity_ID = tblClient.Ethnicity" _
& strWhere _
& strWhere1 _
& strsqu

End With

 

[MajP]

This is a mess.
Why the with end with?
Did you define a custom "qry" class?

To write a debuggable sql string break it up in parts and print it out as you go along. You will then be able to look at it and see if it resolves the way you want it. I see several mistakes, but they will be more evident as you print it out in the debug. The good thing if it resolves correctly you can copy from the debug window and drop in the query builder to see if it is all correct.

dim strSql as string
strSql = "SELECT tblProgram.ProgramID, tblClient.ClientID,tblClient.ClientFirstname, tblClient.ClientIDHistory, tblClient.ClientLastname, tblClient.ClientSSnum, tblClient.ClientAge, tblClient.ClientDOB, tblClient.Diabled,"
debug.print stSql
'check this part looks good. Clearly it is not

strSql = strSql & " tblClient.IsChild, tblClient.ResidesInJacksonCoun, tblClient.ClientReq4Services, tblClient.ReferredBy, tblClient.AnnualIncome, tblClient.IntakeCreated, tblClient.ClientClosed,"
debug.print strSql
'see if it still looks good

 

[MajP]

If it was not clear, you simply keep going

strSql = strSql & " some more of the string"
debug.print strSql
strSql = strSql & " some more"

as you check each part comment out the debug.print, until you have a single debug.print at the end of the string. If it looks good then paste into query builder to verify.

 

[mandls]

Great thanks. I will give it a shot and see what I get.