Report pulling incorrect information from linked table

[blewis81]

Unfortunately, I know enough about Access to get myself into trouble (i.e. not enough)...in fact I'm not even sure if I can explain my issue and ask the right questions. Please bear with me. (This is an inherited DB and I am NO coder)



I have a Contract Number that is linked to multiple project numbers. The first 10-11 digits of the Project number (which is the same as the WBS number) are the same (21 total digits). When I select the project number I want....the report is pulling the first of the list of contract numbers (I think).

I believe my issue is in the code "StrProjectNo = Left([WBS], 10)" I.e. it is reading the first 10 digits ...when the contract number I want pulled is different at the 12th digit and on. However, if I change the string to 12, I get a "94: Invalid Null" error and then nothing works.

Happy to supply any additional information and thankful for any help.

[GinaWhipp]

Then why not pull from the Right since those are the unique value, i.e. StrProjectNo = Right([WBS], 10)

[Micron]

If the left part length will be consistent but the right part will not be, then use the Mid function, otherwise if number length on the right side ever exceeds the current (12?) you will start losing the leftmost digits of the right side portion.
StrProjectNo = Mid([WBS], 10) will give you everything after the 9th character.

[blewis81]

I tried both Right and Mid as well as reading to the 12th digit vice 10th.. No longer getting null error however, still continues to pull the incorrect contract number. As clarification, I have multiple contract numbers. Each contract number could potentially have multiple project numbers associated. In this particular case the project number I'm referring to is similar to four other project numbers linked to a contract number (first 11 digits same). Continues to pull the incorrect contract number (related to four other project numbers).

[GinaWhipp]

Hmm, something sounds off, post the SQL of the query.

[Micron]

...as well as some sample data. All I can see thus far is a written explanation of what your data looks like, which we may not be interpreting correctly. When you post that and the expression you tried, posted results of that expression might help a lot.

[blewis81]

Option Compare Database
Option Explicit

Private Sub ApprovedDate_AfterUpdate()
Me.Status = "Approved"
'Me.ApprovedDateMod = fOSUserName() & "; " & Date
End Sub

Private Sub ApprovedDate_DblClick(Cancel As Integer)
Me.ApprovedDate = Date
End Sub

Private Sub AwaitingReceived_Click()
DoCmd.ApplyFilter "qry_Ordered_NotReceived_Filter"
End Sub

Private Sub Command66_Click()
DoCmd.OpenTable ("WBS")

End Sub

Private Sub Ctl1149_Click()
On Error GoTo Error_Handler
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim FileName1149 As String
Dim FileName1149PG2 As String
Dim Attach1149 As String
Dim Attach1149PG2 As String
Dim SubjectText As String
Dim BodyText As String

Refresh
If IsNull(Me.WBS) Then
MsgBox "WBS is required."
Me.WBS.SetFocus
Exit Sub
End If

SubjectText = "1149 " & Me.Procurement_ID & " for " & Me.WBS
BodyText = "1149 attached."
FileName1149 = Me.Procurement_ID & "_1149"
Attach1149 = "G:\Procurement\ProcurementDatabase\NonPOs" & FileName1149 & ".pdf"
FileName1149PG2 = Me.Procurement_ID & "_1149PG2"
Attach1149PG2 = "G:\Procurement\ProcurementDatabase\NonPOs" & FileName1149PG2 & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_1149", acFormatPDF, Attach1149, False
DoCmd.OutputTo acOutputReport, "rpt_1149_Page2", acFormatPDF, Attach1149PG2, False

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItemFromTemplate("G:\Procurement\ ProcurementDatabase\Data\ProcurementRequest.oft")

With objEmail
.Subject = SubjectText
.Body = BodyText
.Display
.Attachments.Add Attach1149
.Attachments.Add Attach1149PG2
End With

Kill Attach1149
Kill Attach1149PG2


Error_Handler:
If Err = 2501 Or Err = 0 Then
Exit Sub
End If
MsgBox Err & ": " & Err.Description
End Sub

Private Sub CustomerRequestDate_DblClick(Cancel As Integer)
Me.CustomerRequestDate = Date
End Sub

Private Sub DeliveredDate_AfterUpdate()
Me.Status = "Delivered"
'Me.DeliveredDateMod = fOSUserName() & "; " & Date
End Sub

Private Sub DeliveredDate_DblClick(Cancel As Integer)
Me.DeliveredDate = Date
End Sub

Private Sub Email_Request_Click()
On Error GoTo Error_Handler
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim FileNameNonPO As String
Dim FileNameList As String
Dim AttachNonPO As String
Dim AttachList As String
Dim SubjectText As String
Dim BodyText As String
Dim datestart As String

If IsNull(Me.RoutedDate) Then
Me.RoutedDate = Date
End If

Refresh
If IsNull(Me.WBS) Then
MsgBox "WBS is required."
Me.WBS.SetFocus
Exit Sub
End If

If Me.PR_Required = True Then
MsgBox "This procurement requires a PO. A Non-PO form will not be generated."
SubjectText = "Procurement " & Me.Procurement_ID & " for " & Me.WBS
BodyText = "Procurement request attached."
Else
SubjectText = "Non-PO APPROVAL (" & Me.WBS & ") " & Me.Procurement_ID & " - " & Me.Vendor
BodyText = "Non-PO request attached."
FileNameNonPO = Me.Procurement_ID & "_NonPO"
AttachNonPO = "G:\Procurement\ProcurementDatabase\NonPOs" & FileNameNonPO & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_Non-PO", acFormatPDF, AttachNonPO, False
End If

FileNameList = Me.Procurement_ID & "_Item_List"
AttachList = "G:\Procurement\ProcurementDatabase\NonPOs" & FileNameList & ".xls"
DoCmd.OutputTo acOutputReport, "rpt_Current_List", acFormatXLS, AttachList, False

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItemFromTemplate("G:\Procurement\ ProcurementDatabase\Data\ProcurementRequest.oft")

With objEmail
.Subject = SubjectText
.Body = BodyText
.Display
If Me.PR_Required = False Then
.Attachments.Add AttachNonPO
End If
.Attachments.Add AttachList
End With

If Me.PR_Required = False Then
Kill AttachNonPO
End If
Kill AttachList


Error_Handler:
If Err = 2501 Or Err = 0 Then
Exit Sub
End If
MsgBox Err & ": " & Err.Description
End Sub

Private Sub Form_AfterUpdate()
'Me.RecordDateMod = fOSUserName() & "; " & Date
End Sub

Private Sub GovApprovedDate_DblClick(Cancel As Integer)
Me.GovApprovedDate = Date
End Sub

Private Sub GovRoutedDate_DblClick(Cancel As Integer)
Me.GovRoutedDate = Date
End Sub

Private Sub InvoiceDate_DblClick(Cancel As Integer)
Me.InvoiceDate = Date
End Sub

Private Sub NonPO_AllocatedDate_DblClick(Cancel As Integer)
Me.NonPO_AllocatedDate = Date
End Sub

Private Sub OrderDate_AfterUpdate()
Me.Status = "Ordered"
'Me.OrderDateMod = fOSUserName() & "; " & Date
End Sub

Private Sub OrderDate_DblClick(Cancel As Integer)
Me.OrderDate = Date
End Sub

Private Sub PendingApproval_Click()
DoCmd.ApplyFilter "qry_Pending_Approval_Filter"
End Sub

Private Sub ReceivedDate_AfterUpdate()
Me.Status = "Received"
'Me.OrderDateMod = fOSUserName() & "; " & Date
End Sub

Private Sub ReceivedDate_DblClick(Cancel As Integer)
Me.ReceivedDate = Date
End Sub

Private Sub RoutedDate_DblClick(Cancel As Integer)
Me.RoutedDate = Date
End Sub

Private Sub Unlock_Click()
Me.Form.AllowEdits = True
Me.Form.AllowAdditions = True
Me!frm_Procurement_Items.Form.AllowAdditions = True
Me!frm_Procurement_Items.Form.AllowEdits = True
End Sub

Private Sub WBS_AfterUpdate()
On Error GoTo Error_Handler
Dim PMID As String
Dim ContractsID As Variant
Dim GovPOCID As Variant
Dim StrProjectNo As String
Dim GovPoc_Fname As Variant
Dim GovPoc_Lname As Variant
Dim GovPocName As Variant
Dim TaskLeadID As Variant
Dim TaskLead_Fname As Variant
Dim TaskLead_Lname As Variant
Dim TaskLeadName As Variant

Me.Customer = ""
Me.TaskLead = ""
Me.PM = ""
Me.Contracts = ""

'StrProjectNo = [WBS]
StrProjectNo = Left([WBS], 10)

PMID = DLookup("[ProjectManager]", "qry_Current_ProjectInfo", "[ProjectNumber] = '" & StrProjectNo & "'")
'PM_Fname = DLookup("[FirstName]", "Personnel", "[PersonnelID] = " & PMID)
'PM_Lname = DLookup("[LastName]", "Personnel", "[PersonnelID] = " & PMID)
'PMName = PM_Lname & ", " & PM_Fname
Me.PM = PMID

ContractsID = DLookup("[ContractSpecialist]", "qry_Current_ProjectInfo", "[ProjectNumber] = '" & StrProjectNo & "'")
'Contracts_Fname = DLookup("[FirstName]", "Personnel", "[PersonnelID] = " & ContractsID)
'Contracts_Lname = DLookup("[LastName]", "Personnel", "[PersonnelID] = " & ContractsID)
'ContractsName = Contracts_Lname & ", " & Contracts_Fname
Me.Contracts = ContractsID

GovPOCID = DLookup("[GovernmentTaskLead]", "qry_Current_POC", "[WBS] = '" & [WBS] & "'")
GovPoc_Fname = DLookup("[FirstName]", "Personnel", "[PersonnelID] = " & GovPOCID)
GovPoc_Lname = DLookup("[LastName]", "Personnel", "[PersonnelID] = " & GovPOCID)
GovPocName = GovPoc_Lname & ", " & GovPoc_Fname
Me.Customer = GovPocName

TaskLeadID = DLookup("[PrimeTaskLead]", "qry_Current_POC", [WBS] = [WBS])
TaskLead_Fname = DLookup("[FirstName]", "Personnel", "[PersonnelID] = " & TaskLeadID)
TaskLead_Lname = DLookup("[LastName]", "Personnel", "[PersonnelID] = " & TaskLeadID)
TaskLeadName = TaskLead_Lname & ", " & TaskLead_Fname
Me.TaskLead = TaskLeadName

Exit Sub
Error_Handler:
If Err.Number = 3075 Then
Exit Sub
Else
MsgBox Err & ": " & Err.Description
End If
End Sub


Example data: (Could not use actual data because it is internal proprietary) Source data is pulled form External tables....linked. One called WBS and one called Project.
Example Project number being selected is: 123456789123456789123 Want it to query and pull the ContractNumber to fill a field in a report. Should pull: XXXXXXX-XXX-XX-X/X-1
However, there are other Project numbers same as above up to 11th digit (for example: 123456789129999999999, 123456789129999999998, 123456789129999999997, 123456789129999999996 all linked to Contract number
XXXXXXX-XXX-XX-X/X-5 Instead of pulling the contract number ending in x-1 it continuously pulls the x-5

I hope that is enough information. THANK YOU for all the help. This is not the only situation with a contract number being linked to multiple projects. All others query correctly. Just this one group.

[GinaWhipp]

Hmm, next time let's use Code tags makes it easier to read. That said, all I needed was the SQL of the query causing the problem and from what I can see I have queries with no SQL and no indication of which one is causing the problem.

[blewis81]

I'll be honest, I don't know what you mean by "SQL"...I thought that was the code. I also don't know what "code tags" are. I apologize.

[GinaWhipp]

That's okay I can help with that...

The query that you mentioned in the first post, open it in Design View and then to the upper left select SQL and what is in that window copy and past here. For Code tags, after you copy\paste the SQL highlight it and select the button that has the hash mark on it, the one that looks like #.

[blewis81]

This inherited database has multiple queries saved with it....however, I am posting the query that the Report I want is based on (and where I find the error). Not 100% confident it is the correct one needed.

SELECT Procurement.*, Procurement_Items.*, WBS.WBS_Description, Project.PWS_Paragraph, Project.ContractNumber, WBS.CostCenter, Project.ProjectManager, Project.ContractSpecialist
FROM (Project INNER JOIN (Procurement INNER JOIN WBS ON Procurement.WBS = WBS.WBS) ON Project.ProjectNumber = WBS.Project) INNER JOIN Procurement_Items ON Procurement.Procurement_ID = Procurement_Items.Procurement_ID
WHERE (((Procurement.Procurement_ID)=[forms]![frm_Procurement]![Procurement_ID]))
ORDER BY Procurement_Items.Item_ID;

[GinaWhipp]

Hmm, not the query so it is...

StrProjectNo = Left([WBS], 10)

However, this is not a tweak as you found out because you will get an error. You are going to need to have someone work on the actual file. So unless you can upload here (which you probably can't do) so it can be modified. I don't see another way you not being familiar with coding.

[rpeare]

I've read your posts several times and I still don't have a firm grasp of what the problem is

It seems in your first post that your project number may be 10 OR 11 characters long
After those characters your contract number appears.

if that's the case how are you determining the length of the project number? is it simply if the total length of the string is 20 it's 10 characters, if the total length of the string is 21 then it's 11 characters?

The error you're encountering in your first string (INVALID NULL) screams either the entire string is null, or it's not as long as you think it is.
For instance, if the field has a project number but no contracts associated with it (yet?) the field would only be 10 or 11 characters long so operations on something after the 12th place would be invalid
If it is possible for a project to have no assigned number (yet? like a pending status where a project number hasn't been assigned) then that'd definitely cause a null error.

if either one of these cases can exist then you'll probably need to check the length of the string with your operation to make sure you handle situations where the string may not be what you think it is.

i.e. ProjNum: iif(len([STRINGFIELD]) = 0, null, iif(len([STRINGFIELD]) <=11, [STRINGFIELD], iif(len([STRINGFIELD]) = 20, left([STRINGFIELD],10), iif([Len([STRINGFIELD]) = 21, left([STRINGFIELD], 11), null))))

you can do a similar formula to pull out the contract number

if you can correctly parse the project number and contract number that should then you can work on how to pull any other information you want.