Report Criteria and public variables??

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,

I have a crosstab report that takes criteria from a form
i.e Date = forms!frmSalesDates!txtDatafrom

Is is possible for the query that this report runs on takes the criteria
from a public variable?

eg When a user selects a date (for eg 12/12/02) I want the variable that is
passed to the report always to be the month end date - in this eg. 31/12/02

Thanks
 
-----Original Message-----
Hi,
eg When a user selects a date (for eg 12/12/02) I want the variable that is
passed to the report always to be the month end date - in
Click to expand...
this eg. 31/12/02


here is one possible approach;

DateAdd("m",1,(DateAdd("d",(Day([vdate])*(-1)),[vdate])))
 
Newbie said:
I have a crosstab report that takes criteria from a form
i.e Date = forms!frmSalesDates!txtDatafrom

Is is possible for the query that this report runs on takes the criteria
from a public variable?

eg When a user selects a date (for eg 12/12/02) I want the variable that is
passed to the report always to be the month end date - in this eg. 31/12/02
Click to expand...

No, VBA variables are only available in the VBA environment.
You can, however, use either built-in functions or your own
Public functions (in a standard module).

In this case, you can use a criteria of:

DateSerial(Year(Forms!theform.atextbox),
Month(Forms!theform.atextbox) + 1, 0)
 
Thanks for that but I tried this before but it kept coming up with an error
of invalid bracketing on Name . . .

Here is what I have in the Crosstab query - Query Parameters

DateSerial(Year([forms]![frmConToolsCriteria]![dtpDateFrom]),Month([forms]![
frmConToolsCriteria]![dtpDateFrom])+1,0)

And here is what I have as a Column Heading:

Expr1: "Mth" &
DateDiff("m",[OrigReceiptDate],DateSerial(Year([forms]![frmConToolsCriteria]
![dtpDateFrom]),Month([forms]![frmConToolsCriteria]![dtpDateFrom])+1,0))

What am I doing wrong?
 
Newbie said:
Thanks for that but I tried this before but it kept coming up with an error
of invalid bracketing on Name . . .

Here is what I have in the Crosstab query - Query Parameters

DateSerial(Year([forms]![frmConToolsCriteria]![dtpDateFrom]),Month([forms]![
frmConToolsCriteria]![dtpDateFrom])+1,0)

And here is what I have as a Column Heading:

Expr1: "Mth" &
DateDiff("m",[OrigReceiptDate],DateSerial(Year([forms]![frmConToolsCriteria]
![dtpDateFrom]),Month([forms]![frmConToolsCriteria]![dtpDateFrom])+1,0))

What am I doing wrong?
Click to expand...


I think the query paramater is just supposed to be:
[forms]![frmConToolsCriteria]![dtpDateFrom]
 
Thanks but I am now a bit confused . . . what does the Query Parameter do?
If Query parameter just = forms!frmtextbox and the
Column Heading criteria =
dateserial(year(formstextbox),month(forms!frmtextbox) + 1, 0)
what will the query return.

Thanks
Al

Marshall Barton said:
Newbie said:
Thanks for that but I tried this before but it kept coming up with an error
of invalid bracketing on Name . . .

Here is what I have in the Crosstab query - Query Parameters

DateSerial(Year([forms]![frmConToolsCriteria]![dtpDateFrom]),Month([forms]!
Click to expand...
[
frmConToolsCriteria]![dtpDateFrom])+1,0)

And here is what I have as a Column Heading:

Expr1: "Mth" &
DateDiff("m",[OrigReceiptDate],DateSerial(Year([forms]![frmConToolsCriteria
Click to expand...
]
![dtpDateFrom]),Month([forms]![frmConToolsCriteria]![dtpDateFrom])+1,0))

What am I doing wrong?
Click to expand...


I think the query paramater is just supposed to be:
[forms]![frmConToolsCriteria]![dtpDateFrom]
Click to expand...
 
Newbie said:
Thanks but I am now a bit confused . . . what does the Query Parameter do?
If Query parameter just = forms!frmtextbox and the
Column Heading criteria =
dateserial(year(formstextbox),month(forms!frmtextbox) + 1, 0)
what will the query return.
Click to expand...

I think I'm getting confused as to what we're looking for
here.

What is being specified on the form? What does it mean to
the query and the report?

What is the criteria supposed to do? Is it the date the
report should start at or the date it should end at? Or is
it the number of months the rport is supossed to cover?

What is the Column Headings? It looks like you want it to
be the number of months before some date, but I can't figure
out that date might be.
 
The form has a date eg 12/12/02. Based on the entry here the query is
supposed to return records summed by month for the previous 12 mths
however, the query only works properly if a month end date is entered.
therefore if the user enters 12/12/02 I want the query to read this as
31/12/02.

Is the Query Parameter box just telling the query where to look for the
info? i.e on the form
and the column heading syntax telling it what to do once it has found it?
ie. dateserial(etc etc)
Is this how it works?

Thanks for your help
Al
 
Newbie said:
The form has a date eg 12/12/02. Based on the entry here the query is
supposed to return records summed by month for the previous 12 mths
however, the query only works properly if a month end date is entered.
therefore if the user enters 12/12/02 I want the query to read this as
31/12/02.
Click to expand...

If everything already works when the last day of a month is
entered on the form, then, instead of trying to modify the
query, why not just make the form adjust the date when it is
entered? This is easy to do in the dtpDateFrom text box's
After Update event procedure using the last day of the month
expression (DateSerial) mentioned earlier.

Me.dtpDateFrom = DateSerial(Year(Me.dtpDateFrom), _
Month(Me.dtpDateFrom) + 1, 0)

THis would also allow you to enter just the month and year
(Access will use the first of the month when you don't
specify the day) such as 12/02 or Dec 2002 or ...

Is the Query Parameter box just telling the query where to look for the
info? i.e on the form
Click to expand...

Yes, but it has to tell it the control on the form too.
That's why I said the parameter is probably:

Forms!frmConToolsCriteria!dtpDateFrom

whereas you have mentioned several variations that have left
me wondering what you're actually referring to.

Then, in the body of the query, you have to refer to the
value on the form **exactly** the same as specified in the
parameter. It might help to think of the parameter as a way
to tell the query what to do with something it ordinarily
would not know how to process.

and the column heading syntax telling it what to do once it has found it?
ie. dateserial(etc etc)
Is this how it works?
Click to expand...

No. The Column Heading is a special designation for a
crosstab query that tells the query which data goes into
which columns. A parameter can be used just about anywhere
in an Access query, not only for what you are doing.
 
Thanks for your help - I think I'll go with the afterudate suggestion.
Al
Marshall Barton said:
Newbie said:
The form has a date eg 12/12/02. Based on the entry here the query is
supposed to return records summed by month for the previous 12 mths
however, the query only works properly if a month end date is entered.
therefore if the user enters 12/12/02 I want the query to read this as
31/12/02.
Click to expand...

If everything already works when the last day of a month is
entered on the form, then, instead of trying to modify the
query, why not just make the form adjust the date when it is
entered? This is easy to do in the dtpDateFrom text box's
After Update event procedure using the last day of the month
expression (DateSerial) mentioned earlier.

Me.dtpDateFrom = DateSerial(Year(Me.dtpDateFrom), _
Month(Me.dtpDateFrom) + 1, 0)

THis would also allow you to enter just the month and year
(Access will use the first of the month when you don't
specify the day) such as 12/02 or Dec 2002 or ...

Is the Query Parameter box just telling the query where to look for the
info? i.e on the form
Click to expand...

Yes, but it has to tell it the control on the form too.
That's why I said the parameter is probably:

Forms!frmConToolsCriteria!dtpDateFrom

whereas you have mentioned several variations that have left
me wondering what you're actually referring to.

Then, in the body of the query, you have to refer to the
value on the form **exactly** the same as specified in the
parameter. It might help to think of the parameter as a way
to tell the query what to do with something it ordinarily
would not know how to process.

and the column heading syntax telling it what to do once it has found it?
ie. dateserial(etc etc)
Is this how it works?
Click to expand...

No. The Column Heading is a special designation for a
crosstab query that tells the query which data goes into
which columns. A parameter can be used just about anywhere
in an Access query, not only for what you are doing.
--
Marsh
MVP [MS Access]

Parameter
do?
Click to expand...
Click to expand...
 
Back
Top