Form driven report

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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
 
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.
 
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
 
Thanks Basil
Should the form be based on a table or query or just a form with two text
boxes?
Tony
 
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
Click to expand...
 
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
 
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.
 
Thanks Basil I'm a bit rusty on sql but I'll try that
Tony

Basil said:
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.

Tony Williams said:
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
Click to expand...
Click to expand...
 
Back
Top