Form driven report

[Tony Williams]

I am creating a report that compares quarterly figures. The control for the
value of the quarter is txtmonthlabel.
I want to build a form based on the underlying query to report that asks the
user to put in two values. eg March 2004 and March 2003 I know how to create
the query to ask for input on one value of the control but how do I do it if
I want more than one value. I cant use BETWEEN because this would bring up
all the quarters between March 2003 and March 2004?
Anyone help?
Tony Williams

 

[Guest]

hi,
you might try a union query. it is a SQL specific query.
look union querys up in help for examples.
a union query is like a query within a query and puts
common date in the same column.
regards

 

[Guest]

If you are creating a form, why not get them to type these figures into two
textboxes on the form, before they hit a 'Run report' control.

The query of the report would then have criteria "between
Forms!frmReport!txtstart and Forms!frmReport!txtend" or something similar.

You'll probably need to declare these parameters on report load.

 

[Guest]

Just re-read. sorry - have a criteria saying "xxxx" Or "xxxxx" in the same way

You will need to reference the "xxxx" bits from a form. Depending on the
form, you may need to consider if they only enter 1 option. This might need
some VBA

 

[Guest]

Thanks Basil
Should the form be based on a table or query or just a form with two text
boxes?
Tony

 

[Tony Williams]

Thanks I'll check that out
Tony

hi,
you might try a union query. it is a SQL specific query.
look union querys up in help for examples.
a union query is like a query within a query and puts
common date in the same column.
regards

 

[Guest]

Basil tried using unbound form with two unbound text boxes. When I click
command button I'm asked for the values again?
Any idea what I'm doing wrong?
Thanks
tony

 

[Guest]

I just tested it for myself - I put the sql of the query straight into the
report's record source (rather than referencing a saved query) and it didn't
prompt me for anything.

I used an unbound form.

I have come across this problem before and from memory there were 2 ways of
doing this - either set the recordsource of the report onload by setting it
to equal the query's sql text (done within VBA) - however, rather than
referencing the form's textboxes within the sql - I did it within the VBA -
i.e.

dim sqlstr as string

sqlstr = "Select ......... WHERE ((([Table1].[number])= '" &
[Forms]![Form3]![Text0] & "' Or ([Table1].[number])= '" &
[Forms]![Form3]![Text2] & "'));"

The other way was to specifically state the parameters (DAO recordset).

Sorry for my explainations not being as certain or as clear as I'd like -
I'm a bit rusty to say the least, and don't have any of my old databases at
hand.

 

[Guest]

Thanks Basil I'm a bit rusty on sql but I'll try that
Tony

I just tested it for myself - I put the sql of the query straight into the
report's record source (rather than referencing a saved query) and it didn't
prompt me for anything.

I used an unbound form.

I have come across this problem before and from memory there were 2 ways of
doing this - either set the recordsource of the report onload by setting it
to equal the query's sql text (done within VBA) - however, rather than
referencing the form's textboxes within the sql - I did it within the VBA -
i.e.

dim sqlstr as string

sqlstr = "Select ......... WHERE ((([Table1].[number])= '" &
[Forms]![Form3]![Text0] & "' Or ([Table1].[number])= '" &
[Forms]![Form3]![Text2] & "'));"

The other way was to specifically state the parameters (DAO recordset).

Sorry for my explainations not being as certain or as clear as I'd like -
I'm a bit rusty to say the least, and don't have any of my old databases at
hand.

Basil tried using unbound form with two unbound text boxes. When I click
command button I'm asked for the values again?
Any idea what I'm doing wrong?
Thanks
tony