Forms and Date Add

[Wesley]

Hiyas.

I've asked this question before, and i remember getting an outstanding answer from the guru's of this board.

Basically, i need the answer again however.

Problem:
i am setting up a database to track equipment (by asset number, description, etc.) and have fields that i can enter a date that each piece of equipment last had preventative maintenance performed on it. Maintenance can be performed at several different intervals, weekly, biweekly, monthly, quarterly, semi-annually, and annually.

What i would like, is a field that represents the last time a particular piece of machinery was maintained. A field for a user to select what particular interval of maintenance is required for that piece of equipment, and then another field which will list the date the maintenance is next due for that piece of equipment.

Currently, i have managed to get fields which let me enter the date maintenance was last performed, a combo box that lists the desired intervals and a box which lists the date maintenance will next be due. Unfortunately, it will only give the "Date Next Due" as 1 week beyond the current date.

So, there in lies the crux. Anyone able to give me a hand with this?

(i am using the dateadd function, but, i think my problem lies somewhere in the relation between the fields in the combo box and the periodicities)

formula i'm using is DateNextDue=DateAdd([Periodicity],1,[DateMaintenancePerformed])

now, one of the things that i can see being an issue is the "1"... since biweekly would be 2 weeks, Semi Annual would be 6 months.

help pwease!

 

[Wesley]

sorry,

one other question.

with a combo box, is there a way so that the empty white space below the last entry in the combo box does not just scroll endlessly?

i.e. when i pull down a combo box from my form, i don't see any blank white space below the last entry?

 

[KevinM]

Have a look at the DateAdd function in the HelpFile.
It lists all the settings for the time intervals.

I would create a look up table consiting of the setting you want, an integer for the value and a 'user friendly descripton' as well as an autoID (PK) which you will link to and store in your equipment table.

This gives flexibilty, as you may need to add different time intervals at a later date.

In your query you simply include the linked lookup table and reference the relevant fields in your DateAdd function.

hth

 

[Wesley]

/grins
i am using the DateAdd function correctly.

What my formula is currently doing, is going to the table that i have everything listed on (all the equipment stuff, etc. etc.)

but, it seems to be just pulling whatever the code (be it, ww, or q, or m, or yyyy) in the row for whatever record i have.

Are you saying make another table, that would contain the Periodicity code, Periodicity descriptor (that would feed my combo box with user friendly terms)?

 

[KevinM]

As regards you combo box problem....

It is probably returning null values.
Query these out in the combo's rowsource.

 

[KevinM]

Back to the Date Add problem....

You have seven different intervals of varying degrees which you couldn't possibly include in one single DateAdd function with one particular interval.

Create a 'Date Interval' table with the following fields....

[DateIntervalID] (pk, autonumber)
[IntervalSetting]
[Interval]
[IntervalDesc]

A typical record say for quarterly would be...

[IntervalSetting]=q
[Interval]=1
[IntervalDesc] Quartely

For Semi-Annual...

[IntervalSetting]=m
[Interval]=6
[IntervalDesc] Semi-Annually

And so on

Link to and store the DateIntervalID in your equip table via a combo box if you like. Simply select the appropriate interval for each part.

In a query include equipdate and the new interval table.
Include the [DateMaintenancePerformed] from the equip table AND [IntervalSetting] and [Interval] from the Interval table in the query grid amongst other fields you want to see.

Create an expression....

NextDueDate: DateAdd([IntervalSetting],[Interval],[DateMaintenan
cePerformed])

That's it!

Beauty of it is you can add as many types of intervals as you like in the interval table and then simpy add the ID to the Equip table.

 

[Wesley]

What i have now
are two tables
one has the generic info (equipment name, date last maintenance done, etc.)
the other is called PeriodTab and contains three fields
1st field is Periodicity, next is the operator designator for weeks, months, years, etc., next is a field i called "number"

Formula i am using now is:
=DateAdd([PeriodTab].[Periodicity],[PeriodTab].[Number],[ServiceRecords].[ServiceDate])

I'm guessing that i am somehow not referencing the table correctly.

I'm really pretty darn green when it comes to access, and it would be safe to say, that i haven't the slightest idea of what i'm doing, (but, i'm trying awfully hard to learn)

 

[KevinM]

You're on the right tracks, didn't realise you already have the 'Interval' table that I mentioned above.

How are the two tables linked?

Post a cutdown version of the db if you can

 

[KevinM]

You will need to store the ID from your period table in your
equip table as a one to many rel.

Are you storing this?

As long as each equip record has this ID then it should work ok.

 

[directormac]

Hiyas.

I've asked this question before, and i remember getting an outstanding answer from the guru's of this board.

Basically, i need the answer again however.

On a side note, Wesley, if you're asking a question you know has already been answered (seeing as how you asked it yourself and all), you could search the archives, say using your own username, and turn up the answer you got last time.

--Reminder Mac

(OTOH, KevinM - excellent advice given on your part...)

 

[Wesley]

actually, i did do a search with my username, i couldn't find any posts.. i asked this question late last year, and i think its scrolled off of the forums.

i think my problem is lying on the linking of the two tables, i'm not sure i'm asking for the sources of information correctly.

e.g. i don't think i'm specifying where the information is coming from, and where its going to properly.

what would be the easiest way to show you exactly what i've done here?

 

[KevinM]

Post a cutdown version of your db, compacted & zipped.

 

[directormac]

actually, i did do a search with my username, i couldn't find any posts.. i asked this question late last year, and i think its scrolled off of the forums.

Nope, still there. Click on the link I put in my post above. But first, u/l the condensed db so KevinM can continue giving you excellent help.

--Replay Mac

 

[KevinM]

Thanks for the kind words directormac

Wesley

I can't give you anymore detail than I already have.
Everything you need to know is in my replies.

Have a look at your links and re-create them the way I explained.