L
Leslie Isaacs
Hello All
I have a table [x confirmed] with the following fields
'name' (yes I know - bad idea - but it works OK so far and I'm stuck with it
for now!)
'practice'
'NI 1e'
(and lots of other fields).
I have 3 reports based on a query on this table, where the user may wish the
report to include just one 'name' or alternatively all the 'name's where the
value of 'practice' is as selected in combobox 'prac name' in the current
form (called 'frm x main'). If they want to include just one 'name' they
select that name in combo56 on 'frm x main' and click the button labelled
"single" - otherwise combo56 is left blank and they click the button
labelled "all".
I have tried the query below, which returns the correct records - but it is
VERY slow (with all the other fields that are included and some more
criteria it takes 45 - 60 seconds on average, sometimes longer).
Is there a better way to write this query with the variable parameter, or do
I have to create a seperate query for the 2 cases (i.e. single name or all
names). If I do create 2 seperate queries do I also have to create 3
seperate reports, or is there a way of putting some code behind each button
("single" and "all") that tells the report which quer to use?
The query I have is:
SELECT [x confirmed].practice, [x confirmed].name, [x confirmed].[NI 1e],
IsNull([forms]![frm x main]![combo56]) AS [single]
FROM [x confirmed]
WHERE ((([x confirmed].name)=[forms]![frm x main]![combo56]) AND
((IsNull([forms]![frm x main]![combo56]))=False)) OR ((([x
confirmed].practice)=[Forms]![frm x main]![prac name]) AND
((IsNull([forms]![frm x main]![combo56]))=True));
Hope someone can help
Many thanks
Leslie Isaacs
I have a table [x confirmed] with the following fields
'name' (yes I know - bad idea - but it works OK so far and I'm stuck with it
for now!)
'practice'
'NI 1e'
(and lots of other fields).
I have 3 reports based on a query on this table, where the user may wish the
report to include just one 'name' or alternatively all the 'name's where the
value of 'practice' is as selected in combobox 'prac name' in the current
form (called 'frm x main'). If they want to include just one 'name' they
select that name in combo56 on 'frm x main' and click the button labelled
"single" - otherwise combo56 is left blank and they click the button
labelled "all".
I have tried the query below, which returns the correct records - but it is
VERY slow (with all the other fields that are included and some more
criteria it takes 45 - 60 seconds on average, sometimes longer).
Is there a better way to write this query with the variable parameter, or do
I have to create a seperate query for the 2 cases (i.e. single name or all
names). If I do create 2 seperate queries do I also have to create 3
seperate reports, or is there a way of putting some code behind each button
("single" and "all") that tells the report which quer to use?
The query I have is:
SELECT [x confirmed].practice, [x confirmed].name, [x confirmed].[NI 1e],
IsNull([forms]![frm x main]![combo56]) AS [single]
FROM [x confirmed]
WHERE ((([x confirmed].name)=[forms]![frm x main]![combo56]) AND
((IsNull([forms]![frm x main]![combo56]))=False)) OR ((([x
confirmed].practice)=[Forms]![frm x main]![prac name]) AND
((IsNull([forms]![frm x main]![combo56]))=True));
Hope someone can help
Many thanks
Leslie Isaacs