dcsimg
Results 1 to 4 of 4

Thread: [RESOLVED] DMax Date Text box function on continuous form with two where clauses

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Resolved [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???

  2. #2
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: DMax Date Text box function on continuous form with two where clauses

    Perhaps like this:
    strWhere = "dwfTitleNo = 2 And dwfDocID = " & Me.docID
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    Baby Steps a guided tour
    IsDigits() and IsNumber() functions Wichmann-Hill Random() function >> and << functions for VB CopyFileByChunk

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: DMax Date Text box function on continuous form with two where clauses

    Thank you I will try that tomorrow at work and let you know how it worked out for me. Thanks again!

  4. #4

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    34

    Re: DMax Date Text box function on continuous form with two where clauses

    Code:
    Private Function getMaxSigDate() As Date
      Const fldName = "dwfDateComplete"
      Const tblName = "tblDocWorkFlow"
      Dim strWhere As String
      strWhere = "dwfTitleNo = 2 And dwfDocID = " & Me.docID
      getMaxSigDate = Nz(DMax(fldName, tblName, strWhere), 0)
      Me.SignaturesComplete = getMaxSigDate
    End Function
    This is what I tried next. I can't get a date to enter into the field SignaturesComplete. What should I use as the control source?

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
  •  



Featured


Click Here to Expand Forum to Full Width