Link to home
Start Free TrialLog in
Avatar of Michael Dean
Michael DeanFlag for United States of America

asked on

how to populate an unbound Text Box

I have a record source created by a query that I want to use on a report. Another item on the report needs to be Due Date (Unbound Text Box) and needs to be calculated using the following rules.



Service ID        Due Date
IME                  Due date will be 7 calendar days from the Date of IME (it will be blank if   [/qu   no                    IME date has been entered yet)
RR                  Due date will be 10 calendar days from the Date the RR_Doctor letter is created.
ERR                Due Date will be 7 calendar days from the date the RR_Doctor letter is created.
Status-Report.xlsx
Avatar of als315
als315
Flag of Russian Federation image

You can create function like this:
Public Function GetDate(ServiceID as string, DateofIME as Variant, DatetheRR_Doctor as Variant) as Variant
GetDate = Null
If isnull(DateofIME) then exit Function
If ServiceID = "IME" then
            GetDate = DateAdd("d", 7, DateofIME)
elseif ServiceID = "RR" then
            GetDate = DateAdd("d", 10, DatetheRR_Doctor)
elseif ServiceID = "ERR" then
            GetDate = DateAdd("d", 7, DatetheRR_Doctor)
end if
End Function

Open in new window

You should place this function into Module
Avatar of Michael Dean

ASKER

Where do you use the function?  In the query?  When I add this in the query I get "Circular Reference caused by  GetDate.  GetDate is the module I created from what you sent me
Can you upload sample DB or at least show SQL of your query?
SELECT DISTINCTROW First(qryUSAACLaims.FileNo) AS FirstOfFileNo, [qryUSAAClaims.lname] & "," & [qryusaaclaims.fname] AS Patient, Last(qryUSAACLaims.[Claim#]) AS [LastOfClaim#], [customers.first name] & " " & [customers.last name] AS Adjustor, [TblAis.first name] & " " & [TblAIS.last name] AS AISContact, qryUSAACLaims.ServiceID, qryUSAACLaims.Specialty, qryUSAACLaims.[Date/Rec'd], qryUSAACLaims.[Date of IME], Customers.Company, Last(TblDocuments.ReportType) AS LastOfReportType, Last(TblDocuments.DateCreated) AS LastOfDateCreated, qryUSAACLaims.DateofService, [Getdate] AS DueDate
FROM (Customers INNER JOIN (qryUSAACLaims INNER JOIN TblDocuments ON qryUSAACLaims.InvoiceID = TblDocuments.InvoiceID) ON Customers.ID = qryUSAACLaims.[Client - Contact]) INNER JOIN tblAIS ON qryUSAACLaims.[AIS-Contact] = tblAIS.ID
GROUP BY [qryUSAAClaims.lname] & "," & [qryusaaclaims.fname], [customers.first name] & " " & [customers.last name], [TblAis.first name] & " " & [TblAIS.last name], qryUSAACLaims.ServiceID, qryUSAACLaims.Specialty, qryUSAACLaims.[Date/Rec'd], qryUSAACLaims.[Date of IME], Customers.Company, qryUSAACLaims.DateofService
HAVING (((Last(TblDocuments.ReportType)) Like "*RecordsReview" Or (Last(TblDocuments.ReportType)) Like "*ime_doctor"));
Getdate function need 3 parameters. Call it as:
DueDate: GetDate(qryUSAACLaims.ServiceID, qryUSAACLaims.[Date of IME],  qryUSAACLaims.[Date/Rec'd])

Open in new window

I'm not sure in 3rd parameter - check it and replace with correct value
I entered those parameters into the query.  But it doesn't run it actually doesn't return an error but just sits there on the screen.

SELECT DISTINCTROW First(qryUSAACLaims.FileNo) AS FirstOfFileNo, [qryUSAAClaims.lname] & "," & [qryusaaclaims.fname] AS Patient, Last(qryUSAACLaims.[Claim#]) AS [LastOfClaim#], [customers.first name] & " " & [customers.last name] AS Adjustor, [TblAis.first name] & " " & [TblAIS.last name] AS AISContact, qryUSAACLaims.ServiceID, qryUSAACLaims.Specialty, qryUSAACLaims.[Date/Rec'd], qryUSAACLaims.[Date of IME], Customers.Company, Last(TblDocuments.ReportType) AS LastOfReportType, Last(TblDocuments.DateCreated) AS LastOfDateCreated, qryUSAACLaims.DateofService
FROM (Customers INNER JOIN (qryUSAACLaims INNER JOIN TblDocuments ON qryUSAACLaims.InvoiceID = TblDocuments.InvoiceID) ON Customers.ID = qryUSAACLaims.[Client - Contact]) INNER JOIN tblAIS ON qryUSAACLaims.[AIS-Contact] = tblAIS.ID
GROUP BY [qryUSAAClaims.lname] & "," & [qryusaaclaims.fname], [customers.first name] & " " & [customers.last name], [TblAis.first name] & " " & [TblAIS.last name], qryUSAACLaims.ServiceID, qryUSAACLaims.Specialty, qryUSAACLaims.[Date/Rec'd], qryUSAACLaims.[Date of IME], Customers.Company, qryUSAACLaims.DateofService, GetDate([qryUSAACLaims].[ServiceID],[qryUSAACLaims].[Date of IME],[tbldocuments].[DateCreated])
HAVING (((Last(TblDocuments.ReportType)) Like "*RecordsReview" Or (Last(TblDocuments.ReportType)) Like "*ime_doctor"));
Im looking at the module  (GetDate) and it says

If IsNull(DateofIME) Then Exit Function    DateofIME would be Null for some records specifically ServiceID of ERR and RR.  So not sure why the function would exit?

Public Function GetDate(ServiceID As String, DateofIME As Variant, DateCreated As Variant) As Variant
GetDate = Null
If IsNull(DateofIME) Then Exit Function
If ServiceID = "IME" Then
            GetDate = DateAdd("d", 7, DateofIME)
ElseIf ServiceID = "RR" Then
            GetDate = DateAdd("d", 10, DateCreated)
ElseIf ServiceID = "ERR" Then
            GetDate = DateAdd("d", 7, DateCreated)
End If
End Function
Function will return Null If DateofIME is Null and in this case other criteria will not be checked.
May be you can prepare some sample DB? Remove all unrelated tables, forms etc., remove all sensitive data, compact DB and upload it.
Right so that is wrong.  here is the DB, you will have to link the FE to the BE.  THere is no sensitive data.  The report and underlining query is  Status Report.

Thanks so much.
DMC-Backup-Backup.zip
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial