Unbound text box with a crosstab query

[Creeker]

I have a report that is based on a crosstab query. I want to have a text box that asks the user to enter a due date for returning the report, ex. ="Please return this form by " + [ender due date]

but I get the following error:
The Microsoft Jet databse engine does not recognize 'date' as a valid field name or expression.

Can anyone tell me what I'm doing wrong or if this is possible with the crosstab query? It works just great with my other forms.

 

[dhookom]

You must specify the data type of all parameters in crosstab queries. Select Query->Parameters and enter:
[ender due date] Date/Time


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]

 

[Creeker]

I'm a little confused. The due date is not in the query itself, has nothing to do with the data. It is merely text being added to the printed form. I tried it anyhow and it didn't work.

 

[dhookom]

I am confused. You state your error message refers to "Date" and yet your parameter mentions "ender due date".

Maybe it's time to share your sql. Does the query run correctly by itself?

Do you have a field name date? Date is also the name of a function so it should never be used as a field name.

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]

 

[Creeker]

Sorry for the confusion. I merely want to add a text box to a report that prompts for a text input. In this case it is a date, but it is just variable text on a report.

This works fine on normal reports based on a simple query, but for some reason this report, based on a crosstab query, reads the input prompt inside the brackets as a field.

In this report "date" is an input prompt. It could be "name" or "color" or any text and will be displayed on the report as text in this viewing only.

The report is used as a survey and I need to change the due date every time it is printed.

 

[dhookom]

As I told you earlier, you must enter the data type of your parameter into your query parameters. If you have a text box on your report with a control source like:
="The Date: " & [Enter The Date]
Your query must have a parameter declared like:
[Enter The Date] Text

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]

 

[Creeker]

Well I followed directions and entered the data type in the query parameters. When I run the report, it prompts me for the date for each and every company page. I don't think this is going to help me. I'll have to find another solution to the problem. Thanks for your help.

 

[dhookom]

You should only have to enter the prompt once. Are you using subreports? You might consider replacing the prompt with the reference to a control on a form. Also, do you include all of your possible column headings in your column headings property of the crosstab?

You have never provided the SQL view of your report's record source.

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]

 

[Creeker]

I've never been able to parameter just one company with this query either. There is no subreport on this form. I'll have to try that. I don't understand your comments on replacing the prompt or on the column headings property. I do not know SQL so my abilities are limited, but here is the SQL of the record source.

SELECT DISTINCT tblCompanies.YearLastUpdated, tblCompanies.HMAMember, tblCompanies.HMAMemberSub, tblCompanies.CoName1, tblFacilities.FacName1, tblFacilities.FacAddress1, tblFacilities.FacAddress2, tblFacilities.FacCity, tblFacilities.FacState, tblFacilities.FacZip, tblFacilities.FacCountry, tblFacilities.FacPhone, tblFacilities.FacFax, tblFacilities.FacTypeID, tblCompanies.CoID, tblFacilities.FacID, qryFacilitiesSpeciesCrosstab.Alder, qryFacilitiesSpeciesCrosstab.Ash, qryFacilitiesSpeciesCrosstab.Aspen, qryFacilitiesSpeciesCrosstab.Basswood, qryFacilitiesSpeciesCrosstab.Beech, qryFacilitiesSpeciesCrosstab.Birch, qryFacilitiesSpeciesCrosstab.Butternut, qryFacilitiesSpeciesCrosstab.Cherry, qryFacilitiesSpeciesCrosstab.Cottonwood, qryFacilitiesSpeciesCrosstab.Cypress, qryFacilitiesSpeciesCrosstab.Elm, qryFacilitiesSpeciesCrosstab.Gum, qryFacilitiesSpeciesCrosstab.Hackberry, qryFacilitiesSpeciesCrosstab.[Hard Maple], qryFacilitiesSpeciesCrosstab.[Hickory/Pecan], qryFacilitiesSpeciesCrosstab.Locust, qryFacilitiesSpeciesCrosstab.[Pacific Coast Maple], qryFacilitiesSpeciesCrosstab.Poplar, qryFacilitiesSpeciesCrosstab.[Red Oak], qryFacilitiesSpeciesCrosstab.Sassafras, qryFacilitiesSpeciesCrosstab.[Soft Maple], qryFacilitiesSpeciesCrosstab.Sycamore, qryFacilitiesSpeciesCrosstab.Tupelo, qryFacilitiesSpeciesCrosstab.Walnut, qryFacilitiesSpeciesCrosstab.[White Oak], qryFacilitiesSpeciesCrosstab.Willow, qryFacilitiesSpecialtiesCrosstab.Beams, qryFacilitiesSpecialtiesCrosstab.Blocking, qryFacilitiesSpecialtiesCrosstab.[Boxes, Crates & Shook], qryFacilitiesSpecialtiesCrosstab.[Brush Blocks], qryFacilitiesSpecialtiesCrosstab.[Cabinet Doors], qryFacilitiesSpecialtiesCrosstab.[Cabinet Rails & Stiles], qryFacilitiesSpecialtiesCrosstab.Dimension, qryFacilitiesSpecialtiesCrosstab.[Edge Glued Panels], qryFacilitiesSpecialtiesCrosstab.Export, qryFacilitiesSpecialtiesCrosstab.[Fence Stock], qryFacilitiesSpecialtiesCrosstab.[Finger Jointing], qryFacilitiesSpecialtiesCrosstab.Flooring, qryFacilitiesSpecialtiesCrosstab.[Frame Stock], qryFacilitiesSpecialtiesCrosstab.[Gunstock Blanks & Squares], qryFacilitiesSpecialtiesCrosstab.[Heavy Thickness Lumber], qryFacilitiesSpecialtiesCrosstab.[Industrial Timbers], qryFacilitiesSpecialtiesCrosstab.[Interior Trim, Moulding & Millwork], qryFacilitiesSpecialtiesCrosstab.[Kiln-dried Lumber], qryFacilitiesSpecialtiesCrosstab.[Length Separation of Lumber], qryFacilitiesSpecialtiesCrosstab.[Oak Bending Stock], qryFacilitiesSpecialtiesCrosstab.[Pallet Cants], qryFacilitiesSpecialtiesCrosstab.[Pallet Stock/Shook], qryFacilitiesSpecialtiesCrosstab.Pallets, qryFacilitiesSpecialtiesCrosstab.[Railway Car Material], qryFacilitiesSpecialtiesCrosstab.[Railway Ties], qryFacilitiesSpecialtiesCrosstab.[Rift & Quartered Lumber], qryFacilitiesSpecialtiesCrosstab.[S2S Lumber], qryFacilitiesSpecialtiesCrosstab.[S4S Lumber], qryFacilitiesSpecialtiesCrosstab.[Thresholds, Treads & Risers], qryFacilitiesSpecialtiesCrosstab.Veneer, qryFacilitiesSpecialtiesCrosstab.[Width Separation of Lumber], qryFacilitiesSpecialtiesCrosstab.[Wood Shavings]
FROM tblCompanies INNER JOIN ((tblFacilities LEFT JOIN qryFacilitiesSpeciesCrosstab ON tblFacilities.FacID = qryFacilitiesSpeciesCrosstab.FacID) LEFT JOIN qryFacilitiesSpecialtiesCrosstab ON tblFacilities.FacID = qryFacilitiesSpecialtiesCrosstab.FacID) ON tblCompanies.CoID = tblFacilities.CoID
WHERE (((tblCompanies.HMAMember)=Yes) AND ((tblFacilities.FacTypeID)=1 Or (tblFacilities.FacTypeID)=2 Or (tblFacilities.FacTypeID)=3))
ORDER BY tblCompanies.CoName1, tblFacilities.FacName1;

 

[dhookom]

You could add the Query->Parameters to the query above.

I would suggest you don't even mess with the parameter. I assume your report is opened with code behind a form. Assuming the form name is "frmMyForm", add a text box to the form "txtTheDate". Then, in your report, you can add a text box with a control source like:

="This is from the form: " & Forms!frmMyForm!txtTheDate

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]

 

[Creeker]

Thank you for trying but this is way too much fuss to simply remind myself to change a due date. I created a message box to get in my face and remind me to change the date.