Question Please Help with My Control Source code

[brittaink]

Hi,
I have a table called tblChecks. In this table I have 5 fields:
id...........................autonumber
_checktype..............number (link to tblListChecks)
_checker..................number (link to tblUsers)
date.......................Date/Time
Time.......................Date/Time

When the table is populated I have multiple values for the check type for example: 1= tool checks 2=tape checks

my table would have an entry for tool checks every day and an entry for tape checks every week.

What I am trying to do is, on my form create some textboxes which will show the next time that the check has to be completed for example the text box for toolchecks would show a value similar to dateadd(d,1,[tblChecks]![Date]) this would then show 1 day on from the last toolcheck.

The problem comes that I first need to pick the date of the last toolcheck from the table to perform the dateadd function to, and then I need to show this in the unbound text box.

If anyone can enlighten me on the VBA code for this I would be most grateful.

Many Thanks in advance

Keith Brittain

 

[neileg]

Use the DMax function to find the latest date.

 

[brittaink]

neileg,
Thank you for the advice. Using the SQL:
SELECT Max(tblchecks.date) AS Maxdate FROM tblchecks WHERE (((tblchecks.[_checktype])=1));
I can extract the correct date for use.

The 2 problems that I have left are that:
1. I can not add a day to that value, or a month for example using the dateadd function afterwards

2. I can not populate the finished value into an ubound text box afterwards.

Please Help!!

Keith

 

[neileg]

Have you looked at DMax in the help file? It basically does the same as your query is doing but returns a single value, not a dataset. You can then apply DateAdd to that.