Parameter form between 2 text fields

[gmazza via AccessMonster.com]

Hi there,
I have a parameter form that the user makes a choice between 2 countries and
the results are displayed in a form between whatever countries chosen.
It works fine and everything.
I added a UNION so the word ALL shows up in the countries list but I can't
get the ReportFilter part right for when the user selects ALL.

Can anyone help?

My query for the report looks to the parameter form and returns all countries
between the 2 countries chosen and that all works. I'm just missing something
for when ALL is chosen and what to do.
Thanks in advance!

 

[John W. Vinson]

Hi there,
I have a parameter form that the user makes a choice between 2 countries and
the results are displayed in a form between whatever countries chosen.
It works fine and everything.
I added a UNION so the word ALL shows up in the countries list but I can't
get the ReportFilter part right for when the user selects ALL.

Can anyone help?

My query for the report looks to the parameter form and returns all countries
between the 2 countries chosen and that all works. I'm just missing something
for when ALL is chosen and what to do.
Thanks in advance!

Correct the problem with your code. If you would like help doing so, post the
code (the code that launches the report and the SQL of the query).

 

[Douglas J. Steele]

You need something like

((MyField = Forms!NameOfForm!NameOfControl) OR
(Forms!NameOfForm!NameOfControl = "All"))

 

[gmazza via AccessMonster.com]

Here is the query for my report:
SELECT zcCountry.CountryDesc, TMDetail.Trademark, TMDetail.FilingNo, TMDetail.
Class, TMStatus.StatusDesc, TMDetail.LawOffice
FROM zcCountry INNER JOIN (TMStatus INNER JOIN TMDetail ON TMStatus.StatusId
= TMDetail.StatusId) ON zcCountry.CountryCode = TMDetail.CountryCode
WHERE (((zcCountry.CountryDesc) Between [forms]![SelectCountry]!
[txtCountryFrom] And [forms]![SelectCountry]![txtCountryTo]) AND ((TMStatus.
[StatusDesc])=[Forms]![SelectCountry]![cboStatus]));

Here is the Open event of the report:
DoCmd.OpenForm "SelectCountry", , , , , acDialog

If Forms![SelectCountry]![rptcancel] = True Then
Cancel = True
End If

When you click Ok on the parameter form after making your choices of ALL,
here is the code behind that:
' This field is checked from calling report to see if user has
aborted report.
Me.rptcancel = False
' This will return control to calling report, which will close this
form on exit.
Me.Visible = False


Let me know if you need anything else.
Thanks!!

Hi there,
I have a parameter form that the user makes a choice between 2 countries and

[quoted text clipped - 9 lines]

for when ALL is chosen and what to do.
Thanks in advance!

Correct the problem with your code. If you would like help doing so, post the
code (the code that launches the report and the SQL of the query).

 

[John W. Vinson]

Here is the query for my report:
SELECT zcCountry.CountryDesc, TMDetail.Trademark, TMDetail.FilingNo, TMDetail.
Class, TMStatus.StatusDesc, TMDetail.LawOffice
FROM zcCountry INNER JOIN (TMStatus INNER JOIN TMDetail ON TMStatus.StatusId
= TMDetail.StatusId) ON zcCountry.CountryCode = TMDetail.CountryCode
WHERE (((zcCountry.CountryDesc) Between [forms]![SelectCountry]!
[txtCountryFrom] And [forms]![SelectCountry]![txtCountryTo]) AND ((TMStatus.
[StatusDesc])=[Forms]![SelectCountry]![cboStatus]));

Here is the Open event of the report:
DoCmd.OpenForm "SelectCountry", , , , , acDialog

If Forms![SelectCountry]![rptcancel] = True Then
Cancel = True
End If

When you click Ok on the parameter form after making your choices of ALL,
here is the code behind that:
' This field is checked from calling report to see if user has
aborted report.
Me.rptcancel = False
' This will return control to calling report, which will close this
form on exit.
Me.Visible = False

There is absolutely NOTHING in this code that looks for the value "ALL" in the
combo box! As it is, it will (I presume) treat ALL as if it were a country
name, between Algeria and American Samoa?

Try changing your query to

SELECT zcCountry.CountryDesc, TMDetail.Trademark, TMDetail.FilingNo, TMDetail.
Class, TMStatus.StatusDesc, TMDetail.LawOffice
FROM zcCountry INNER JOIN (TMStatus INNER JOIN TMDetail ON TMStatus.StatusId
= TMDetail.StatusId) ON zcCountry.CountryCode = TMDetail.CountryCode
WHERE
((((zcCountry.CountryDesc) Between [forms]![SelectCountry]!
[txtCountryFrom] And [forms]![SelectCountry]![txtCountryTo])
OR [forms]![SelectCountry]![txtCountryFrom] = "ALL") AND
((TMStatus.[StatusDesc])=[Forms]![SelectCountry]![cboStatus]));

 

[gmazza via AccessMonster.com]

Thanks for your help John, it works perfect.
I thought the ALL criteria had to be in my code, not my query.

Here is the query for my report:
SELECT zcCountry.CountryDesc, TMDetail.Trademark, TMDetail.FilingNo, TMDetail.

[quoted text clipped - 20 lines]

form on exit.
Me.Visible = False

There is absolutely NOTHING in this code that looks for the value "ALL" in the
combo box! As it is, it will (I presume) treat ALL as if it were a country
name, between Algeria and American Samoa?

Try changing your query to

SELECT zcCountry.CountryDesc, TMDetail.Trademark, TMDetail.FilingNo, TMDetail.
Class, TMStatus.StatusDesc, TMDetail.LawOffice
FROM zcCountry INNER JOIN (TMStatus INNER JOIN TMDetail ON TMStatus.StatusId
= TMDetail.StatusId) ON zcCountry.CountryCode = TMDetail.CountryCode
WHERE
((((zcCountry.CountryDesc) Between [forms]![SelectCountry]!
[txtCountryFrom] And [forms]![SelectCountry]![txtCountryTo])
OR [forms]![SelectCountry]![txtCountryFrom] = "ALL") AND
((TMStatus.[StatusDesc])=[Forms]![SelectCountry]![cboStatus]));