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
seamus9909Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
0
seamus9909Author Commented:
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
0
als315Commented:
Can you upload sample DB or at least show SQL of your query?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

seamus9909Author Commented:
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"));
0
als315Commented:
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
0
seamus9909Author Commented:
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"));
0
seamus9909Author Commented:
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
0
als315Commented:
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.
0
seamus9909Author Commented:
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
0
als315Commented:
You should:
1. Rename module from GetDate to GetDate1 (or something else). Module name and function name should differ
2. ServiceID is lookup field and really there are numbers, not text:
ServiceID      Type
1      Film
2      Medical Records/Peer Review
3      IME
4      Consult
5      Neuro-Psych
6      ACT 6 Initial
7      IME_Reeval
8      FCE
9      IME Rescheduled
10      Addendum
11      ACT 6 Recon
12      ERR
Use 3 for IME, 12 for ERR. I can't find RR (may be 2?) in this table, set it to proper value
Public Function GetDate(ServiceID As String, DateofIME As Variant, DateCreated As Variant) As Variant
GetDate = Null
If IsNull(DateofIME) Then Exit Function
If ServiceID = 3 Then ' IME
            GetDate = DateAdd("d", 7, DateofIME)
ElseIf ServiceID = 2 Then ' RR?
            GetDate = DateAdd("d", 10, DateCreated)
ElseIf ServiceID = 12 Then ' Err
            GetDate = DateAdd("d", 7, DateCreated)
End If
End Function

Open in new window

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([qryUSAACLaims].[ServiceID],[qryUSAACLaims].[Date of IME],[TblDocuments].[DateCreated]) 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, GetDate([qryUSAACLaims].[ServiceID],[qryUSAACLaims].[Date of IME],[TblDocuments].[DateCreated])
HAVING (((Last(TblDocuments.ReportType)) Like "*RecordsReview" Or (Last(TblDocuments.ReportType)) Like "*ime_doctor"));

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.