Code builder for Access 2003 - Help urgently required

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deaconj999 - 17 Mar 2007 20:30 GMT

Hi All,

I have a simple request, which involves 3 fields only to help me iwth
my main job as a fire inspector.
I am writing a simple database that requires a date of last fire
inspection, frequency for reinspection and future inspection date
based on the frequency. I am self taught on both ACCESS and EXCEL
only.

EXCEL no problems I used this

=IF(O54="HIGH",SUM(P54+365),IF(O54="MED",SUM(P54+1095),IF(O54="LOW",SUM(P54+1825),IF(O54="N/
A",TODAY()))))

But Access I'm sure is a little different.

Field One You enter a choice of 3 re inspection frequencies ( [Risk
factor] ) from a drop down on the form. They are High Annual, Med - 3
Yearly, Low - 5 Yearly. I then enter the date of last insprection and
based on the frequency want the code for the next inspection to
calculate and add either 365 1095 or 1825 days to the last inspection
date entered . So far I have this in the properties

=[Date of Last FSMP]+365

But it does not take into account the choice of frequency. Ideally I
would like it to calculate the Last FSMP (FSMP is fire inspection)
based on the choice in the Risk Factor field and display in the [Date
of Next FSMP field]

You get the idea.

I suppose all I need is some kind person to rewrite my Excel formulae
in Access format and I should be able to do the rest

PLEASE HELP - COMMUNITY FIRE SAFETY IS AT PERIL !!!!!!!!!!!!
Joe

Reply to this Message

Bob Hairgrove - 17 Mar 2007 20:41 GMT

>EXCEL no problems I used this
>
[quoted text clipped - 11 lines]
>
>=[Date of Last FSMP]+365

In Access you could use the IIf() function which does exactly the same thing as
the Excel "IF()". The reason they didn't call it "If" is because that is also
used in VBA as a keyword (I suppose...)

However, there is also the Switch() as well as the Choose() function which are
easier to use once you have three or more criteria.

Anyway, you might try this with IIf:

=IIf([Risk factor]="HIGH", [Date of Last FSMP]+365, IIf([Risk factor]="MED",
[Date of Last FSMP]+1095, IIf([Risk factor]="LOW", [Date of Last FSMP]+1825,
IIf([Risk factor]="N/A", Date()))))

(caveat emptor because of the parentheses, of course!)

--
Bob Hairgrove
NoSpamPlease@Home.com

Reply to this Message

deaconj999 - 17 Mar 2007 22:03 GMT

> >EXCEL no problems I used this
>
[quoted text clipped - 30 lines]
> Bob Hairgrove
> NoSpamPle...@Home.com

Bob,

Thank you so much for the response it works very well.

Thanks

Reply to this Message

fredg - 17 Mar 2007 22:26 GMT

> Hi All,
>
[quoted text clipped - 33 lines]
> PLEASE HELP - COMMUNITY FIRE SAFETY IS AT PERIL !!!!!!!!!!!!
> Joe

I'll assume you really want 1, 3, or 5 years, as 365 days is close to,
but not really a year, and you'll get an incorrect date if there is a
leap year within the time period.

In an unbound text control on your form:

=IIf([Risk Factor] = "High",DateAdd("yyyy",1,[Date of Last
FSMP],IIf([Risk Factor = "Med",DateAdd("yyyy",3,[Date of Last
FSMP]),DateAdd("yyyy",5,[Date of Last FSMP])))

I would simplify this, as any change in the available scheduling, i.e.
add a 4 year or 2 year schedule, would necessitate modifying this
code.

Create a tblFrequency.
Field1 Number datatype, Integer Field Size
Field2 Text

Field1 Field2
1 "High"
3 "Med"
5 "Low"

The value stored in the Field1 field is what should be stored in the
Frequency combo box bound column, but the Field2 text value is what is
displayed in the combo box.
Here's how:

Set the Combo Rowsource property to:

Select Field1,Field2 From tblFrequency;

Then set the Combo column count to 2
Bound Column to 1
Column Widths to:
0";1"
Set the Control Source property to the [Risk Factor] field. Make sure
you have changed the [Risk Factor] field's datatype to number
(Integer).

Then you could simply use:

=DateAdd("yyyy",[Risk Factor],[Date of Last FSMP])

If you ever need to add additional year inspection periods, simply add
them to the table.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

deaconj999 - 18 Mar 2007 18:53 GMT

> > Hi All,
>
[quoted text clipped - 86 lines]
>
> - Show quoted text -

Fred I have treid to use your unbound formuae and I cannot get it to
work at all. i have treid the extra parenthesis here or there but
still no luck.

I have used with success

=IIf([Risk factor]="HIGH - annual",[Date of Last FSMP]+365,IIf([Risk
factor]="MED - 3 yearly",[Date of Last FSMP]+1095,IIf([Risk
factor]="LOW - 5 yearly",[Date of Last FSMP]+1825,IIf([Risk factor]="N/
A",Date()))))

but would rather it take into accounbt the leap year, you will see it
is modified from the original on this discussion and I am using the
medium date format in my databse, I wold rather not mess about
creating another table so please advise on the way I can change this
formulae to count the years as per your suggestion.

Anyone else got any ideas please

Reply to this Message

fredg - 18 Mar 2007 22:19 GMT

>>> Hi All,
>>
[quoted text clipped - 105 lines]
>
> Anyone else got any ideas please

It looks as though I left out a parenthesis after the first DateAdd
function. Sorry.
Let's try it again.

=IIf([Risk Factor] = "High",DateAdd("yyyy",1,[Date of Last
FSMP]),IIf([Risk Factor = "Med",DateAdd("yyyy",3,[Date of Last
FSMP]),DateAdd("yyyy",5,[Date of Last FSMP])))

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message