Criteria

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hello,

I have a form with an option group. The option group values are 1, 2 or 3.

I have a table that has the option group values 1, 2 and 3 and the value in
a second column.

I have the fields linked in the query and I can get my query to work fine
with this. However I now want to use do the same but rather than for a
specific number I want to say >50 or >30. I have tried adding in the fields
in the criteria as follows:

[QueryName]![QueryCriteria]

However when I run the query it asks me for the value of this. The value is
there and I can also type it in when prompted but I dont understand why the
query cant pick this up itself.

Any suggestions?

Thanks in advance,

Martin
 
Martin said:
Hello,

I have a form with an option group. The option group values are 1, 2
or 3.

I have a table that has the option group values 1, 2 and 3 and the
value in a second column.

I have the fields linked in the query and I can get my query to work
fine with this. However I now want to use do the same but rather
than for a specific number I want to say >50 or >30. I have tried
adding in the fields in the criteria as follows:

[QueryName]![QueryCriteria]

However when I run the query it asks me for the value of this. The
value is there and I can also type it in when prompted but I dont
understand why the query cant pick this up itself.
Click to expand...

I can't really make sense of this. Perhaps if you posted the SQL of your
query someone would have a better chance to see what you are doing. I know
that it was probably just an example but I can't see how
[QueryName]![QueryCriteria] would EVER work when placed in the Criteria row
of a query.
 
Sorry.

Here is the table that has the option group criteria:

OptionGroup Value QueryCriteria
1 30.00
2 50.00
3 0.00

Here is the SQL that gets the option group value from the form and has the
value I was to pass to the query:


SELECT [tblOption Group: Distance].[OptionGroup Value], [tblOption Group:
Distance].QueryCriteria
FROM [tblOption Group: Distance]
WHERE ((([tblOption Group: Distance].[OptionGroup Value])=[Forms]![Main
Menu]![DistanceOption]));

So, if the option group value is 1 then the value I want to pass to the
query is 30.00.

In the query I want to say something like "give me all the values that are
30.00. Here is the full SQL of that query:
Click to expand...

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section 02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE ((([Section 02:01].[RM Distance (straight line in miles)])>[Section
02:11]![QueryCriteria]));

When I run this SQL it asks me to enter the parameter value of Section
02:11!QueryCriteria.

Thank you.

Martin


Rick Brandt said:
Martin said:
Hello,

I have a form with an option group. The option group values are 1, 2
or 3.

I have a table that has the option group values 1, 2 and 3 and the
value in a second column.

I have the fields linked in the query and I can get my query to work
fine with this. However I now want to use do the same but rather
than for a specific number I want to say >50 or >30. I have tried
adding in the fields in the criteria as follows:

[QueryName]![QueryCriteria]

However when I run the query it asks me for the value of this. The
value is there and I can also type it in when prompted but I dont
understand why the query cant pick this up itself.
Click to expand...

I can't really make sense of this. Perhaps if you posted the SQL of your
query someone would have a better chance to see what you are doing. I know
that it was probably just an example but I can't see how
[QueryName]![QueryCriteria] would EVER work when placed in the Criteria row
of a query.
Click to expand...
 
Instead of going to all the trouble why not access the value directly

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section
02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE [Section 02:01].[RM Distance (straight line in miles)] >
DLookup("QueryCriteria","[tblOption Group: Distance]","[OptionGroup
Value]="& [Forms]![Main Menu]![DistanceOption])

Or even simpler if the values associated with the OptionGroup don't
change is

SELECT [Section 02:01].Region, [Section 02:01].[AD Area]
, [Section 02:01].[AD Name]
, [Section 02:01].[RM Name]
, [Section 02:01].[Portfolio Code]
, [Section 02:01].[RM Postcode]
, [Section 02:01].[Customer Name]
, [Section 02:01].[Customer ID]
, [Section 02:01].Brand
, [Section 02:01].Income
, [Section 02:01].[Customer Postcode]
, [Section 02:01].[Risk Band]
, [Section 02:01].[Debit Balance]
, [Section 02:01].[OD Limit]
, [Section 02:01].[Loan Balance]
, [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)]
, [Section 02:01].[RM Distance (straight line in miles)]
, [Section 02:01].[Nearest RM Region]
, [Section 02:01].[Nearest RM AD Area]
, [Section 02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10]
ON [Section 02:01].[Risk Band] = [Section 02:10].QueryCriteria
WHERE [Section 02:01].[RM Distance (straight line in miles)] >
Choose([Forms]![Main Menu]![DistanceOption],30,50,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Sorry.

Here is the table that has the option group criteria:

OptionGroup Value QueryCriteria
1 30.00
2 50.00
3 0.00

Here is the SQL that gets the option group value from the form and has the
value I was to pass to the query:


SELECT [tblOption Group: Distance].[OptionGroup Value], [tblOption Group:
Distance].QueryCriteria
FROM [tblOption Group: Distance]
WHERE ((([tblOption Group: Distance].[OptionGroup Value])=[Forms]![Main
Menu]![DistanceOption]));

So, if the option group value is 1 then the value I want to pass to the
query is 30.00.

In the query I want to say something like "give me all the values that are
30.00. Here is the full SQL of that query:
Click to expand...

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section 02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE ((([Section 02:01].[RM Distance (straight line in miles)])>[Section
02:11]![QueryCriteria]));

When I run this SQL it asks me to enter the parameter value of Section
02:11!QueryCriteria.

Thank you.

Martin


Rick Brandt said:
Martin said:
Hello,

I have a form with an option group. The option group values are 1, 2
or 3.

I have a table that has the option group values 1, 2 and 3 and the
value in a second column.

I have the fields linked in the query and I can get my query to work
fine with this. However I now want to use do the same but rather
than for a specific number I want to say >50 or >30. I have tried
adding in the fields in the criteria as follows:

[QueryName]![QueryCriteria]

However when I run the query it asks me for the value of this. The
value is there and I can also type it in when prompted but I dont
understand why the query cant pick this up itself.
Click to expand...
I can't really make sense of this. Perhaps if you posted the SQL of your
query someone would have a better chance to see what you are doing. I know
that it was probably just an example but I can't see how
[QueryName]![QueryCriteria] would EVER work when placed in the Criteria row
of a query.
Click to expand...
Click to expand...
 
Thank you.

I have tried both examples and the query runs but no records are returned.
I can understand the logic behind both so thank you for the insight, not sure
why the records are not being returned however

John Spencer said:
Instead of going to all the trouble why not access the value directly

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section
02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE [Section 02:01].[RM Distance (straight line in miles)] >
DLookup("QueryCriteria","[tblOption Group: Distance]","[OptionGroup
Value]="& [Forms]![Main Menu]![DistanceOption])

Or even simpler if the values associated with the OptionGroup don't
change is

SELECT [Section 02:01].Region, [Section 02:01].[AD Area]
, [Section 02:01].[AD Name]
, [Section 02:01].[RM Name]
, [Section 02:01].[Portfolio Code]
, [Section 02:01].[RM Postcode]
, [Section 02:01].[Customer Name]
, [Section 02:01].[Customer ID]
, [Section 02:01].Brand
, [Section 02:01].Income
, [Section 02:01].[Customer Postcode]
, [Section 02:01].[Risk Band]
, [Section 02:01].[Debit Balance]
, [Section 02:01].[OD Limit]
, [Section 02:01].[Loan Balance]
, [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)]
, [Section 02:01].[RM Distance (straight line in miles)]
, [Section 02:01].[Nearest RM Region]
, [Section 02:01].[Nearest RM AD Area]
, [Section 02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10]
ON [Section 02:01].[Risk Band] = [Section 02:10].QueryCriteria
WHERE [Section 02:01].[RM Distance (straight line in miles)] >
Choose([Forms]![Main Menu]![DistanceOption],30,50,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Sorry.

Here is the table that has the option group criteria:

OptionGroup Value QueryCriteria
1 30.00
2 50.00
3 0.00

Here is the SQL that gets the option group value from the form and has the
value I was to pass to the query:


SELECT [tblOption Group: Distance].[OptionGroup Value], [tblOption Group:
Distance].QueryCriteria
FROM [tblOption Group: Distance]
WHERE ((([tblOption Group: Distance].[OptionGroup Value])=[Forms]![Main
Menu]![DistanceOption]));

So, if the option group value is 1 then the value I want to pass to the
query is 30.00.

In the query I want to say something like "give me all the values that are
30.00. Here is the full SQL of that query:
Click to expand...

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section 02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE ((([Section 02:01].[RM Distance (straight line in miles)])>[Section
02:11]![QueryCriteria]));

When I run this SQL it asks me to enter the parameter value of Section
02:11!QueryCriteria.

Thank you.

Martin


Rick Brandt said:
Martin wrote:
Hello,

I have a form with an option group. The option group values are 1, 2
or 3.

I have a table that has the option group values 1, 2 and 3 and the
value in a second column.

I have the fields linked in the query and I can get my query to work
fine with this. However I now want to use do the same but rather
than for a specific number I want to say >50 or >30. I have tried
adding in the fields in the criteria as follows:

[QueryName]![QueryCriteria]

However when I run the query it asks me for the value of this. The
value is there and I can also type it in when prompted but I dont
understand why the query cant pick this up itself.
I can't really make sense of this. Perhaps if you posted the SQL of your
query someone would have a better chance to see what you are doing. I know
that it was probably just an example but I can't see how
[QueryName]![QueryCriteria] would EVER work when placed in the Criteria row
of a query.
Click to expand...
Click to expand...
Click to expand...
 
Hi - I decided to finish for today on this as it was driving me nuts. I
compacted the database and tried one more time and it works perfectly. I
really dont understand what impact compacting did but it worked.

Thank you for your help.

Martin

Martin said:
Thank you.

I have tried both examples and the query runs but no records are returned.
I can understand the logic behind both so thank you for the insight, not sure
why the records are not being returned however

John Spencer said:
Instead of going to all the trouble why not access the value directly

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section
02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE [Section 02:01].[RM Distance (straight line in miles)] >
DLookup("QueryCriteria","[tblOption Group: Distance]","[OptionGroup
Value]="& [Forms]![Main Menu]![DistanceOption])

Or even simpler if the values associated with the OptionGroup don't
change is

SELECT [Section 02:01].Region, [Section 02:01].[AD Area]
, [Section 02:01].[AD Name]
, [Section 02:01].[RM Name]
, [Section 02:01].[Portfolio Code]
, [Section 02:01].[RM Postcode]
, [Section 02:01].[Customer Name]
, [Section 02:01].[Customer ID]
, [Section 02:01].Brand
, [Section 02:01].Income
, [Section 02:01].[Customer Postcode]
, [Section 02:01].[Risk Band]
, [Section 02:01].[Debit Balance]
, [Section 02:01].[OD Limit]
, [Section 02:01].[Loan Balance]
, [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)]
, [Section 02:01].[RM Distance (straight line in miles)]
, [Section 02:01].[Nearest RM Region]
, [Section 02:01].[Nearest RM AD Area]
, [Section 02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10]
ON [Section 02:01].[Risk Band] = [Section 02:10].QueryCriteria
WHERE [Section 02:01].[RM Distance (straight line in miles)] >
Choose([Forms]![Main Menu]![DistanceOption],30,50,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Sorry.

Here is the table that has the option group criteria:

OptionGroup Value QueryCriteria
1 30.00
2 50.00
3 0.00

Here is the SQL that gets the option group value from the form and has the
value I was to pass to the query:


SELECT [tblOption Group: Distance].[OptionGroup Value], [tblOption Group:
Distance].QueryCriteria
FROM [tblOption Group: Distance]
WHERE ((([tblOption Group: Distance].[OptionGroup Value])=[Forms]![Main
Menu]![DistanceOption]));

So, if the option group value is 1 then the value I want to pass to the
query is 30.00.

In the query I want to say something like "give me all the values that are
30.00. Here is the full SQL of that query:

SELECT [Section 02:01].Region, [Section 02:01].[AD Area], [Section
02:01].[AD Name], [Section 02:01].[RM Name], [Section 02:01].[Portfolio
Code], [Section 02:01].[RM Postcode], [Section 02:01].[Customer Name],
[Section 02:01].[Customer ID], [Section 02:01].Brand, [Section 02:01].Income,
[Section 02:01].[Customer Postcode], [Section 02:01].[Risk Band], [Section
02:01].[Debit Balance], [Section 02:01].[OD Limit], [Section 02:01].[Loan
Balance], [Loan Balance]+IIf([Debit Balance]>[OD Limit],[Debit Balance],[OD
Limit]) AS [Total Loan Balance & OD Limit (or Debit balance if larger)],
[Section 02:01].[RM Distance (straight line in miles)], [Section
02:01].[Nearest RM Region], [Section 02:01].[Nearest RM AD Area], [Section
02:01].[Nearest RM Postcode]
FROM [Section 02:01] INNER JOIN [Section 02:10] ON [Section 02:01].[Risk
Band] = [Section 02:10].QueryCriteria
WHERE ((([Section 02:01].[RM Distance (straight line in miles)])>[Section
02:11]![QueryCriteria]));

When I run this SQL it asks me to enter the parameter value of Section
02:11!QueryCriteria.

Thank you.

Martin


:

Martin wrote:
Hello,

I have a form with an option group. The option group values are 1, 2
or 3.

I have a table that has the option group values 1, 2 and 3 and the
value in a second column.

I have the fields linked in the query and I can get my query to work
fine with this. However I now want to use do the same but rather
than for a specific number I want to say >50 or >30. I have tried
adding in the fields in the criteria as follows:

[QueryName]![QueryCriteria]

However when I run the query it asks me for the value of this. The
value is there and I can also type it in when prompted but I dont
understand why the query cant pick this up itself.
I can't really make sense of this. Perhaps if you posted the SQL of your
query someone would have a better chance to see what you are doing. I know
that it was probably just an example but I can't see how
[QueryName]![QueryCriteria] would EVER work when placed in the Criteria row
of a query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top