Problem With Form Button

[bob bisquick]

I am building a database that is geared towards running a report with some totals of the information in a seperate database. I have created all my queries. They all work. I have created my report. It works. Now I am making a form where the user enters dates to limit the data included in the report. The dates are stored as global functions. Here is the code associated with my form:

Private Sub Run_Report_Click()
On Error GoTo Err_Run_Report_Click

Dim stDocName As String
'Set Date Range
If IsNull([Start_Date]) Or IsNull([End_Date]) Then
MsgBox "Please Enter a Date Range"
Exit Sub
End If

Current_From_Date = Nz([Start_Date], "")
Current_To_Date = Nz([End_Date], "")

stDocName = "Monthly_Prison_Outreach_Report"
DoCmd.Close
DoCmd.OpenReport stDocName, acViewPreview

Exit_Run_Report_Click:
Exit Sub

Err_Run_Report_Click:
MsgBox Err.Description
Resume Exit_Run_Report_Click

End Sub

When I enter the dates and then click the OK button on my form (called Run_Report) I get the following message:

Microsoft Access can't find the field "|" referred to in your expression.

Now, I have never referred to "|" anywhere in the database, so I don't even know how to troubleshoot because I don't know what the hell it's talking about.

 

[jfgambit]

Have you tried changing:

Current_From_Date = Nz([Start_Date], "")
Current_To_Date = Nz([End_Date], "")

to

Current_From_Date = me.[Start_Date]
Current_To_Date = me.[End_Date]

 

[bob bisquick]

No Dice

I tried the "me.[start_date]" thing and no dice.

The query that the dates affect is this:

SELECT srv_patient.ptn_pk, srv_client_questionaire.cln_qs_annual_year, srv_patient.ptn_custom_4_rfk, Max(srv_saar_service.sar_sr_date) AS MaxOfsar_sr_date, srv_client_questionaire.cln_qs_annual_aids_hiv_status_rfk
FROM (srv_patient INNER JOIN srv_client_questionaire ON srv_patient.ptn_pk = srv_client_questionaire.cln_qs_ptn_fk) INNER JOIN srv_saar_service ON srv_patient.ptn_pk = srv_saar_service.sar_sr_ptn_fk
WHERE (((srv_client_questionaire.cln_qs_annual_aids_hiv_status_rfk)="1" Or (srv_client_questionaire.cln_qs_annual_aids_hiv_status_rfk)="2" Or (srv_client_questionaire.cln_qs_annual_aids_hiv_status_rfk)="3"))
GROUP BY srv_patient.ptn_pk, srv_client_questionaire.cln_qs_annual_year, srv_patient.ptn_custom_4_rfk, srv_client_questionaire.cln_qs_annual_aids_hiv_status_rfk
HAVING (((srv_client_questionaire.cln_qs_annual_year)="2003") AND ((srv_patient.ptn_custom_4_rfk)="03") AND ((srv_client_questionaire.cln_qs_annual_aids_hiv_status_rfk) Between FromDate() And ToDate()));

Sorry, that's pretty messy. I had used Access' GUI interface to create it. Anyway, the thing is, the report is actually based on Querys that are based on this query. This query creates the list of clients that I want to work with, then I create a bunch of other querys that tell me things about those patients, then I get a count of the result of each of those querys, and then a query that is all those different counts. The report is based on that.

Clients in question -> Subset of thoe clients 1 -> Count of 1
-> Subset 2 -> Count of 2
-> Subset 3 -> Count of 3

All Counts -> Report

Sorry Pat, your explanation is beyond me.

 

[jfgambit]

Bob:

Here is an example of how to filter a date in a Query and in a listbox on a form.

This should help you.