help on writing/buliding an expression/formula

[Katie]

i've written a query but am struggling to get extra info i need for some
stats i want to produce. i would like to be able to write a formula along
these lines

IF((A-B)>=2,TRUE,FALSE)

i.e. IF(logical_test,value_if_true,value_if_false)

I was hoping then that i could search on the true false values returned. i
know this is an equation that Excel would recognise and assumed that Access
would too,it doesn't

this isn't a seperate expression i want to create that would give me an
extra column. i want this to display a result in the column i am typing in.
i do have the (A-B) bit named as EXPR 1: in a new column already an have
tried to use this reference too but it hasn't worked either.

 

[Chaim]

Where do you want the test to occur? Is this expression part of your
criteria or a field to be displayed?

For the syntax that you are posting, it looks like you might be as close as
replacing 'IF' with 'IIF'.

 

[Katie]

Thanks Chaim
i want to write it in the criteria. thanks for the IIF tip i'll give it a
go. if no joy then i'll post back

 

[Ken Sheridan]

The equivalent function in Access is the IIF function (Immediate IF), which
works in the same way as the Excel IF function. However, if you simply want
to return a value of TRUE or FALSE then you don't need to call the function
at all as the expression itself will evaluate to TRUE or FALSE. You can't do
what you ask and display TRUE or FALSE in the same column in a query in
datasheet view, however; you'd have to have a separate column with the
expression to evaluate the data in other columns:

SELECT A, B, A-B>=2 AS Result
FROM YourTable;

In design view you'd enter the computed result column as:

Result:[A]->=2

The values in the Result column would by default show as 0 or -1 which is
how Access implements Boolean FALSE and TRUE values. To show them as the
latter set the Result column's Format property to True/False in its
properties sheet.

This might not necessarily be the best way of achieving your objective,
however. If you'd care to post back with an explanation of what you want to
do rather than how you are trying to do it we could perhaps advise further.
If its just a question of searching for rows in a table which match this
criterion then you would not need to return a computed column at all, but
simply include the expression in the query's WHERE clause:

SELECT *
FROM YourTable
WHERE A-B>=2;

 

[KARL DEWEY]

In query design view grid add it in the Field row in a blank column like this

 

[Katie]

Thanks Ken.
I'll try to explain what i want to do.

i convene conferences and collate information for them, the information has
to be provided to the conference chairs a minimum of 2 days before the
conference date

i have a column for the date of the conference
i have a column for the date the information in recieved.

i have to provide stats on how often they are recieved within the correct
timescale.

there are lots of other conditions on these conferences that need to be
monitored too, so i have created a form to do this.

idealy i would like the colum "report available to chair" to automatically
tick a yes no box if the date recieved is more than or equal to 2 days prior
to conference. i suppose this is more of a time saver so that staff don't
have to work that out everytime they use the form. we convene upto1000
conferences a year so any shortcuts are a massive help

after writing this i suspect that maybe i'm trying to get this result in the
wrong way/place

The equivalent function in Access is the IIF function (Immediate IF), which
works in the same way as the Excel IF function. However, if you simply want
to return a value of TRUE or FALSE then you don't need to call the function
at all as the expression itself will evaluate to TRUE or FALSE. You can't do
what you ask and display TRUE or FALSE in the same column in a query in
datasheet view, however; you'd have to have a separate column with the
expression to evaluate the data in other columns:

SELECT A, B, A-B>=2 AS Result
FROM YourTable;

In design view you'd enter the computed result column as:

Result:[A]->=2

The values in the Result column would by default show as 0 or -1 which is
how Access implements Boolean FALSE and TRUE values. To show them as the
latter set the Result column's Format property to True/False in its
properties sheet.

This might not necessarily be the best way of achieving your objective,
however. If you'd care to post back with an explanation of what you want to
do rather than how you are trying to do it we could perhaps advise further.
If its just a question of searching for rows in a table which match this
criterion then you would not need to return a computed column at all, but
simply include the expression in the query's WHERE clause:

SELECT *
FROM YourTable
WHERE A-B>=2;

i've written a query but am struggling to get extra info i need for some
stats i want to produce. i would like to be able to write a formula along
these lines

IF((A-B)>=2,TRUE,FALSE)

i.e. IF(logical_test,value_if_true,value_if_false)

I was hoping then that i could search on the true false values returned. i
know this is an equation that Excel would recognise and assumed that Access
would too,it doesn't

this isn't a seperate expression i want to create that would give me an
extra column. i want this to display a result in the column i am typing in.
i do have the (A-B) bit named as EXPR 1: in a new column already an have
tried to use this reference too but it hasn't worked either.

 

[Ken Sheridan]

Firstly don't be tempted to include a column in the base table to indicate
this. The availability of the information by the two-day deadline is
something which can, and should, be computed on the fly, not stored. To
store it would constitute redundancy. Redundancy is to be avoided as it
leaves a database open to the risk of update anomalies.

You can do this in a query in the way I suggested, e.g.

SELECT *,
DATEDIFF("d",[Date Received], [ConferenceDate]) >= 2 AS [Report available to
chair]
FROM YourTable;

Note that I've used the DateDiff function here rather than a simple
subtraction. This is more reliable as date/time values can inadvertently
contain a time of day element, which will be unseen if the value is formatted
as a date, so a simple subtraction would not necessarily give the correct
result, whereas the DateDiff function does.

A form based on this query can include a check box bound to the [Report
available to chair] column in the query.

A further refinement could be to filter the form to show only those records
where [Report available to chair] is TRUE. To do this you could have a
button on the form with the following in its Click event procedure:

Me.Filter = "[Report available to chair] = True"
Me.FilterOn = True

You might have another button to show only records where its FALSE:

Me.Filter = "[Report available to chair] = False"
Me.FilterOn = True

To turn off the filter and show all records you can either use the built in
button on the toolbar or have another button on the form with the following
in its Click event procedure:

Me.FilterOn = False

You can probably get a lot of your aggregated data by queries too. For
instance a query to count the number of records where information was
received for a user entered conference date within the deadline would be
along these lines:

PARAMETERS [Enter Conference Date:] DATETIME;
SELECT [Conference Date], COUNT(*) AS NumberWithinDeadline
FROM YourTable
WHERE DATEDIFF("d",[Date Received], [ConferenceDate]) >= 2
AND [ConferenceDate] = [Enter Conference Date:]
GROUP BY [Conference Date];

Or the same thing can be done with the DCount function. You could for
instance have an unbound control on a form with the following as its
ControlSource property:

=DCount("*", "YourTable", " DateDiff("d",[Date Received], [ConferenceDate])

= 2 And [Conference date] = #" & Format([txtConferenceDate,"mm/dd/yy") & "#")

where txtConference Date is the name of an unbound text box control on the
form into which the date is entered. An even better option would be, rather
than having a text box, to have a combo box on the form which lists the dates
of all conferences, so a user simply selects one from the list. With a bit of
ingenuity you'd probably find you can compute most of the aggregate data you
need in similar ways to the above.

There are also various tricks you can use in reports to aggregate data
there. For instance you could have a report grouped by conference date which
gives the numbers of records within or outside the two day deadline for each
conference, and probably many other aggregated values for each conference.