Help with MS Access

[Andy_Hollywood]

I've been playing with Oracle and SQL Server now for a year or so and getting on fine, howvere someone asked me to do somehting in Microsoft Access for them, and i must say i'm stumped! as i can't seem to achieve what i would like.

I have a table, with a start date/time column, and a finsih date/time column.

I also have another column which needs to represent the number of hours between the start and the end, now i would have usually done this using a trigger, or somehting to work it out, but how do i do it in MS Access?! or how do i place a trigger on the table to do lots of work for me?1

cheers andy

[mendhak]

There are no triggers in Access, afaik.

You could create a macro for this, that populates the third field with the difference of the two, but I'm not familiar with VBA.

But hey, at least it'll lead you in the right direction.

And my condolences for the "degradation" to Access

[Andy_Hollywood]

thank you, i'm sat here on my chair rocking back and forth slowly!

luckily its only a temporary degradation.

Cheers for you help

Andy

[mendhak]

Lucky you. My chair is spot welded to the floor.

[Maxie_Max]

Or alternatively you could simply use the Datediff function in VBA to work out the differance in what ever format you want.

[Andy_Hollywood]

but the question is how do i do that, i know how to use VBA, but how do you do it so it runs the macro on insert?1

[Maxie_Max]

i will depend how you are getting the data into the table in the first place.....

if your entering the data through a form you can simply use datediff at that point to update the field

[Andy_Hollywood]

what if i'm entering directly into the table?!

i should really enter it seperatly and have the form do the working ?!

[Maxie_Max]

if you do it through a form you have a lot more control to ensure the data is being entered correctly....

on your form have a textbox or list box for each field and a submit button to actually commit the data.

when you commit the data you can open the table as a record set and update the fields including your hours difference field.

eg.

private sub button_clickevent

dim db as dao.database
dim rs as dao.recordset

set db = currentdb

set rs = db.openrecordset("mytable",dbopendynaset)

with rs
.addnew
!dtmdate = txtdate1
!dtmdate2 = txtdate2
!lnghours = datediff(h,txtdate1,txtdate2)
.update
end with

rs.close
set rs = nothing

db.close
set db = nothing

Hope this helps... should pretty much get you there

[Andy_Hollywood]

thats marvellous

thank you