Using combo box selection to supply criteria for query

E

Erick C

I am hoping someone can help me out with a problem that I am having.
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary. I made a form that has six
different combo boxes along the top. The data sources for the combo
boxes are different, some have names that I manually added. Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).

In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part. 5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.

It was pretty messed up setting up the query, since I had to set up a
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria. I can post my SQL view, but it is around 3 pages long whan
I copy it to Word

Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.

Erick
 
K

KARL DEWEY

Try this --
Like [Forms]![YourFormName]![Combo22] & "*"

If nothing is selected in the combo then it is the same as selecing all.
 
E

Erick C

No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"

And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.


Try this --
      Like [Forms]![YourFormName]![Combo22] & "*"

If nothing is selected in the combo then it is the same as selecing all.

--
Build a little, test a little.



Erick C said:
I am hoping someone can help me out with a problem that I am having.
I am setting up a database that will serve as an archive for our
reporting results.  I am jsut starting to get everything together, so
I can modify anything if necessary.  I made a form that has six
different combo boxes along the top.  The data sources for the combo
boxes are different, some have names that I manually added.  Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).
Click to expand...
In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part.  5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.
Click to expand...
It was pretty messed up setting up the query, since I had to set up a
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria.  I can post my SQL view, but it is around 3 pages long whan
I copy it to Word
Click to expand...
Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.
Click to expand...
Erick- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
M

Mr. B

Try first assigning the value from your combo box to a variable. I realize
that I will work using just the reference to the control, but when you
already have an issue with the correct syntax it makes it a little simpler to
just declare a variable and assign the value from the combo box to it.

dim strRegion as String

strRegion = [Forms]![Main Form]![Region Select]

then use the variable in your sql statement as:

Like """ & strRegion & "*""


Later if you want to come back and just use the reference to the control,
you still can do that.
-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm


Erick C said:
No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"

And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.


Try this --
Like [Forms]![YourFormName]![Combo22] & "*"

If nothing is selected in the combo then it is the same as selecing all.

--
Build a little, test a little.



Erick C said:
I am hoping someone can help me out with a problem that I am having.
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary. I made a form that has six
different combo boxes along the top. The data sources for the combo
boxes are different, some have names that I manually added. Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).
Click to expand...
In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part. 5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.
Click to expand...
It was pretty messed up setting up the query, since I had to set up a
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria. I can post my SQL view, but it is around 3 pages long whan
I copy it to Word
Click to expand...
Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.
Click to expand...
Erick- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
S

Steve Sanford

Hi Erick,
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary.
Click to expand...

You should read "The Ten Commandments of Access" at
http://mvps.org/access/tencommandments.htm

Especially #3 ....the last part about spaces in object names.


boxes are different, some have names that I manually added. Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).
Click to expand...

Do you know about "DISTINCT" in queries? If you have a table of cities and
states, selecting just the states will give you multiple states in a query.
If you use "Distinct" ("Select Distinct StateName FROM tblLocations") it will
return each state, once.


The "Docmd.OpenReport" command has a "WHERE" clause argument:

Docmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode,
OpenArgs)


My suggestion (and I use this) is to build a where clause "on-the-fly".

When you click on the button to open the report, you check each of the combo
boxes, and if not null, add the condition to a where string. When you have
checked all of the combo boxes, the Where string is built and you open/print
the report.

The report should have a basic (saved) query as the record source. Open the
report with the Where clause (not the filter clause). Maybe not as clean as a
pure query solution, but it works.

If you want a sample of the code, post the query for a report (without
criteria) and the name of a few combo boxes and I will give you a few lines..

HTH
 
K

KARL DEWEY

What part did not work?
Not return all records when no selection was made?
Post the query SQL.
--
Build a little, test a little.


Erick C said:
No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"

And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.


Try this --
Like [Forms]![YourFormName]![Combo22] & "*"

If nothing is selected in the combo then it is the same as selecing all.

--
Build a little, test a little.



Erick C said:
I am hoping someone can help me out with a problem that I am having.
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary. I made a form that has six
different combo boxes along the top. The data sources for the combo
boxes are different, some have names that I manually added. Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).
Click to expand...
In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part. 5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.
Click to expand...
It was pretty messed up setting up the query, since I had to set up a
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria. I can post my SQL view, but it is around 3 pages long whan
I copy it to Word
Click to expand...
Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.
Click to expand...
Erick- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
E

Erick C

Mr. B,
Sorry, I am still learning SQL and VBA, I don't think that I made the
module correctly. After I added the module none of the sql code
worked. Did I not need to make a module? Was the code supposed to go
somewhere else?

Steve and Karl -
Below is the SQL code that I currently have.
The form contains 5 combo boxes; SBT Account, Region, ADM, Month, Year
I named each of the combo boxes with the same names, just with
"Select" at the end, as you will see in the code.
I can leave all combo boxes blank and retrieve all 33 records, If I
use any one of the combo boxes individually, except for Region, the
query works fine. If I only make a selection in the Region combo box
then the qurey retrieves all 33 records instead of filtering by my
selection.

Thank you all again for the help.

Here is the code, sorry it is very long:

SELECT [ARCHIVE - ADM Summary].ADM, [ARCHIVE - ADM Summary].Region,
[ARCHIVE - ADM Summary].[SBT Account], [ARCHIVE - ADM Summary].Month,
[ARCHIVE - ADM Summary].Year, [ARCHIVE - ADM Summary].[# Of Stores
Counted], [ARCHIVE - ADM Summary].[ADM Cumulative Shrink %]
FROM [ARCHIVE - ADM Summary]
WHERE ((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM
Select]) AND (([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]!
[Region Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=[Forms]!
[Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM Summary].Month)=
[Forms]![Main Form]![Month Select]) AND (([ARCHIVE - ADM Summary].Year)
=[Forms]![Main Form]![Year Select])) OR ((([ARCHIVE - ADM
Summary].Region)=[Forms]![Main Form]![Region Select]) AND (([ARCHIVE -
ADM Summary].[SBT Account])=[Forms]![Main Form]![SBT Account Select])
AND (([ARCHIVE - ADM Summary].Month)=[Forms]![Main Form]![Month
Select]) AND (([ARCHIVE - ADM Summary].Year)=[Forms]![Main Form]![Year
Select]) AND ((IsNull([Forms]![Main Form]![ADM Select]))<>False)) OR
((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]) AND
(([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]![Region Select])
AND (([ARCHIVE - ADM Summary].Month)=[Forms]![Main Form]![Month
Select]) AND (([ARCHIVE - ADM Summary].Year)=[Forms]![Main Form]![Year
Select]) AND ((IsNull([Forms]![Main Form]![SBT Account Select]))
<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM
Select]) AND (([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]!
[Region Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=[Forms]!
[Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM Summary].Year)=
[Forms]![Main Form]![Year Select]) AND ((IsNull([Forms]![Main Form]!
[Month Select]))<>False)) OR ((([ARCHIVE - ADM Summary].Region)=
[Forms]![Main Form]![Region Select]) AND (([ARCHIVE - ADM Summary].
[SBT Account])=[Forms]![Main Form]![SBT Account Select]) AND
(([ARCHIVE - ADM Summary].Year)=[Forms]![Main Form]![Year Select]) AND
((IsNull([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull
([Forms]![Main Form]![Month Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].Region)=[Forms]![Main Form]![Region Select]) AND (([ARCHIVE -
ADM Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Month Select]))<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]!
[Main Form]![ADM Select]) AND (([ARCHIVE - ADM Summary].Region)=
[Forms]![Main Form]![Region Select]) AND (([ARCHIVE - ADM Summary].
[SBT Account])=[Forms]![Main Form]![SBT Account Select]) AND
(([ARCHIVE - ADM Summary].Month)=[Forms]![Main Form]![Month Select])
AND ((IsNull([Forms]![Main Form]![Year Select]))<>False)) OR
((([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]![Region
Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=[Forms]![Main
Form]![SBT Account Select]) AND (([ARCHIVE - ADM Summary].Month)=
[Forms]![Main Form]![Month Select]) AND ((IsNull([Forms]![Main Form]!
[ADM Select]))<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))
<>False)) OR ((([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]!
[Region Select]) AND (([ARCHIVE - ADM Summary].Month)=[Forms]![Main
Form]![Month Select]) AND ((IsNull([Forms]![Main Form]![ADM Select]))
<>False) AND ((IsNull([Forms]![Main Form]![SBT Account Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))<>False)) OR
((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]) AND
(([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]![Region Select])
AND (([ARCHIVE - ADM Summary].[SBT Account])=[Forms]![Main Form]![SBT
Account Select]) AND ((IsNull([Forms]![Main Form]![Month Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))<>False)) OR
((([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]![Region
Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=[Forms]![Main
Form]![SBT Account Select]) AND ((IsNull([Forms]![Main Form]![ADM
Select]))<>False) AND ((IsNull([Forms]![Main Form]![Month Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))<>False)) OR
((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]) AND
(([ARCHIVE - ADM Summary].Region)=[Forms]![Main Form]![Region Select])
AND ((IsNull([Forms]![Main Form]![SBT Account Select]))<>False) AND
((IsNull([Forms]![Main Form]![Month Select]))<>False) AND ((IsNull
([Forms]![Main Form]![Year Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].Region)=[Forms]![Main Form]![Region Select]) AND (([ARCHIVE -
ADM Summary].Month)=[Forms]![Main Form]![Month Select]) AND (([ARCHIVE
- ADM Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![SBT Account Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].ADM)=[Forms]![Main Form]![ADM Select]) AND (([ARCHIVE - ADM
Summary].Region)=[Forms]![Main Form]![Region Select]) AND (([ARCHIVE -
ADM Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull
([Forms]![Main Form]![SBT Account Select]))<>False) AND ((IsNull
([Forms]![Main Form]![Month Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].ADM)=[Forms]![Main Form]![ADM Select]) AND (([ARCHIVE - ADM
Summary].Region)=[Forms]![Main Form]![Region Select]) AND (([ARCHIVE -
ADM Summary].Month)=[Forms]![Main Form]![Month Select]) AND ((IsNull
([Forms]![Main Form]![SBT Account Select]))<>False) AND ((IsNull
([Forms]![Main Form]![Year Select]))<>False)) OR (((IsNull([Forms]!
[Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]![Month
Select]))<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))
<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM
Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=[Forms]![Main
Form]![SBT Account Select]) AND (([ARCHIVE - ADM Summary].Month)=
[Forms]![Main Form]![Month Select]) AND (([ARCHIVE - ADM Summary].Year)
=[Forms]![Main Form]![Year Select]) AND ((IsNull([Forms]![Main Form]!
[Region Select]))<>False)) OR ((([ARCHIVE - ADM Summary].[SBT Account])
=[Forms]![Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM
Summary].Month)=[Forms]![Main Form]![Month Select]) AND (([ARCHIVE -
ADM Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![Region Select]))<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=
[Forms]![Main Form]![ADM Select]) AND (([ARCHIVE - ADM Summary].Month)=
[Forms]![Main Form]![Month Select]) AND (([ARCHIVE - ADM Summary].Year)
=[Forms]![Main Form]![Year Select]) AND ((IsNull([Forms]![Main Form]!
[SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Region Select]))<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]!
[Main Form]![ADM Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=
[Forms]![Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM
Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull([Forms]!
[Main Form]![Month Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Region Select]))<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]!
[Main Form]![ADM Select]) AND (([ARCHIVE - ADM Summary].[SBT Account])=
[Forms]![Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM
Summary].Month)=[Forms]![Main Form]![Month Select]) AND ((IsNull
([Forms]![Main Form]![Year Select]))<>False) AND ((IsNull([Forms]!
[Main Form]![Region Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].Month)=[Forms]![Main Form]![Month Select]) AND (([ARCHIVE -
ADM Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Region Select]))<>False)) OR ((([ARCHIVE - ADM Summary].[SBT Account])
=[Forms]![Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM
Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull([Forms]!
[Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Month Select]))<>False) AND ((IsNull([Forms]![Main Form]![Region
Select]))<>False)) OR ((([ARCHIVE - ADM Summary].[SBT Account])=
[Forms]![Main Form]![SBT Account Select]) AND (([ARCHIVE - ADM
Summary].Month)=[Forms]![Main Form]![Month Select]) AND ((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![Year Select]))<>False) AND ((IsNull([Forms]![Main Form]![Region
Select]))<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main
Form]![ADM Select]) AND (([ARCHIVE - ADM Summary].Year)=[Forms]![Main
Form]![Year Select]) AND ((IsNull([Forms]![Main Form]![SBT Account
Select]))<>False) AND ((IsNull([Forms]![Main Form]![Month Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Region Select]))<>False))
OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select])
AND (([ARCHIVE - ADM Summary].Month)=[Forms]![Main Form]![Month
Select]) AND ((IsNull([Forms]![Main Form]![SBT Account Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))<>False) AND
((IsNull([Forms]![Main Form]![Region Select]))<>False)) OR ((([ARCHIVE
- ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]) AND (([ARCHIVE -
ADM Summary].[SBT Account])=[Forms]![Main Form]![SBT Account Select])
AND ((IsNull([Forms]![Main Form]![Month Select]))<>False) AND ((IsNull
([Forms]![Main Form]![Year Select]))<>False) AND ((IsNull([Forms]!
[Main Form]![Region Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].Year)=[Forms]![Main Form]![Year Select]) AND ((IsNull([Forms]!
[Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]![Month
Select]))<>False) AND ((IsNull([Forms]![Main Form]![Region Select]))
<>False)) OR ((([ARCHIVE - ADM Summary].Month)=[Forms]![Main Form]!
[Month Select]) AND ((IsNull([Forms]![Main Form]![ADM Select]))
<>False) AND ((IsNull([Forms]![Main Form]![SBT Account Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))<>False) AND
((IsNull([Forms]![Main Form]![Region Select]))<>False)) OR ((([ARCHIVE
- ADM Summary].[SBT Account])=[Forms]![Main Form]![SBT Account
Select]) AND ((IsNull([Forms]![Main Form]![ADM Select]))<>False) AND
((IsNull([Forms]![Main Form]![Month Select]))<>False) AND ((IsNull
([Forms]![Main Form]![Year Select]))<>False) AND ((IsNull([Forms]!
[Main Form]![Region Select]))<>False)) OR ((([ARCHIVE - ADM
Summary].Region)=[Forms]![Main Form]![Region Select]) AND ((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Month Select]))<>False) AND ((IsNull([Forms]![Main Form]![Year
Select]))<>False)) OR ((([ARCHIVE - ADM Summary].ADM)=[Forms]![Main
Form]![ADM Select]) AND ((IsNull([Forms]![Main Form]![SBT Account
Select]))<>False) AND ((IsNull([Forms]![Main Form]![Month Select]))
<>False) AND ((IsNull([Forms]![Main Form]![Year Select]))<>False) AND
((IsNull([Forms]![Main Form]![Region Select]))<>False)) OR (((IsNull
([Forms]![Main Form]![ADM Select]))<>False) AND ((IsNull([Forms]![Main
Form]![SBT Account Select]))<>False) AND ((IsNull([Forms]![Main Form]!
[Month Select]))<>False) AND ((IsNull([Forms]![Main Form]![Year
Select]))<>False) AND ((IsNull([Forms]![Main Form]![Region Select]))
<>False));



What part did not work?
Not return all records when no selection was made?
Post the query SQL.
--
Build a little, test a little.



Erick C said:
No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"
Click to expand...
And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.
Click to expand...
Try this --
Like [Forms]![YourFormName]![Combo22] & "*"
If nothing is selected in the combo then it is the same as selecing all.
--
Build a little, test a little.
:
I am hoping someone can help me out with a problem that I am having..
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary. I made a form that has six
different combo boxes along the top. The data sources for the combo
boxes are different, some have names that I manually added. Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).
In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part. 5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.
It was pretty messed up setting up the query, since I had to set upa
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria. I can post my SQL view, but it is around 3 pages long whan
I copy it to Word
Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.
Erick- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...



What part did not work?
Not return all records when no selection was made?
Post the query SQL.
--
Build a little, test a little.



Erick C said:
No luck.
I even tried putting the wildcard on both ends and it still did not
work:
Like "*" & [Forms]![Main Form]![Region Select] & "*"
and I tried:
Like [Forms]![Main Form]![Region Select] & "*"
Click to expand...
And the Region is only a one letter designation coming from the select
query, so there is not a lot of stuff that is being brought into the
combo box, just single letters.
Click to expand...
Try this --
      Like [Forms]![YourFormName]![Combo22] & "*"
If nothing is selected in the combo then it is the same as selecing all.
--
Build a little, test a little.
:
I am hoping someone can help me out with a problem that I am having..
I am setting up a database that will serve as an archive for our
reporting results.  I am jsut starting to get everything together, so
I can modify anything if necessary.  I made a form that has six
different combo boxes along the top.  The data sources for the combo
boxes are different, some have names that I manually added.  Other
boxes use select queries that I set up pulling data from certain
tables as data sources, since using the tables themselves yielded
duplicate results (the same region is listed multiple times in one
table).
In the end I want to set up buttons for various reports, and the
results will be driven by the combo box selections made on the forms.
I tried setting up a test select query, and it works for the most
part.  5 out of the 6 combo boxes will yield results if they are
selected by themselves, but one combo box will only filter the query
results when it is used in conjunction with another combo box.
It was pretty messed up setting up the query, since I had to set upa
line of criteria for each possible combination that the combo boxes
could be selected, or not selected, meaning I have 32 rows of
criteria.  I can post my SQL view, but it is around 3 pages long whan
I copy it to Word
Any help I can get, or any suggestions on how I can set up my filters
so they will work better, more efficiently, I would be very grateful.
Erick- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
S

Steve Sanford

Eric,
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary.
Click to expand...

I stress again: using spaces in object names just causes you headaches.
instead of using

[ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]

it would be better to use

[ARCHIVE_ADM_Summary].ADM = [Forms]![Main_Form]![ADM_Select]

or

[ArchiveADMSummary].ADM = [Forms]![MainForm]![ADMSelect]

Both are still very easy to read. And no one but the programmer will be
seeing the names.

See #3 in the "The Ten Commandments of Access" at

http://www.accessmvp.com/djsteele/AccessTenCommandments.html


(also don't use special chars (!@#$%^&*><=-) except the underscore (_)


Also be aware that "Year", "Date" and "Month" are reserved words (and
functions) in Access. You will find a list of reserved word for Access and
SQL at

http://allenbrowne.com/AppIssueBadWord.html


OK, now to your problem. You posted a query for a report. That is some query!

Try this: copy the report and change the record source to:

SELECT [ARCHIVE - ADM Summary].ADM, [ARCHIVE - ADM Summary].Region,
[ARCHIVE - ADM Summary].[SBT Account], [ARCHIVE - ADM Summary].Month,
[ARCHIVE - ADM Summary].Year, [ARCHIVE - ADM Summary].[# Of Stores
Counted], [ARCHIVE - ADM Summary].[ADM Cumulative Shrink %]
FROM [ARCHIVE - ADM Summary]


Open the report and check to see if the correct (expected?) records are
returned.
If so, create a button on the form that has your combo boxes.

***In the following code, I don't know the data types of the fields, so you
will need to add the correct delimiters***

In the click event for the new button, paste in the following code:

'----------------
Dim strWhere As String
Dim strReportName As String

'************
'change to your report name!!!!!
strReportName = "YourReportName"
'************


strWhere = " WHERE"

If Not IsNull([Forms]![Main Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].ADM = "
strWhere = strWhere & [Forms]![Main Form]![ADM Select] & " AND"
End If

If Not IsNull([Forms]![Main Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].Region)= "
strWhere = strWhere & [Forms]![Main Form]![Region Select] & " AND"
End If

If Not IsNull([Forms]![Main Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].[SBT Account] = "
strWhere = strWhere & [Forms]![Main Form]![SBT Account Select] & " And"
End If

If Not IsNull([Forms]![Main Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].[Month] = "
strWhere = strWhere & [Forms]![Main Form]![Month Select] & " And"
End If

If Not IsNull([Forms]![Main Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].[Year] = "
strWhere = strWhere & [Forms]![Main Form]![Year Select] & " And"
End If


If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

'change
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
'----------------



With all combo boxes unselected, open the report. Check the number of
records. Select one of the combo boxes.
Select two of the combo boxes.
 
E

Erick C

Steve -
Thank you for the reply.
Given your recommendation, I have updated all of the object names in
the database so there are no longer any spaces and there are no
keywords used.

I updated the form with the button, but when I try to execute I
receive the following error:
Run-time error '2497': The action or method requires a Report Name
argument.

Here is the code that I added to the button, the debugger highlights
the DoCmd line, I do not know what is missing from the command.

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = "
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region)= "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = "
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport ADM_Test_2, acViewPreview, , strWhere


Eric,
I am setting up a database that will serve as an archive for our
reporting results.  I am jsut starting to get everything together, so
I can modify anything if necessary.
Click to expand...

I stress again: using spaces in object names just causes you headaches.
instead of using

[ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]

it would be better to use

[ARCHIVE_ADM_Summary].ADM = [Forms]![Main_Form]![ADM_Select]

or

[ArchiveADMSummary].ADM = [Forms]![MainForm]![ADMSelect]

Both are still very easy to read. And no one but the programmer will be
seeing the names.

See #3 in the "The Ten Commandments of Access" at

http://www.accessmvp.com/djsteele/AccessTenCommandments.html

(also don't use special chars    (!@#$%^&*><=-)   except the underscore  (_)

Also be aware that "Year", "Date" and "Month" are reserved words (and
functions) in Access. You will find a list of reserved word for Access and
SQL at

       http://allenbrowne.com/AppIssueBadWord.html

OK, now to your problem.  You posted a query for a report. That is somequery!

Try this: copy the report and change the record source to:

SELECT [ARCHIVE - ADM Summary].ADM, [ARCHIVE - ADM Summary].Region,
[ARCHIVE - ADM Summary].[SBT Account], [ARCHIVE - ADM Summary].Month,
[ARCHIVE - ADM Summary].Year, [ARCHIVE - ADM Summary].[# Of Stores
Counted], [ARCHIVE - ADM Summary].[ADM Cumulative Shrink %]
FROM [ARCHIVE - ADM Summary]

Open the report and check to see if the correct (expected?) records are
returned.
If so, create a button on the form that has your combo boxes.

***In the following code, I don't know the data types of the fields, so you
will need to add the correct delimiters***

In the click event for the new button, paste in the following code:

'----------------
   Dim strWhere As String
   Dim strReportName As String

   '************
   'change to your report name!!!!!
   strReportName = "YourReportName"
   '************

   strWhere = " WHERE"

   If Not IsNull([Forms]![Main Form]![ADM Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].ADM = "
      strWhere = strWhere & [Forms]![Main Form]![ADM Select] & " AND"
   End If

   If Not IsNull([Forms]![Main Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].Region)= "
      strWhere = strWhere & [Forms]![Main Form]![Region Select] &" AND"
   End If

   If Not IsNull([Forms]![Main Form]![SBT Account Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].[SBT Account] = "
      strWhere = strWhere & [Forms]![Main Form]![SBT Account Select] & " And"
   End If

   If Not IsNull([Forms]![Main Form]![Month Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].[Month] ="
      strWhere = strWhere & [Forms]![Main Form]![Month Select] & " And"
   End If

   If Not IsNull([Forms]![Main Form]![Year Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].[Year] = "
      strWhere = strWhere & [Forms]![Main Form]![Year Select] & "And"
   End If

   If Len(strWhere) > 6 Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If

   'change
   DoCmd.OpenReport strReportName, acViewPreview, , strWhere
'----------------

With all combo boxes unselected, open the report. Check the number of
records. Select one of the combo boxes.
Select two of the combo boxes.
 .
 .
 .
Select different combinations of combo boxes.

Are the results what you expect/want???

HTH
Click to expand...
 
S

Steve Sanford

Erick,
Given your recommendation, I have updated all of the object names in
the database so there are no longer any spaces and there are no
keywords used.
Click to expand...

Did you remove the spaces in the control names? In the code I see several
names that still have spaces in them.....


As for the error, the "OpenReport" syntax requires the report name to be
delimited with quotes, if you use the actual name. If you use a variable, it
is just the variable name.

You can use:

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere


OR

Dim strReportName As String

strReportName = "ADM_Test_2"

DoCmd.OpenReport strReportName , acViewPreview, , strWhere


In my previous post, I wrote:

***In the following code, I don't know the data types of the fields, so you
will need to add the correct delimiters***


From the code, it looks like the fields [SBT Account], [Month] and [Year]
might be numbers, but I can't tell if [ADM] and [Region] are numbers or text.
If text, they will need to be delimited with quotes.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Erick C said:
Steve -
Thank you for the reply.
Given your recommendation, I have updated all of the object names in
the database so there are no longer any spaces and there are no
keywords used.

I updated the form with the button, but when I try to execute I
receive the following error:
Run-time error '2497': The action or method requires a Report Name
argument.

Here is the code that I added to the button, the debugger highlights
the DoCmd line, I do not know what is missing from the command.

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = "
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region)= "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = "
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport ADM_Test_2, acViewPreview, , strWhere


Eric,
I am setting up a database that will serve as an archive for our
reporting results. I am jsut starting to get everything together, so
I can modify anything if necessary.
Click to expand...

I stress again: using spaces in object names just causes you headaches.
instead of using

[ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]

it would be better to use

[ARCHIVE_ADM_Summary].ADM = [Forms]![Main_Form]![ADM_Select]

or

[ArchiveADMSummary].ADM = [Forms]![MainForm]![ADMSelect]

Both are still very easy to read. And no one but the programmer will be
seeing the names.

See #3 in the "The Ten Commandments of Access" at

http://www.accessmvp.com/djsteele/AccessTenCommandments.html

(also don't use special chars (!@#$%^&*><=-) except the underscore (_)

Also be aware that "Year", "Date" and "Month" are reserved words (and
functions) in Access. You will find a list of reserved word for Access and
SQL at

http://allenbrowne.com/AppIssueBadWord.html

OK, now to your problem. You posted a query for a report. That is some query!

Try this: copy the report and change the record source to:

SELECT [ARCHIVE - ADM Summary].ADM, [ARCHIVE - ADM Summary].Region,
[ARCHIVE - ADM Summary].[SBT Account], [ARCHIVE - ADM Summary].Month,
[ARCHIVE - ADM Summary].Year, [ARCHIVE - ADM Summary].[# Of Stores
Counted], [ARCHIVE - ADM Summary].[ADM Cumulative Shrink %]
FROM [ARCHIVE - ADM Summary]

Open the report and check to see if the correct (expected?) records are
returned.
If so, create a button on the form that has your combo boxes.

***In the following code, I don't know the data types of the fields, so you
will need to add the correct delimiters***

In the click event for the new button, paste in the following code:

'----------------
Dim strWhere As String
Dim strReportName As String

'************
'change to your report name!!!!!
strReportName = "YourReportName"
'************

strWhere = " WHERE"

If Not IsNull([Forms]![Main Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].ADM = "
strWhere = strWhere & [Forms]![Main Form]![ADM Select] & " AND"
End If

If Not IsNull([Forms]![Main Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].Region)= "
strWhere = strWhere & [Forms]![Main Form]![Region Select] & " AND"
End If

If Not IsNull([Forms]![Main Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].[SBT Account] = "
strWhere = strWhere & [Forms]![Main Form]![SBT Account Select] & " And"
End If

If Not IsNull([Forms]![Main Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].[Month] = "
strWhere = strWhere & [Forms]![Main Form]![Month Select] & " And"
End If

If Not IsNull([Forms]![Main Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE - ADM Summary].[Year] = "
strWhere = strWhere & [Forms]![Main Form]![Year Select] & " And"
End If

If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

'change
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
'----------------

With all combo boxes unselected, open the report. Check the number of
records. Select one of the combo boxes.
Select two of the combo boxes.
.
.
.
Select different combinations of combo boxes.

Are the results what you expect/want???

HTH
Click to expand...
Click to expand...
 
E

Erick C

Steve -
Thank you again for your help. I updated all tables, report, form and
queries, but the fields in the tables still have spaces if you open a
table to view it. Should I also go into all of the tables and remove
spaces? I did not know if updating the object names would be enough
or not.

I added parenthesis as you instructed, but now I receive a new kind of
error, really 3 kinds:

If I leave the form combo boxes empty, I get a "Enter Parameter Value"
box that opens asking to define WHERE.
If I enter a value into one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].C'. In this example I selected C in the ADM
combo box.
If I enter a value into one of the number-based combo boxes (like
MONTH or YEAR) then I get a run-time error 3075, saying "Syntax error
(missing operator) in query expression ' WHERE [ARCHIVE_ADM_Summary].
[Month]=8'. In this example I selected 8 in the Month combo box.

Here is my updated code attached to the button, I believe that I added
the parenthesis correctly though I may be wrong. When it comes to
this stuff I am typically incorrect!

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'ADM'" = "
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'Region'")= "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'SBT Account'" =
"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere


Erick,
Given your recommendation, I have updated all of the object names in
the database so there are no longer any spaces and there are no
keywords used.
Click to expand...

Did you remove the spaces in the control names? In the code I see several
names that still have spaces in them.....

As for the error, the "OpenReport" syntax requires the report name to be
delimited with quotes, if you use the actual name. If you use a variable,it
is just the variable name.

You can use:

   DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

     OR

   Dim strReportName As String

    strReportName = "ADM_Test_2"

   DoCmd.OpenReport strReportName , acViewPreview, , strWhere

In my previous post, I wrote:

***In the following code, I don't know the data types of the fields, so you
will need to add the correct delimiters***

From the code, it looks like the fields [SBT Account], [Month] and [Year]
might be numbers, but I can't tell if [ADM] and [Region] are numbers or text.
If text, they will need to be delimited with quotes.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



Erick C said:
Steve -
Thank you for the reply.
Given your recommendation, I have updated all of the object names in
the database so there are no longer any spaces and there are no
keywords used.
Click to expand...
I updated the form with the button, but when I try to execute I
receive the following error:
Run-time error '2497':  The action or method requires a Report Name
argument.
Click to expand...
Here is the code that I added to the button, the debugger highlights
the DoCmd line, I do not know what is missing from the command.
Click to expand...
Dim strWhere As String
   Dim strReportName As String
   strWhere = " WHERE"
   If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = "
      strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
   End If
   If Not IsNull([Forms]![Main_Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region)= "
      strWhere = strWhere & [Forms]![Main_Form]![Region Select]& "
AND"
   End If
   If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = "
      strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
   End If
   If Not IsNull([Forms]![Main_Form]![Month Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] ="
      strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
   End If
   If Not IsNull([Forms]![Main_Form]![Year Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
      strWhere = strWhere & [Forms]![Main_Form]![Year Select] &" And"
   End If
   If Len(strWhere) > 6 Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If
Click to expand...
   DoCmd.OpenReport ADM_Test_2, acViewPreview, , strWhere
Click to expand...
Eric,
I am setting up a database that will serve as an archive for our
reporting results.  I am jsut starting to get everything together, so
I can modify anything if necessary.
I stress again: using spaces in object names just causes you headaches.
instead of using
[ARCHIVE - ADM Summary].ADM)=[Forms]![Main Form]![ADM Select]
it would be better to use
[ARCHIVE_ADM_Summary].ADM = [Forms]![Main_Form]![ADM_Select]
or
[ArchiveADMSummary].ADM = [Forms]![MainForm]![ADMSelect]
Both are still very easy to read. And no one but the programmer will be
seeing the names.
See #3 in the "The Ten Commandments of Access" at
http://www.accessmvp.com/djsteele/AccessTenCommandments.html
(also don't use special chars    (!@#$%^&*><=-)   except the underscore  (_)
Also be aware that "Year", "Date" and "Month" are reserved words (and
functions) in Access. You will find a list of reserved word for Access and
SQL at
       http://allenbrowne.com/AppIssueBadWord.html
OK, now to your problem.  You posted a query for a report. That is some query!
Try this: copy the report and change the record source to:
SELECT [ARCHIVE - ADM Summary].ADM, [ARCHIVE - ADM Summary].Region,
[ARCHIVE - ADM Summary].[SBT Account], [ARCHIVE - ADM Summary].Month,
[ARCHIVE - ADM Summary].Year, [ARCHIVE - ADM Summary].[# Of Stores
Counted], [ARCHIVE - ADM Summary].[ADM Cumulative Shrink %]
FROM [ARCHIVE - ADM Summary]
Open the report and check to see if the correct (expected?) records are
returned.
If so, create a button on the form that has your combo boxes.
***In the following code, I don't know the data types of the fields, so you
will need to add the correct delimiters***
In the click event for the new button, paste in the following code:
'----------------
   Dim strWhere As String
   Dim strReportName As String
   '************
   'change to your report name!!!!!
   strReportName = "YourReportName"
   '************
   strWhere = " WHERE"
   If Not IsNull([Forms]![Main Form]![ADM Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].ADM ="
      strWhere = strWhere & [Forms]![Main Form]![ADM Select] & " AND"
   End If
   If Not IsNull([Forms]![Main Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].Region)= "
      strWhere = strWhere & [Forms]![Main Form]![Region Select] & " AND"
   End If
   If Not IsNull([Forms]![Main Form]![SBT Account Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].[SBT Account] = "
      strWhere = strWhere & [Forms]![Main Form]![SBT Account Select] & " And"
   End If
   If Not IsNull([Forms]![Main Form]![Month Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].[Month]= "
      strWhere = strWhere & [Forms]![Main Form]![Month Select] & " And"
   End If
   If Not IsNull([Forms]![Main Form]![Year Select]) Then
      strWhere = strWhere & " [ARCHIVE - ADM Summary].[Year] = "
      strWhere = strWhere & [Forms]![Main Form]![Year Select]& " And"
   End If
   If Len(strWhere) > 6 Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If
   'change
   DoCmd.OpenReport strReportName, acViewPreview, , strWhere
'----------------
With all combo boxes unselected, open the report. Check the number of
records. Select one of the combo boxes.
Select two of the combo boxes.
 .
 .
 .
Select different combinations of combo boxes.
Are the results what you expect/want???
HTH
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
S

Steve Sanford

Erick,
spaces? I did not know if updating the object names would be enough
or not.
Click to expand...

I do not use spaces in *any* object names; that means field names, form
names, qyery name, control names, report names, module names.

And I use a naming convention. If a field is a text type field, I use a
prefix of "txt". And I use descriptive names - "Month" or "Year" doesn't tell
you much..... month of birth? of your wedding?
shipped month?? OK, you get the picture.

Here is a link to one (of many) naming conventions:

http://mvps.org/access/general/gen0012.htm



On the delimiters, I think I wasn't clear enough. :(

You only add delimiters to the value if are concatenating to a string or
date type field.
The rules are: for dates, use the pound (#) sign (example #9/20/2009#).
for strings, use single or double quotes (ex: 'C' or "C")
Numbers do not need delimiters.


Expanded ofr clarity, it will look like:

....... & " [ARCHIVE_ADM_Summary].ADM = ' "
....... & [Forms]![Main_Form]![ADM Select] & " ' AND"

It looks like "ADM", "Region" and "SBT Account" are text type fields; so try
this:

'--------------------------
Dim strWhere As String
' Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'--------------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Erick C said:
Steve -
Thank you again for your help. I updated all tables, report, form and
queries, but the fields in the tables still have spaces if you open a
table to view it. Should I also go into all of the tables and remove
spaces? I did not know if updating the object names would be enough
or not.

I added parenthesis as you instructed, but now I receive a new kind of
error, really 3 kinds:

If I leave the form combo boxes empty, I get a "Enter Parameter Value"
box that opens asking to define WHERE.
If I enter a value into one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].C'. In this example I selected C in the ADM
combo box.
If I enter a value into one of the number-based combo boxes (like
MONTH or YEAR) then I get a run-time error 3075, saying "Syntax error
(missing operator) in query expression ' WHERE [ARCHIVE_ADM_Summary].
[Month]=8'. In this example I selected 8 in the Month combo box.

Here is my updated code attached to the button, I believe that I added
the parenthesis correctly though I may be wrong. When it comes to
this stuff I am typically incorrect!

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'ADM'" = "
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'Region'")= "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'SBT Account'" =
"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
Click to expand...
 
E

Erick C

Steve -
I will update all of the names tonight or in the morning.
Unfortunately, I have to update a few other databases because all of
the tables in this database are being appended by queries in other
databases. It is going to take me a little bit to get rid of all of
the spaces, but I will get it done.

I updated the code with your modifications but I am still getting the
same results. The only change that I see is if I enter a value into
one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].Region=C'. In this example I selected C in the
ADM
combo box. The error message looks better than before, but still not
working.

Just in case, here is the code that I currently have, I may have
missed something that you had in your update.

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
"' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere




Erick,
spaces?  I did not know if updating the object names would be enough
or not.
Click to expand...

I do not use spaces in *any* object names; that means field names, form
names, qyery name, control names, report names, module names.

And I use a naming convention. If a field is a text type field, I use a
prefix of "txt". And I use descriptive names - "Month" or "Year" doesn't tell
you much..... month of birth? of your wedding?
shipped month?? OK, you get the picture.

Here is a link to one (of many) naming conventions:

     http://mvps.org/access/general/gen0012.htm

On the delimiters, I think I wasn't clear enough. :(

You only add delimiters to the value if are concatenating to a string or
date type field.
The rules are: for dates, use the pound (#) sign (example #9/20/2009#).
                    for strings, use single or doublequotes (ex: 'C'  or "C")
Numbers do not need delimiters.

Expanded ofr clarity, it will look like:

    ....... & " [ARCHIVE_ADM_Summary].ADM = ' "
    ....... & [Forms]![Main_Form]![ADM Select] & " ' AND"

It looks like "ADM", "Region" and "SBT Account" are text type fields; so try
this:

'--------------------------
Dim strWhere As String
   '    Dim strReportName As String
   strWhere = " WHERE"
   If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
      strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "'AND"
   End If
   If Not IsNull([Forms]![Main_Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
      strWhere = strWhere & [Forms]![Main_Form]![Region Select] &" AND"
   End If
   If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account]= '"
      strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
   End If
   If Not IsNull([Forms]![Main_Form]![Month Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
      strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
   End If
   If Not IsNull([Forms]![Main_Form]![Year Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
      strWhere = strWhere & [Forms]![Main_Form]![Year Select] & "And"
   End If

    'remove the last 4 chars
   If Len(strWhere) > 6 Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If

   DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'--------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



Erick C said:
Steve -
Thank you again for your help.  I updated all tables, report, form and
queries, but the fields in the tables still have spaces if you open a
table to view it.  Should I also go into all of the tables and remove
spaces?  I did not know if updating the object names would be enough
or not.
Click to expand...
I added parenthesis as you instructed, but now I receive a new kind of
error, really 3 kinds:
Click to expand...
If I leave the form combo boxes empty, I get a "Enter Parameter Value"
box that opens asking to define WHERE.
If I enter a value into one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].C'.  In this example I selected C in the ADM
combo box.
If I enter a value into one of the number-based combo boxes (like
MONTH or YEAR) then I get a run-time error 3075, saying "Syntax error
(missing operator) in query expression ' WHERE [ARCHIVE_ADM_Summary].
[Month]=8'.  In this example I selected 8 in the Month combo box.
Click to expand...
Here is my updated code attached to the button, I believe that I added
the parenthesis correctly though I may be wrong.  When it comes to
this stuff I am typically incorrect!
Click to expand...
Dim strWhere As String
   Dim strReportName As String
   strWhere = " WHERE"
   If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'ADM'" = "
      strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
   End If
   If Not IsNull([Forms]![Main_Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'Region'")= "
      strWhere = strWhere & [Forms]![Main_Form]![Region Select]& "
AND"
   End If
   If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'SBT Account'" =
"
      strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
   End If
   If Not IsNull([Forms]![Main_Form]![Month Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] ="
      strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
   End If
   If Not IsNull([Forms]![Main_Form]![Year Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
      strWhere = strWhere & [Forms]![Main_Form]![Year Select] &" And"
   End If
   If Len(strWhere) > 6 Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If
Click to expand...
   DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
S

Steve Sanford

Change the code to look like this:

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

Docmd.Print strWhere

' DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere



Run the code, then open the immediate window (press contrl-G), copy the line
and paste in in a post so I can see what the string looks like.

---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Erick C said:
Steve -
I will update all of the names tonight or in the morning.
Unfortunately, I have to update a few other databases because all of
the tables in this database are being appended by queries in other
databases. It is going to take me a little bit to get rid of all of
the spaces, but I will get it done.

I updated the code with your modifications but I am still getting the
same results. The only change that I see is if I enter a value into
one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].Region=C'. In this example I selected C in the
ADM
combo box. The error message looks better than before, but still not
working.

Just in case, here is the code that I currently have, I may have
missed something that you had in your update.

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
"' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere




Erick,
spaces? I did not know if updating the object names would be enough
or not.
Click to expand...

I do not use spaces in *any* object names; that means field names, form
names, qyery name, control names, report names, module names.

And I use a naming convention. If a field is a text type field, I use a
prefix of "txt". And I use descriptive names - "Month" or "Year" doesn't tell
you much..... month of birth? of your wedding?
shipped month?? OK, you get the picture.

Here is a link to one (of many) naming conventions:

http://mvps.org/access/general/gen0012.htm

On the delimiters, I think I wasn't clear enough. :(

You only add delimiters to the value if are concatenating to a string or
date type field.
The rules are: for dates, use the pound (#) sign (example #9/20/2009#).
for strings, use single or double quotes (ex: 'C' or "C")
Numbers do not need delimiters.

Expanded ofr clarity, it will look like:

....... & " [ARCHIVE_ADM_Summary].ADM = ' "
....... & [Forms]![Main_Form]![ADM Select] & " ' AND"

It looks like "ADM", "Region" and "SBT Account" are text type fields; so try
this:

'--------------------------
Dim strWhere As String
' Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'--------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



Erick C said:
Steve -
Thank you again for your help. I updated all tables, report, form and
queries, but the fields in the tables still have spaces if you open a
table to view it. Should I also go into all of the tables and remove
spaces? I did not know if updating the object names would be enough
or not.
Click to expand...
I added parenthesis as you instructed, but now I receive a new kind of
error, really 3 kinds:
Click to expand...
If I leave the form combo boxes empty, I get a "Enter Parameter Value"
box that opens asking to define WHERE.
If I enter a value into one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].C'. In this example I selected C in the ADM
combo box.
If I enter a value into one of the number-based combo boxes (like
MONTH or YEAR) then I get a run-time error 3075, saying "Syntax error
(missing operator) in query expression ' WHERE [ARCHIVE_ADM_Summary].
[Month]=8'. In this example I selected 8 in the Month combo box.
Click to expand...
Here is my updated code attached to the button, I believe that I added
the parenthesis correctly though I may be wrong. When it comes to
this stuff I am typically incorrect!
Click to expand...
Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'ADM'" = "
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'Region'")= "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'SBT Account'" =
"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If
Click to expand...
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
S

Steve Sanford

Two more changes:

Change

from: strWhere = " WHERE"

to: strWhere = " WHERE 1 = 1 AND"

and change

from: If Len(strWhere) > 6 Then

to: If Len(strWhere) > 16 Then


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Steve Sanford said:
Change the code to look like this:

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

Docmd.Print strWhere

' DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere



Run the code, then open the immediate window (press contrl-G), copy the line
and paste in in a post so I can see what the string looks like.

---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Erick C said:
Steve -
I will update all of the names tonight or in the morning.
Unfortunately, I have to update a few other databases because all of
the tables in this database are being appended by queries in other
databases. It is going to take me a little bit to get rid of all of
the spaces, but I will get it done.

I updated the code with your modifications but I am still getting the
same results. The only change that I see is if I enter a value into
one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].Region=C'. In this example I selected C in the
ADM
combo box. The error message looks better than before, but still not
working.

Just in case, here is the code that I currently have, I may have
missed something that you had in your update.

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
"' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere




Erick,

spaces? I did not know if updating the object names would be enough
or not.

I do not use spaces in *any* object names; that means field names, form
names, qyery name, control names, report names, module names.

And I use a naming convention. If a field is a text type field, I use a
prefix of "txt". And I use descriptive names - "Month" or "Year" doesn't tell
you much..... month of birth? of your wedding?
shipped month?? OK, you get the picture.

Here is a link to one (of many) naming conventions:

http://mvps.org/access/general/gen0012.htm

On the delimiters, I think I wasn't clear enough. :(

You only add delimiters to the value if are concatenating to a string or
date type field.
The rules are: for dates, use the pound (#) sign (example #9/20/2009#).
for strings, use single or double quotes (ex: 'C' or "C")
Numbers do not need delimiters.

Expanded ofr clarity, it will look like:

....... & " [ARCHIVE_ADM_Summary].ADM = ' "
....... & [Forms]![Main_Form]![ADM Select] & " ' AND"

It looks like "ADM", "Region" and "SBT Account" are text type fields; so try
this:

'--------------------------
Dim strWhere As String
' Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'--------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



:
Steve -
Thank you again for your help. I updated all tables, report, form and
queries, but the fields in the tables still have spaces if you open a
table to view it. Should I also go into all of the tables and remove
spaces? I did not know if updating the object names would be enough
or not.

I added parenthesis as you instructed, but now I receive a new kind of
error, really 3 kinds:

If I leave the form combo boxes empty, I get a "Enter Parameter Value"
box that opens asking to define WHERE.
If I enter a value into one of the text-based combo boxes (like ADM,
REGION, or SBT Account) then I get a run-time error 3075, saying
"Syntax error (missing operator) in query expression ' WHERE
[ARCHIVE_ADM_Summary].C'. In this example I selected C in the ADM
combo box.
If I enter a value into one of the number-based combo boxes (like
MONTH or YEAR) then I get a run-time error 3075, saying "Syntax error
(missing operator) in query expression ' WHERE [ARCHIVE_ADM_Summary].
[Month]=8'. In this example I selected 8 in the Month combo box.

Here is my updated code attached to the button, I believe that I added
the parenthesis correctly though I may be wrong. When it comes to
this stuff I am typically incorrect!

Dim strWhere As String
Dim strReportName As String
strWhere = " WHERE"
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'ADM'" = "
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & " AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'Region'")= "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary]." 'SBT Account'" =
"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
" And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Lose the word WHERE.

The WhereCondition argument of an OpenReport or OpenForm method needs to be a
valid SQL WHERE clause *WITHOUT* the word WHERE included.
 
S

Steve Sanford

Many thanks John..... I knew that. <headsmack>
I've been creating too many recordsets in code lately...


Erick,

Now the code should look like:

'-------------------------------------
Dim strWhere As String
' Dim strReportName As String ' REM'ed - not used

strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'-------------------------------------
 
E

Erick C

OK, made the changes. It runs for 2 seconds and then pops up with an
error 3075: Syntax error (missing operator) in query expression 'WHERE
1 = 1 AND'.
The debugger highlights the DoCmd line:
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

Is it possibly an error caused by how I built the report?


Many thanks John..... I knew that. <headsmack>
I've been creating too many recordsets in code lately...

Erick,

Now the code should look like:

'-------------------------------------
   Dim strWhere As String
   '   Dim strReportName As String  ' REM'ed - not used

   strWhere = ""
   If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
      strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "'AND"
   End If
   If Not IsNull([Forms]![Main_Form]![Region Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
      strWhere = strWhere & [Forms]![Main_Form]![Region Select] &"' AND"
   End If
   If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account]= '"
      strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
   End If
   If Not IsNull([Forms]![Main_Form]![Month Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
      strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
   End If
   If Not IsNull([Forms]![Main_Form]![Year Select]) Then
      strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
      strWhere = strWhere & [Forms]![Main_Form]![Year Select] & "And"
   End If

   'remove the last 4 chars
   If Len(strWhere) > 6 Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If

   DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'-------------------------------------

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



Lose the word WHERE.
Click to expand...
The WhereCondition argument of an OpenReport or OpenForm method needs to be a
valid SQL WHERE clause *WITHOUT* the word WHERE included.
--
Click to expand...
             John W. Vinson [MVP]- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
D

Douglas J. Steele

What's the actual code you're using (Steve's code does not have WHERE 1 = 1
anywhere in it).

Incidentally, Steve's code does have an error. You should be adding "' AND
", not "' AND" (the difference is the space after the word AND)

and the line of code

strWhere = Left$(strWhere, Len(strWhere) - 4)

should be

strWhere = Left$(strWhere, Len(strWhere) - 5)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OK, made the changes. It runs for 2 seconds and then pops up with an
error 3075: Syntax error (missing operator) in query expression 'WHERE
1 = 1 AND'.
The debugger highlights the DoCmd line:
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

Is it possibly an error caused by how I built the report?


Many thanks John..... I knew that. <headsmack>
I've been creating too many recordsets in code lately...

Erick,

Now the code should look like:

'-------------------------------------
Dim strWhere As String
' Dim strReportName As String ' REM'ed - not used

strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If

'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'-------------------------------------

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



John W. Vinson said:
On Mon, 21 Sep 2009 13:15:01 -0700, Steve Sanford <limbim53 at yahoo dot
com>
wrote:
Click to expand...
Lose the word WHERE.
Click to expand...
The WhereCondition argument of an OpenReport or OpenForm method needs to
be a
valid SQL WHERE clause *WITHOUT* the word WHERE included.
--
Click to expand...
John W. Vinson [MVP]- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
E

Erick C

Hi Doug -
Sorry, I was responding in the middle of the conversation that
everyone was having, I did not see the new code.

OK, the report now opens correctly if I have no combo box selections.

If I select something in the combo boxes for SBT Account (text
format), ADM (text format), or Month (number format) I receive a Run-
time error 3075: Syntax error (missing operator) in query expression
'[ARCHIVE_ADM_Summary].ADM = 'AE' (error is the same for SBT account
selection, just replace 'AE' with 'Meijer')
Or I get a Run-time error 3075: Syntax error (missing operator) in
query expression '[ARCHIVE_ADM_Summary].[Month] = '.

If I select something in the Region combo box I get an 'Enter
Parameter Value' box that opens up.

If I select something in the Year combo box (right now only 2009 is
populated in my tables) I get a blank report, no error messages.

Here is the updated code that I have right now, I believe that I
captured the most recent recommendations:

Dim strWhere As String
Dim strReportName As String

strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "'
AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] &
"' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & "
And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If


If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 5)
End If


DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere



What's the actual code you're using (Steve's code does not have WHERE 1 = 1
anywhere in it).

Incidentally, Steve's code does have an error. You should be adding "' AND
", not "' AND" (the difference is the space after the word AND)

and the line of code

strWhere = Left$(strWhere, Len(strWhere) - 4)

should be

strWhere = Left$(strWhere, Len(strWhere) - 5)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


OK, made the changes.  It runs for 2 seconds and then pops up with an
error 3075: Syntax error (missing operator) in query expression 'WHERE
1 = 1 AND'.
The debugger highlights the DoCmd line:
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere

Is it possibly an error caused by how I built the report?

Many thanks John..... I knew that. <headsmack>
I've been creating too many recordsets in code lately...

Now the code should look like:
Click to expand...
'-------------------------------------
Dim strWhere As String
' Dim strReportName As String ' REM'ed - not used
Click to expand...
strWhere = ""
If Not IsNull([Forms]![Main_Form]![ADM Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].ADM = '"
strWhere = strWhere & [Forms]![Main_Form]![ADM Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![Region Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].Region = "
strWhere = strWhere & [Forms]![Main_Form]![Region Select] & "' AND"
End If
If Not IsNull([Forms]![Main_Form]![SBT Account Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[SBT Account] = '"
strWhere = strWhere & [Forms]![Main_Form]![SBT Account Select] & "' And"
End If
If Not IsNull([Forms]![Main_Form]![Month Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Month] = "
strWhere = strWhere & [Forms]![Main_Form]![Month Select] & " And"
End If
If Not IsNull([Forms]![Main_Form]![Year Select]) Then
strWhere = strWhere & " [ARCHIVE_ADM_Summary].[Year] = "
strWhere = strWhere & [Forms]![Main_Form]![Year Select] & " And"
End If
Click to expand...
'remove the last 4 chars
If Len(strWhere) > 6 Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If
Click to expand...
DoCmd.OpenReport "ADM_Test_2", acViewPreview, , strWhere
'-------------------------------------
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

filter query on combo box in a continuous subform 0
Combo box help 1
Combo Box reduction 0
Cascading Combo Box Selection Being Overwritten 1
combo box selection will not populate query correctly 1
Create Check box that controls visibility of a text box or combo b 1
combobox not displaying results 2
Combo box / Query 1

Top