Crosstab Report with Selecting Date on Form

[natural]

Good Afternoon

I was hoping someone could possible assit

I currently have a report that i need to run monthly daily. etc

The report is based on a cross tab query, and i it dynamically updates the date fields

However I am hoping to select
Begining Date [unbound box]
and
End Date [unboundbox]
on a form and with the onclick event it will open the crosstab report for the selected days.

In my query which my report runs from (crosstab query)
Under my date wich is called Bookingdate - (this is my colum heading)

I typed the following twice underneath one another

Between[forms]![FrmDailytotalsSelect]![beginningdate] and [forms]![FrmDailytotalsSelect]![Endingdate]

Hoping this will allow me to enter the dates

It gives me the error

The Ms Jet Database engine does not recognize
[forms]![FrmDailytotalsSelect]![beginningdate] and [forms]![FrmDailytotalsSelect]![Endingdate] as a valid field name or expression.

Can i do this on the crosstab query.

 

[pbaldy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209778

 

[natural]

Thank you for the response

I completely removed the entry under my criteria under the bookingdate colum

Went to the parameters section as indicated in the article

Typed in under paramater

Between [forms]![FrmDailytotals]![BeginingDate] And [forms]![Frmdailytotals]![EndingDate]


Set my Data type to Date/Time
And it now gives me an error

Invalid Bracketing of name ......Between [forms]![FrmDailytotals]![BeginingDate] And [forms]![Frmdailytotals]![EndingDate]


Also if i just use [Enter date] Enter n fixed date ie 20/12/2010 it still brings up all the records

 

[pbaldy]

You would leave the criteria the way it was, and have two lines in the parameters section, one for each form reference. Looks like the attached.

 

[DCrake]

Her is an Alternative method or approach to this subject. See if this works for you.

 

[natural]

Hi Paul

Thank you very much that work.
However only when i run my query..
When i run the report, that is based on the query
It will prompt me for the days but no data will display.. Not to sure why.

However if i run with the paramater, it list all the current data in the table only 33 days in at the moment.

Any idea why the query might work but the report on open does not see this at all

 

[pbaldy]

Is FrmDailytotalsSelect open when the report opens (it needs to be)?

 

[natural]

Good Morning Paul
My apologies for the late reply.

Yes the form would be open
if open my db. and
Open the query, it will prompt for start date and end date.
And it will open it perfectly

I will then open the form (unbound)
Enter the dates and then click on my View Report button

On the onlick event
I have

Dim SrtDoc Name As string

If isdate (BeginningDate) and IsDate (EndingDate) then
if Endingdate < BeginningDate Then
Msgbox "The ending date must be later then the beginning date"
exit Sub
End If
Else
Msgbox " Please use a valid date for the beginning date and the ending date"
exit sub
End If

Strdocname = "RptDailytotals"
docmd.Openreport strdocname, acviewpreview



My report is then based on the query
Where under the bookingdate columns I have

Between [Forms]![FrmDailyTotalsSelect]![Beginingdate] And [Forms]![FrmDailytotalsSelect]![EndingDate]

Then the parameter are set to

1st Entry [Forms]![FrmDailyTotalsSelect]![Beginingdate] - Date/Time
2nd Entry [Forms]![FrmDailytotalsSelect]![EndingDate] - Date/time

Yes My form would be open when the Report opens.
It is currently set to a popup So need to manually close after the report has opened

 

[DCrake]

Could there be some ambiguity betwen the dates UK v US format?
TRy putting the same in both prompts and make it a date that cannot be transposed to the US format, such as 15/12/2010.

 

[natural]

Hi David

I tried that made double sure dates are entered as 01/12/2010 to 02/12/2010
Still Nothing

 

[pbaldy]

Can you post the db, or a representative sample?

 

[natural]

Hi Paul

Thank you for your help

Attached is a copy of the db.
It is running of the query "qryTotalPerDay"

Thank you

 

[pbaldy]

The report is opening without prompts as I would expect. The appearance of no data is because of your code in the open event (that's supposed to set the control sources). Have you set a breakpoint and watched that code execute? You might review wherever you got that code from, because it's not running properly. I don't know that you can use fld that way. I do this type of thing a little differently, using the count of fields.

 

[natural]

Hi Paul

I am not sure... No promps.. I get two when open the report directly,

i will however be using the form to open the report.

I got the code from a access site... It was Called.. Simple Crosstab Sample, and just added some more labels on the report to accommodate my total 31 days. Any guidance would be greatly appreciated on this. As this is my first

 

[pbaldy]

To be fair, I had the form open already and opened the report from the database window. Opening it from the form's preview button also works as expected, no prompts. Opening it from the database window without the form being opened would certainly trigger the prompts.

I do this type of thing, after opening a recordset on the report's source:

intColCount = rst.Fields.Count

For i = 1 To intColCount

and then use i when getting the field name.