report with #ERROR for Begin and End date.

[cthai]

I have a report that show how many cases a person have close on a range of date – I have a Begin and End date where the user can enter a date that they would like to run this report. However, if a person that was selected did not close any cases that day the Begin and End date give me an #ERROR and the person name which I call “Case Assigned to” is blank, is there a way I can have the person name show up even when they did not close any cases from the date function? And have the date show up rather then #error?

thank you!

 

[PHV]

Have a look at OUTER JOIN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cthai]

Can access do OUTER JOIN? it looks like i can do, either RIGHT, LEFT or INNER JOIN only.

 

[cthai]

FULL OUTER JOIN --

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cthai]

I create this in the design query - but here is what the SQL code is showing.

SELECT [Last_Name] & ", " & [First_Name] & " " & [Middle_Name] AS Name, dbo_tblApplicantionion.CaseSSNumber, dbo_tblApplicantion.DateFormsReceived, dbo_tblApplicantion.Date_Assigned, Date()-[Date_Assigned] AS [Route to Aging], dbo_tblApplicantion.Result, dbo_tblApplicantion.Determination, dbo_tblApplicantion.Case_Dt, dbo_tblApplicantion.IssuesCategory, dbo_tblApplicantion.SENT_LETTER, dbo_tblApplicantion.LETTER_RETURNED, IIf([Inactive]=True,"Yes","No") AS INA, dbo_tblApplicantion.DateSACReceived, IIf([EmpConNameConcate] Is Null,"Blank",[EmpConNameConcate]) AS RouteTo, IIf([LETTER_RETURNED]>=[SENT_LETTER],[LETTER_RETURNED],Null) AS [LOI CLOSED DATE], IIf([SENT_LETTER] Is Not Null And [CREDIT_LETTER_RETURNED]>=[SENT_CREDIT_LETTER] Is Null,"Open",IIf([SENT_CREDIT_LETTER] Is Null,"No Contact Noted","Closed")) AS OpenLOI, IIf([OpenLOI] In ("Open"),Date()-[SENT_LETTER],Null) AS [LOI Aging], IIf([IssuesCategory]="Credit/Criminal","Cred/Crim") AS NameChange, EmpConTAbleNumber.EmpConNameConcate
FROM dbo_tblApplicantion RIGHT JOIN EmpConTAbleNumber ON dbo_tblApplicantion.RoutedID = EmpTAbleNumber.EmpID
WHERE (((dbo_tblApplicantion.Determination) In ("Pending","Incpl Form","Decisional") Or (dbo_tblApplicantion.Determination) Is Null));

 

[PHV]

I don't see any field called "Case Assigned to" ...
So, what is the problem with this SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cthai]

Hi PHV-

sorry i rename "case assigned to" to "RouteTo"

when I set this up in a report - i have a Being date and End date - from a combo box i select a person name then i enter a date range that i would like to see how many case this person have close in the date range i have enter, if the person i selected did not have any case close from the date ranage i enter the date function shows up as #ERROR and the Routed To is blank...i would like the report to show the date range and the person name as well even when they have no case closed from the date range.

** off topic** just want to say Thank you for taken your time in helping me solving this issue... greatly appreciated