Thanks Crystal, you have given me some bed time reading to enable me to get a
better understanding of access and I hope I succeed.
Your help is very much appreciated
Yours Aye
Bruce
:
Hi Bruce,
"too difficult for a novice like myself to understand"
that can be fixed <smile> ... read this:
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~
what I gave you, Bruce, IS fairly basic (you probably have an aversion
to code -- don't! VBA is one of the easiest languages to understand and
use ... if you get over your mental block, you will see it is logical
and makes sense). After you read 'Access Basics', spend a couple hours
to understand it -- this concept is extremely useful <smile>
Warm Regards,
Crystal
remote programming and training
*
have an awesome day
*
piper wrote:
THanks for that, name is Bruce. You have given a very detailed explanation
but too difficult for a novice like myself to understand, I was stuck as soon
as I read use form to collect criteria for the report, don't know how to do
this any advice would be helpful please. If there was a way to the the
parameter Screen to offer a list or drop down menu with the 20 odd categories
I have that is what I am looking to do, I know it sounds easy but I am sure
it isnt. I have seen a database that when you click on the switchboard button
to open the report the enter parameter screen appears you then click on the
downward arrow and a list of categories appear click on the one you want and
the report relating to that category opens, now that is what I am wanting to
crack and would appreciate anyone's assistance, but needs to be basic step by
step as I am just a novice with access databases.
Your help and assistance would be very much appreciated
Yours Aye
Bruce
:
Hi piper (what is your name?)
use a form to collect crieria for the report
then, one way, is to use the WHERE clause of the OpenReport action to
limit the report for your specified criteria when you click a command
button to open the report
use the textboxes/comboboxes to filter the report
It is best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use textboxes,
comboboxes, listboxes, etc to make it easier for the user to choose
criteria and you can ignore criteria when it has not been specified...
~~~
here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport for the WHERECONDITION clause
DoCmd.OpenReport reportname[, View][, FilterName][, WhereCondition]
ie:
DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"
~~~
assuming you are in the code behind the ReportMenu form...
here is a general case:
'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant
'initialize the variable
mFilter = null
'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if
'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if
'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if
if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have used:
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks
DateFieldname to show that dates need to be delimited with #
NumericFieldname to show that numbers are not delimited
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria
make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)
For a Date Range, you would do:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if
If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:
Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IF you want to also PRINT the criteria on the report
put a LABEL on your PageFooter
name --> Label_Criteria
then, in the OnFormat event of the ReportHeader
'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~
hopefully, you can adapt this logic to your question -- if not, we can
help you further
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
& and + are both Operators
The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.
The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.
Null + "anything" = Null
Null & "anything = "anything"
"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value
Null + "" = Null
Null & "" = ""
(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname
Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:
(Firstname + " ") & Lastname
What is in the parentheses is evaluated first -- then it is concatenated
to what comes next
You might also want to do this:
(Firstname + " ") & (Middlename + " ") & Lastname
Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.
What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.
Trim((Firstname + " ") & (Middlename + " ") & Lastname)
here is something you may want to read:
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
Warm Regards,
Crystal
*
have an awesome day
*
piper wrote:
I am looking for a way that, when I click on a report a pop up screen will
appear and ask me to enter a category, then OK this will then look through a
specific column in my table and produce a report of the category I requested?
Any advice would be much appreciated.
