Report Error 1

[Hawkide]

Access 97

I am creating a report with a chart on it. The chart is based on a query that has uses "Between And" for one of the ctriteria. If I hardcode the dates in the query criteria:

Between #9/1/04# And #12/15/04#

the Report shows the graph as expected.

However if I try to read the dates from a form:

Between CDate([Forms]![**frmMetrics]![txbStart]) And CDate([Forms]![**frmMetrics]![txbEnd])

the report gives the following error:

The Microsoft Jet database engine does not recogize '[Forms]![**frmMetrics]![txbStart]' as a valid field name or expression

The query returns the data, but the report chokes on it...Any help would be greatly appreciated. TIA...

 

[ZmrAbdulla]

This should work for you
Between [Forms]![frmMetrics]![txbStart] And [Forms]![frmMetrics]![txbEnd]
BTW, What is the actual name of [Blue]frmMetrics[/blue] ?
Is it [Blue]**frmMetrics[/blue] or [Blue]frmMetrics[/blue]?
You have to enter the correct name of the form
regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[dhookom]

Hawkide,
Also, charts often use crosstab queries as Row Sources. Crosstabs require you to specify the data type of any parameters in the Query->Parameters dialog.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Hawkide]

The form is named **frmMetrics (for beta testing so the form will be at the top of the list)

Duane,

My query is a select query (not a crosstab). Are you saying that a chart cannot be based on a query that has a parameter passed to it? Sorry, if I am misunderstaning something, but I am fairly new to Access. TIA...

 

[Hawkide]

Duane...

I think I got it thanks to your help. I added th e following to the Query-> Parameters dialog

[Forms]![**frmMetricsReports]![txbEnd] Date/Time
[Forms]![**frmMetricsReports]![txbStart] Date/Time

and now it works. Is this what your were suggesting I do? Thanks for your help guys...

 

[dhookom]

That was what I was suggesting. Usually the parameter data types are only required for crosstab queries. Are you sure you looked at the SQL view of your chart's row source property?

BTW: I would never use "*" in the name of any object. I often use "_" or other to display objects at the top of a list. You can also create custom groups of objects in your database.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Hawkide]

I misunderstood (I guess I should read a little more carefully). My query is a select query, but the Chart's row source property is as follows:

TRANSFORM Count([**Stage_Volume].Substage) AS CountOfSubstage
SELECT (Format([Date],"mmm"" '""yy")) AS Expr1
FROM [**Stage_Volume]
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy"))
PIVOT [**Stage_Volume].Substage;

I guess "TRANSFORM" implies a crosstab query.

Also, I plan to remove the "*" when I have the reports working. I am merely trying to figure out how to create these reports since I have never created reports with charts before. Why do you suggest "_" as opposed to "*"? I will look at the idea of creating a custom group (I have never heard of that before).

I have spent quite a bit of time struggling with these reports. Thank you so much for your help. I truely appreciate it...

 

[dhookom]

The "*" character is a wildcard in Access sql/queries. When people view "*" they immediately think something is wrong as I did and possibly Zameer.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]