Avatar of seamus9909
seamus9909 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
Microsoft Access

Avatar of undefined
Last Comment
als315

8/22/2022 - Mon
als315

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
ASKER
seamus9909

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
als315

Can you upload sample DB or at least show SQL of your query?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
seamus9909

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"));
als315

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
ASKER
seamus9909

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"));
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
seamus9909

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
als315

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.
ASKER
seamus9909

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
als315

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question