PASS QRY PARAM FOR CROSSTAB QRY

[Jean9]

I have created a crosstab query that requires "DTWKEND" date/time parameter. I created a report for this crosstab query but do not set the report recordsource until the OnOpen Event of the report because I can't work on the report when its recordsource is set to the qry as it keeps prompting me for DTWKEND over and over and over and over. So, the user will access this report from a form.

    Dim sLINKCRIT As String
    sLINKCRIT = "DTWKEND=#" & Me.DtWkEnd & "#"
    sDOCNM = "RptGROUP_TS"
    DoCmd.OpenForm sDOCNM, , , sLINKCRIT

The execution of this code receives the following error
2102 The form name 'RptGROUP_TS' is misspelled or refers to a form that doesn't exist.

I double-checked, the report is named that. So I figure it's because it is based on that crosstab query and the sLINKCRIT in the code is not getting the parameter to that underlying Query for the report. So I am looking for the proper method of passing the parameter from the form to the report/report's query recordsource if the recordsource is not set until the OnOpen event.

Thanks,
J9

 

[Jean9]

Nevermind about the error part of this thread. I think I figured out why I was getting that error. I was executing a Form open when it should have been a report open.

I still need to know how to pass that parameter from the form to the query.
Thanks,
J9

 

[dhookom]

One method of passing a parameter value to a query is to create a one field, one record table. Or if you want a date range consider a two field table:

tblDateRange
================
datStart date/time
datEnd date/time

You can then add this, one record table to a query and use datStart and datEnd in the criteria. It is very important to have only a single record in tblDateRange.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]