Query with variable parameter

[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

 

[Marshall Barton]

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));

Try basing the report on the x confirmed table and applying
the filtering in the OpenReport method's WhereCondition
argument.

The button for single name would use code like:

DoCmd.OpenReport "the report", acviewPreview, _
WhereCondition:= "[Name] =""" & Me.combo56 & """"

and the one for all names could be just:

DoCmd.OpenReport "the report", acviewPreview

 

[Leslie Isaacs]

Marshall

Thanks for your reply.

Is there a 'typo' in you suggested code -
WhereCondition:= "[Name] =""" & Me.combo56 & """"
Should there be the same number of quotes before and after:
& Me.combo56 &
Why are any quotes needed there anyway?

I will not get the opportunity to try your suggestion until Thursday, but
will post back then with the results.

Thanks again
Les

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));

Try basing the report on the x confirmed table and applying
the filtering in the OpenReport method's WhereCondition
argument.

The button for single name would use code like:

DoCmd.OpenReport "the report", acviewPreview, _
WhereCondition:= "[Name] =""" & Me.combo56 & """"

and the one for all names could be just:

DoCmd.OpenReport "the report", acviewPreview

 

[Marshall Barton]

Although I am very poor with keyboards, those quotes were
intentional. Quotes around the name are required because a
name by itself would not result in a legal Where clause.
I.e. after the concatenation, the WhereCondition string
without the quotes vould be:
[Name] = E Smith-Jones
when you need it to be
[Name] = "E Smith-Jones"
so Access can figure out which parts of the string are
keywords or syntax characters and which parts are field
names and data.

The reason for the "extra" quotes is because you need to use
two quotes to represent one quote when it is inside other
quotes. I.e. "[Name] =""" results in the string [Name] ="
which is then concatenated to the name from the combo box
and the final quote character.

Don't beat yourself up over the quoted quote issue, it can
be confusing until you eventually wrap your head around the
concepts. An analoguous situation could occur in normal
written English if you used a sentence like:
Bob said, "O'Riley's list is "", ,, ,. and '.", right?".
which can be at least a little difficult for a human to
parse.
--
Marsh
MVP [MS Access]

Is there a 'typo' in you suggested code -
WhereCondition:= "[Name] =""" & Me.combo56 & """"
Should there be the same number of quotes before and after:
& Me.combo56 &
Why are any quotes needed there anyway?

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));

"Marshall Barton" wrote
Try basing the report on the x confirmed table and applying
the filtering in the OpenReport method's WhereCondition
argument.

The button for single name would use code like:

DoCmd.OpenReport "the report", acviewPreview, _
WhereCondition:= "[Name] =""" & Me.combo56 & """"

and the one for all names could be just:

DoCmd.OpenReport "the report", acviewPreview

 

[Leslie Isaacs]

Marshall
Many thanks for that explanation - I get it now ... I think!
I'll let you know how I get on with using your suggestion on Thursday.
Cheers
Les

Although I am very poor with keyboards, those quotes were
intentional. Quotes around the name are required because a
name by itself would not result in a legal Where clause.
I.e. after the concatenation, the WhereCondition string
without the quotes vould be:
[Name] = E Smith-Jones
when you need it to be
[Name] = "E Smith-Jones"
so Access can figure out which parts of the string are
keywords or syntax characters and which parts are field
names and data.

The reason for the "extra" quotes is because you need to use
two quotes to represent one quote when it is inside other
quotes. I.e. "[Name] =""" results in the string [Name] ="
which is then concatenated to the name from the combo box
and the final quote character.

Don't beat yourself up over the quoted quote issue, it can
be confusing until you eventually wrap your head around the
concepts. An analoguous situation could occur in normal
written English if you used a sentence like:
Bob said, "O'Riley's list is "", ,, ,. and '.", right?".
which can be at least a little difficult for a human to
parse.
--
Marsh
MVP [MS Access]

Is there a 'typo' in you suggested code -
WhereCondition:= "[Name] =""" & Me.combo56 & """"
Should there be the same number of quotes before and after:
& Me.combo56 &
Why are any quotes needed there anyway?

Leslie Isaacs wrote:
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));

"Marshall Barton" wrote
Try basing the report on the x confirmed table and applying
the filtering in the OpenReport method's WhereCondition
argument.

The button for single name would use code like:

DoCmd.OpenReport "the report", acviewPreview, _
WhereCondition:= "[Name] =""" & Me.combo56 & """"

and the one for all names could be just:

DoCmd.OpenReport "the report", acviewPreview

 

[Jack Isaacs]

Marshall
Sorry - I didn't get chance to work on the mdb with the query problem that I
had, so can't yet report back ... and now I'm away for 10 days (sailing from
Corfu, Greece!), so will have to wait until after that.
I will post back then.
Thanks again for the help
Les

Marshall
Many thanks for that explanation - I get it now ... I think!
I'll let you know how I get on with using your suggestion on Thursday.
Cheers
Les

Although I am very poor with keyboards, those quotes were
intentional. Quotes around the name are required because a
name by itself would not result in a legal Where clause.
I.e. after the concatenation, the WhereCondition string
without the quotes vould be:
[Name] = E Smith-Jones
when you need it to be
[Name] = "E Smith-Jones"
so Access can figure out which parts of the string are
keywords or syntax characters and which parts are field
names and data.

The reason for the "extra" quotes is because you need to use
two quotes to represent one quote when it is inside other
quotes. I.e. "[Name] =""" results in the string [Name] ="
which is then concatenated to the name from the combo box
and the final quote character.

Don't beat yourself up over the quoted quote issue, it can
be confusing until you eventually wrap your head around the
concepts. An analoguous situation could occur in normal
written English if you used a sentence like:
Bob said, "O'Riley's list is "", ,, ,. and '.", right?".
which can be at least a little difficult for a human to
parse.
--
Marsh
MVP [MS Access]

Is there a 'typo' in you suggested code -
WhereCondition:= "[Name] =""" & Me.combo56 & """"
Should there be the same number of quotes before and after:
& Me.combo56 &
Why are any quotes needed there anyway?


Leslie Isaacs wrote:
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));


"Marshall Barton" wrote
Try basing the report on the x confirmed table and applying
the filtering in the OpenReport method's WhereCondition
argument.

The button for single name would use code like:

DoCmd.OpenReport "the report", acviewPreview, _
WhereCondition:= "[Name] =""" & Me.combo56 & """"

and the one for all names could be just:

DoCmd.OpenReport "the report", acviewPreview

 

[Marshall Barton]

Marshall
Sorry - I didn't get chance to work on the mdb with the query problem that I
had, so can't yet report back ... and now I'm away for 10 days (sailing from
Corfu, Greece!), so will have to wait until after that.
I will post back then.
Thanks again for the help
Les

That sounds like a great excuse for not working on a
database ;-)

After that much time, it would be appropriate for you to
reformulate your question with whatever the situation is at
that time and post it to a new thread.

Have a great time.

 

[Jack Isaacs]

Marshal
Just realised I haven't let you know that your suggested method worked a
treat!
Many thanks for your help - greatly appreciated.
Cheers
Les

Marshall
Many thanks for that explanation - I get it now ... I think!
I'll let you know how I get on with using your suggestion on Thursday.
Cheers
Les

Although I am very poor with keyboards, those quotes were
intentional. Quotes around the name are required because a
name by itself would not result in a legal Where clause.
I.e. after the concatenation, the WhereCondition string
without the quotes vould be:
[Name] = E Smith-Jones
when you need it to be
[Name] = "E Smith-Jones"
so Access can figure out which parts of the string are
keywords or syntax characters and which parts are field
names and data.

The reason for the "extra" quotes is because you need to use
two quotes to represent one quote when it is inside other
quotes. I.e. "[Name] =""" results in the string [Name] ="
which is then concatenated to the name from the combo box
and the final quote character.

Don't beat yourself up over the quoted quote issue, it can
be confusing until you eventually wrap your head around the
concepts. An analoguous situation could occur in normal
written English if you used a sentence like:
Bob said, "O'Riley's list is "", ,, ,. and '.", right?".
which can be at least a little difficult for a human to
parse.
--
Marsh
MVP [MS Access]

Is there a 'typo' in you suggested code -
WhereCondition:= "[Name] =""" & Me.combo56 & """"
Should there be the same number of quotes before and after:
& Me.combo56 &
Why are any quotes needed there anyway?


Leslie Isaacs wrote:
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));


"Marshall Barton" wrote
Try basing the report on the x confirmed table and applying
the filtering in the OpenReport method's WhereCondition
argument.

The button for single name would use code like:

DoCmd.OpenReport "the report", acviewPreview, _
WhereCondition:= "[Name] =""" & Me.combo56 & """"

and the one for all names could be just:

DoCmd.OpenReport "the report", acviewPreview