-
Jun 1st, 2010, 11:00 PM
#1
Thread Starter
Member
[RESOLVED] DMax Date Text box function on continuous form with two where clauses
I created a continuous form frmStep10a where I want a text box SignaturesComplete to get the last date that an employee entered a signature for a document. I have it set up as follows:
tblDocInfo: docID (pk); DatePostedSharepoint
tblDocWorkFlow: dwfDocID (fk); dwfTitleNo (fk); dwfDateComplete
tblWorkFlowTitles: wrfTitleID (pk) WHERE wrfTitleID = 2 (Signature Work Flow Title Number)
I am trying to get the textbox named SignaturesComplete on my continuous form frmStep10a to get the maximum date value in the field dwfDateComplete from tblDocWorkFlow where the wrfTitleID = 2 (limits it to the Signature work flow) for each document (dwfDocID = docID).
There are usually 3 signatures for each document and I want to calculate how many days there are in between the last signature and the date it is posted to sharepoint.
The form's control source is tblDocInfo, it is a continuous form that also has a text box for the field DatePostedSharepoint. Another text box named difTargetOutputStep10A calculates the Difference of Days between the Date Posted to Sharepoint and the Date Signatures Complete with the control source =DateDiff("d",[SignaturesComplete],[DatePostedSharepoint]).
I know the DateDiff works on other forms, I just can't get the SignaturesComplete to get the maximum date from the tblDocWorkFlow for a docID's associated entries for the Signature Work Flow Title. What I tried last was:
Code:
Public Function getMaxSigDate() As Date
Const fldName = "dwfDateComplete"
Const tblName = "tblDocWorkFlow"
Dim strWhere As String
strWhere = dwfTitleNo & " = " & 2 And Me.docID & " = " & dwfDocID
getMaxSigDate = Nz(DMax(fldName, tblName, strWhere), 0)
Me.SignaturesComplete = getMaxSigDate
End Function
I was using the Nz function in case there are null values.
Any ideas where I missed the boat on this one???
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|