Thanks Mr Meyer/MVP
I've tried your proposal:
Using Between [Forms]![Front_page]![Date_from] And
[Forms]![Front_page]![Date_to]
and for the UCS query
<[Forms]![Front_page]![UCS (MPa)]
on the same Table: Core Cylinder
There seem to be soem Runtime Errors: I frankly don't know why ??
"This expression is typed incorrectly, or it is to complex to be
evaluated.
Fro Example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expresssion to
variables"
is what Arvin I keep constanly getting , the system will run
1. Form!Front_page!UCS (MPa)
2. Forms!Front_page!Date_from
3.Forms!Front_page!Date_to
and the above Dialog box comes up instead of the Query showing the
table
field as defined by Options_Query.
I've no clue as to why it's doing this , I rechecked and check....
Hope you can assit me with this frustrating problem.
:
With data stored in multiple tables, you either need to connect them
with
an
Outer Join, or run 2 queries followed by a Union query. Or just run
then
separately.
Once again, the dates are handled like:
In the Date Casted query column try using this expression:
Between [Enter a Start Date] And [Enter an End Date]
The above expression will give you 2 prompts (for start and end
dates).
and use an expression something like:
[Enter the UCS]
for the criterion in the UCS column. Using the Square Brackets [ ]
tells
Access that this is a specific value. But there is no value supplied,
so
Access prompts for it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Hi Gordon,
I'm sorry I should have put down a bit more than what wrote up.
I have some data in a 2 table. One for Cylinder & Core samples and 1
for
Panel only.
Both store data on Date Casted, Date Test , UCS measured.
I am wish to create a simple form to produce a Print Preview Report
that
will take
Start Date : ( this will hav eto use the Date tested)
End Date
this will hav eto use the Date tested)
UCS : This is entered on this Form but use the UCS stored on the
Tables
( 1x
Cylinder & Core samples and 1x Panel samples only) to filter those
UCS
Tested
sample who UCS <= the Entered UCS value.
I wish use any existing Report , i.e. "Core_cylinder Shotcrete Test
Report"
and "Panel Shotcrete Test Report"
So we have 3 Inputs : Start Date , End Date and UCS
:
Hi Rod,
One of the problems new users face, is that they don't know what
they
don't
know - so they don't know how to ask the right questions.
From your question it seems we have a query as our record source
and a
report that 'publishes' the information contained in the
query/record
source.
What we wish to filter on are Two fields - [Date Casted] and [UCS],
correct?
So now the question is - what date do you wish to filter on? Where
is
this
date coming from? Are you filtering the query or the report with
it?
UCS is being compared to 30 MPa - but is that the value in the
column
(field) UCS? If you look at the table or the query itself, does
that
appear
in the UCS field just like that? 30 MPa? Or does it just say 30?
We need to be certain what data type we are attempting to build a
filter
for. Is UCS a text field or a number field? From the name of the
field,
I
assume [Date Casted] is a Date field- is this correct?
If the report is built on a query (as your question implies), then
one
way
to filter the record source would be to filter the data before it
gets
to
the Report.
To filter the query - you would open the query in Design View, find
the
two
fields you wish to filter on (the criteria row towards the bottom
of
the
screen is the where 'filter' of the query).
You can place literal values in the criteria row - like "30 MPa" or
30
if
the UPC field is actually a number column. Your question showed a
greater
than or equal operators - >= so we would enter that like this:
if text
= "30 MPa"
if number
= 30
For the [Date Casted] criteria we could put a literal data this way
#01/01/2008#
An alternative that would allow you to dynamically input the date
when
you
run the query (or open the report) would be to put a psuedo-field
name
in
the criteria - one that doesn't exist. If Access doesn't find the
field
or
variable or reference used as criteria for the query - it will ask
you
what
you want to use for this value. To do this, you would put something
like
[Date of Casting]
In the criteria row.
Now the query (assuming there isn't a field named [Date of
Casting])
will
bring up an input box that lets you type in a date when you run the
query
and it will use the date you type in as the 'criteria'.
The literals are generally considered design time criteria, and the
dynamic
(user supplied values) are considered run time criteria.
Understanding
the
difference goes a long way towards helping you understand how you
want
to
go
about doing this or that. Basically, if you know the value and it
doesn't
change or you have a way of looking it up without involving the
user -
then
it's a design time value. If you need the user to supply some
information,
then that's run time criteria.
Filtering can be done on the report itself, and this has a couple
elements.
One part is the filter criteria - what we are discussing above. The
other
is
that the filter has to be applied (turned on), or the Report
property
Filter on Load needs to be Yes and the Allow Filter needs to be
Yes.
Open the Report in Design View -(Right click the name of the report
in
the
Database Window, select Design View)
Look at the Property Sheet (usually appears on the right side of
the
screen.
If it isn't visible, in the upper left corner of the Report, just
below
the
top border where the name of the report appears, there is a square
block -
called the report selector box. Left click that (to select the
entire
report) then right click the selector box and select(click)
Properties.
That
will make the property sheet visible.
The Property sheet Selection Type should say Report at the top in a
combo
box.
Below that, you will see 5 tabs. Click on the Data tab.
Here you will see the record source, the name of the table or query
that
the
report is based on or a SQL string that dynamically builds a
recordset
(record source) when you open the report.
Below that is your Filter.
Below that is the Filter On Load property - which will be either
Yes
or
No.
If No, then the filter will not be automatically applied when you
open
the
report.
At the bottom of the list - you see Allow Filters, again a Yes or
No
value
is here. If it is No, then as you might guess - filters cannot be
applied
to
your report.
Access is a friendly database application that you won't quickly
outgrow,
a
valuable tool that can save you countless hours in the future, and
help
you
work smarter, enable you to be more competitive in the marketplace.
Don't get discouraged - it's well worth the time and effort it
takes
to
become a proficient Access user. Most of the useful things that a
computer
can do in the business world (and for an individual) are either
straightforward databases or they lean heavily on a database
structures
to
do what they do.
Hope this helps,
Gordon
I have tired to create a Form using an existing Print Preview
Report.
but
it
does not seem to work at all ??
Report name is "Core_cylinder Shotcrete Test Report"
It need 2 inputs: "Date Casted" is the Date to filter for this
report.
Where another Field "UCS" >= 30 MPa
It am new to MS Access and can't work this out. I have tried
Allen
Brown's
tips " Method 2: Form for entering the Date" and it was
impossible
