If No Discharge Date #Error

[burrina]

How can I eliminate the #Error if no Discharge Date has been given yet? Of course it can't count the number of Visits since their is no Discharge Date!

Code: Select all

7Day: Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Visits>" & [Visits] & " And Admitted Between #" & Format([Discharged],"mm\/dd\/yyyy") & "# And #" & Format([Discharged]+7,"mm\/dd\/yyyy") & "#"))

[HansV]

You could replace both instances of [Discharged] with Nz([Discharged],#01/01/1900#)

[burrina]

Does that mean I keep my Format Syntax? Having trouble with the replace since I can't use find and replace in my query!

[HansV]

Yes, it'd be

7Day: Val(DCount("*","tblAdmissions","PatientID=" & [PatientID] & " And Visits>" & [Visits] & " And Admitted Between #" & Format(Nz([Discharged],#01/01/1900#),"mm\/dd\/yyyy") & "# And #" & Format(Nz([Discharged],#01/01/1900#)+7,"mm\/dd\/yyyy") & "#"))

One way to find/replace is:
- Switch to SQL view
- Copy the text
- Paste it into Notepad
- Perform the find/replace there
- Copy the text in Notepad
- Paste it into the SQL view of your query

I myself use the Find and Replace add-in from Rick Fisher: Products - Rick Fisher Consulting. It's not free, but you can try it out for free for 30 days.

[burrina]

Thanks Very Much. I had forgot about the view sql. Rick's add-in is on my list.

Thanks Again!