Date last used

[bss100us]

Hello:

I have a simple Access 2000 database with 2 tables; tblSites and
tblLandspreading. I need to look up the date a site was last used from the
landspreading table and place that value in a field in the tblSites table.
Any help would be greatly appreciated.

 

[John W. Vinson]

Hello:

I have a simple Access 2000 database with 2 tables; tblSites and
tblLandspreading. I need to look up the date a site was last used from the
landspreading table and place that value in a field in the tblSites table.
Any help would be greatly appreciated.

Ummm...

No. You should NOT store this date redundantly in tblSites. The moment you add
a new record to tblLandspreading, the value now stored in tblSites *WILL BE
WRONG*.

Just calculate it on demand:

DMax("[DateUsed]", "[tblLandspreading]", "[SiteID]=" & [SiteID])

in a query for example.

John W. Vinson [MVP]

 

[bss100us]

Hi John:

Thanks for the quick comeback. I've been working with your formula today.
I've used it in both a query and as a control source on a form. I keep
getting an #Error message when attempting to use it.

Just a little aside, I'm not real experienced with Access development, but
our programmer left the company, and I was left with the task of this small
project of getting the last date used for a particular site.

If you would be so kind as to shed some light on what I may be doing wrong,
I would really appreciate it. Thanks in advance, and have a nice weekend.

Brian
--
Brian

Hello:

I have a simple Access 2000 database with 2 tables; tblSites and
tblLandspreading. I need to look up the date a site was last used from the
landspreading table and place that value in a field in the tblSites table.
Any help would be greatly appreciated.

Ummm...

No. You should NOT store this date redundantly in tblSites. The moment you add
a new record to tblLandspreading, the value now stored in tblSites *WILL BE
WRONG*.

Just calculate it on demand:

DMax("[DateUsed]", "[tblLandspreading]", "[SiteID]=" & [SiteID])

in a query for example.

John W. Vinson [MVP]

 

[John W. Vinson]

If you would be so kind as to shed some light on what I may be doing wrong,
I would really appreciate it. Thanks in advance, and have a nice weekend.

If you'll be so kind as to tell me what you're *doing* - posting the name of
your table, the names of the relevant fields, and the expression that you're
using - then I might have a chance at seeing what you're doing *wrong*.
Remember, I'm off in my office in Idaho, and cannot see your screen!

John W. Vinson [MVP]

 

[bss100us]

John:

I have 2 tables in this database.

tblSites with the following fields:

SiteID
DNR_ID
Owner

tblLandspreading with the following fields:

DNR_ID
Date
Time
Load#

What I'm trying to do is write a query that would report the last date that
each site was used. I'm also adding a text field to a form for the tblSites
table that would do the same for each individual site record.

I used the following expression:

DMax("[Date]","[tblLandspreading]","[SiteID]=" & [SiteID]).

Thank you for your assistance.

 

[John W. Vinson]

tblLandspreading with the following fields:

DNR_ID
Date
Time
Load#

What I'm trying to do is write a query that would report the last date that
each site was used. I'm also adding a text field to a form for the tblSites
table that would do the same for each individual site record.

I used the following expression:

DMax("[Date]","[tblLandspreading]","[SiteID]=" & [SiteID]).

Thank you for your assistance.

Since there is no field named SiteID in tblLandSpreading, DMax() won't be able
to magically find it.

Is DNR_ID the Primary Key of tblLandSpreading? Do you have ANY link between
tblSite and tblLandspreading? *Maybe* you need to change this to use DNR_ID
instead of SiteID, but it's not clear to me how your tables are related, so
I'm not sure!

John W. Vinson [MVP]

 

[bss100us]

There is no primary key defined in tblLlandspreading. DNR_ID is indexed with
duplicates ok. They didn't set a primary key up in tblSites either. The
DNR_ID in that table is Indexed with no duplicates.

The relationship is one to many on the DNR_ID key in tblSites to
tblLandspreading respectively. Hope this helps.
--
Brian

tblLandspreading with the following fields:

DNR_ID
Date
Time
Load#

What I'm trying to do is write a query that would report the last date that
each site was used. I'm also adding a text field to a form for the tblSites
table that would do the same for each individual site record.

I used the following expression:

DMax("[Date]","[tblLandspreading]","[SiteID]=" & [SiteID]).

Thank you for your assistance.

Since there is no field named SiteID in tblLandSpreading, DMax() won't be able
to magically find it.

Is DNR_ID the Primary Key of tblLandSpreading? Do you have ANY link between
tblSite and tblLandspreading? *Maybe* you need to change this to use DNR_ID
instead of SiteID, but it's not clear to me how your tables are related, so
I'm not sure!

John W. Vinson [MVP]

 

[John W. Vinson]

There is no primary key defined in tblLlandspreading. DNR_ID is indexed with
duplicates ok. They didn't set a primary key up in tblSites either. The
DNR_ID in that table is Indexed with no duplicates.

The relationship is one to many on the DNR_ID key in tblSites to
tblLandspreading respectively. Hope this helps.
--

Try

DMax("[Date]","[tblLandspreading]","[DNR_ID]=" & [DNR_ID])

then.

John W. Vinson [MVP]