Solved Help with handling of Null

[allen675]

Hello,

So far with the very kind help of people of this forum website, I have pieced together the below code and it is working

I am new to VBA and although dabbled in the past never got this involved.

Essentially the code below takes information for a specific lead/customer from three separate tables. Of the three, two pull lead details and notes and transfers this information to the body of an Outlook template stored under the current project folder. The third pulls the file names stored in ContactProofT table for the same lead and attaches the proofs to the same email. This folder is also under the current project.

So the next step I would like to look at is the handling of null values for details, notes and attachments. For example if the end user speaks to the lead on the second call attempt and identifies that the client does not wish to be helped then this lead will never get to the third call attempt and so therefore the value of this field would be null. The same for the attachments, there wont always be proofs of contact to attach to an email.

Could all you forum'ers, that know what you are doing, unlike me, have a look at the code and see how we/I can accomplish this please?

Many thanks in advance

Private Sub ClientStatus_Change()
Dim sStatus As String
sStatus = Me!ClientStatus & ""
If sStatus <> "NPW - No Contact" And sStatus <> "NPW - Gone Elsewhere" And sStatus <> "NPW - Unable to Place" Then
    Exit Sub
End If
If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbNo Then
    Exit Sub
End If
    
Me.Refresh

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String
Dim clientRST As Variant
Dim salesRST As Variant
Dim strTable As String
Dim i As Variant
Dim strPaths() As String


strSQL = "SELECT [CustomerID], [Broker], [Lead_Date], [Client_FN], [Client_SN], [Email_Address], [Mobile_No], [Email_Sent], [SMS/WhatsApp_Sent], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3] FROM Client" _
            & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")
    
    strSQL = "SELECT NoteDate, Note" _
    & " FROM NoteHistory" _
    & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)
    
    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 0 To salesRST.Fields.Count - 1
        strTable = strTable & "<td>" & "</td>"
    Next i
    strTable = strTable & "</th>"
    
    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 1).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close
    
    strSQL = "SELECT [FileName] FROM ContactProofT" _
    & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
    strPaths = Split(SimpleCSV(strSQL), ",")
    
    
    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"
        Dim x As Long
        For x = 0 To UBound(strPaths)
            .Attachments.Add CurrentProject.Path & "\ContactProofs\" & strPaths(x)
        Next
        
            
        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%date%", clientRST![Lead_Date])
        .HTMLBody = Replace(.HTMLBody, "%first%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%surname%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%mobile%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%email%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%unsuccessful%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%message%", clientRST![SMS/WhatsApp_Sent])
        .HTMLBody = Replace(.HTMLBody, "%Broker%", clientRST![Broker])
        
        
        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)
        
        .Display
    
    End With
    
    
    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop
clientRST.Close
Set clientRST = Nothing
DoCmd.Close acForm, "CopyExistingLeadF"

End Sub

 

[The_Doc_Man]

The way you described this makes me pause. You said:

For example if the end user speaks to the lead on the second call attempt and identifies that the client does not wish to be helped then this lead will never get to the third call attempt and so therefore the value of this field would be null.

In a properly normalized database, there IS no third call attempt slot until someone actually MAKES a third attempt. If you have a fixed number of call slots, your DB is not normalized with respect to call slots. You should have a child table of call slots that has EXACTLY as many records as calls that were made. Your stats would be based on the child record with the earliest and the latest calls and a count of all calls. If your client calls 50 times, you 50 records. If your client never calls, you have no records.

Your more immediate problem would be that if your client declines further assistance, you would have to post a code that says "Declines assistance" (using whatever term you normally would use). After that (which SHOULD close the case), trigger a query or other action to load up a code for the extra slots AND load the time of the closure call. OR you can use the NZ function to test for nulls and let NZ load up some useful, meaningful, unequivocal value that applies in such cases.

 

[allen675]

Hi

Everything that you have said makes absolute sense and I do have child tables within my DB for example with notes, however, our sales process is for three calls max, one email and one message/WhatsApp and that's how I've built the lead input form & table.

If value is null for placeholders then I was thinking sometime along the lines of is null then value " " because it doesn't matter if the placeholders are blank

 

[GPGeorge]

Hi

Everything that you have said makes absolute sense and I do have child tables within my DB for example with notes, however, our sales process is for three calls max, one email and one message/WhatsApp and that's how I've built the lead input form & table.

If value is null for placeholders then I was thinking sometime along the lines of is null then value " " because it doesn't matter if the placeholders are blank

Your sales process is how you do things today. There's no guarantee that process will remain the same forever. Don't get locked into a design based on assumptions that can, and usually do, change over time.

Please take the advice regarding inappropriate table design seriously.

Not only that, the only reason we're discussing the problem of Null placeholders at all is that the table design CURRENTLY isn't even appropriate. It forces you to deal with something (the missing third call) that wouldn't come up in a properly normalized table design. Already, you're incurring extra work unnecessarily.

 

[June7]

If you want to substitute Null with empty string, try Nz(fieldname, "") or fieldname & ""

I don't disagree with the advice for normalization but db design also involves a balancing act between normalization and ease of data entry/output.
"Normalize until it hurts, denormalize until it works."

 

[allen675]

@June7 thank you for your input and defence having said that what the other commenters don't know yet, as I have not explained this, is that elsewhere in the DB this information populates a spreadsheet with defined call attempt cells which then gets fired off to the directors. I am sure there is some way of writing code to manipulate and format a spreadsheet but I am doing my best to shoehorn this database into a format they want and already use and following a sales process which hasn't changed in 20+ years(but never say never, I suppose it could). I don't disagree if I had my way I would have separate child table for the call attempts as I do with Notes & the file attachments which of course allow for unlimited records and avoid trying to work around this problem.

Not being an experienced DB designer or coder, would you be so kind to show me an example of the above ideas you have provided in the code that I have provided please?

 

[allen675]

@Pat Hartman you see the things is I have an advantage over you, I actually work for the organisation I am putting this together for and our sales process is three calls so its not about belief, its a fact! Anyhow this isn't about how the business is run its about working with what I have been given and finding a solution.

I'm not planning on gaining any further experience but should I decide to I will ask someone else ;-)

Thanks for your input, it was very useful!

 

[allen675]

Well that's why I'm here Pat for the code, not business advice. Should things change and I decide to leave the business well then that's not my problem is it! Sorry but my crystal ball fell out of the car this morning while I was crossing the Brooklyn Bridge and broke so I cant predict the future anymore and I suspect neither can you.

 

[MarkK]

 

[allen675]

Feel free to ignore me in the future if that makes you feel good

I think you'll find Pat, that I have agreed with you so not ignoring you. Just looking for a solution for what I have rather than re-inventing the wheel.

 

[allen675]

I'm self employed Pat so wouldn't make any difference and anyhow if they saw it probably wouldn't bother them anyway.

Good comparison aircraft pilot and db designer I can see the similarities

Just because someone doesn't want to do it a certain way shouldn't open them up to abuse, which is exactly what has happened here.

I've said I agree with you but that's not what is wanted and asked for your help to resolve what I have.

What exactly do you want from me Pat? Do you want me to say okay I'll get the directors to change the sales process to fit the db or do you want me to put something into a db that we don't need or want because it might upset those that I've asked for help?

 

[GPGeorge]

I'm self employed Pat so wouldn't make any difference and anyhow if they saw it probably wouldn't bother them anyway.

Good comparison aircraft pilot and db designer I can see the similarities

Just because someone doesn't want to do it a certain way shouldn't open them up to abuse, which is exactly what has happened here.

I've said I agree with you but that's not what is wanted and asked for your help to resolve what I have.

What exactly do you want from me Pat? Do you want me to say okay I'll get the directors to change the sales process to fit the db or do you want me to put something into a db that we don't need or want because it might upset those that I've asked for help?

Sorry you feel it is abuse. It can feel that way, I'm sure, when someone tells you that you've invested a huge amount of time in a less than optimal design. You have done that, though, and telling you it's a less than optimal design is not intended to be hurtful; it's intended to help you correct the problem.
You might get a kick out of this blog post, or it might just tick you off. Pay particular attention to the third point about "Practically Perfect".

 

[allen675]

@GPGeorge well worded and a much better approach

I am upset with the approach not by the fact that I've been told its not a perfect design after all on several occasions I have admitted I agree. I have what I have and can only work with what I am given. I am not going to do something in a way that is not asked. That may be something a professional DB designer/company would do and highlight the design pitfalls after all that's what they are paid to do.

There is no need for pointless links to fruitless information about bridges, from someone who thinks their wit is clever, and certainly no need for pictures suggesting that I am telling someone how to do their job. Its childish, unnecessary and quit frankly rude.

Its a three call process always has been and for the foreseeable future will be, I CANT CHANGE THAT. The DB elsewhere populates THEIR spreadsheet which has three cells, Call 1, Call 2 and Call 3 and will happily do so even with null values. I now would like help, not criticism, to put in place to deal with the null values in the code provided in the initial post, can this be done yes or no. If it can then great please show me, if not then I will not proceed any further and tell them I cant help.

 

[GPGeorge]

@GPGeorge well worded and a much better approach

I am upset with the approach not by the fact that I've been told its not a perfect design after all on several occasions I have admitted I agree. I have what I have and can only work with what I am given. I am not going to do something in a way that is not asked. That may be something a professional DB designer/company would do and highlight the design pitfalls after all that's what they are paid to do.

There is no need for pointless links to fruitless information about bridges, from someone who thinks their wit is clever, and certainly no need for pictures suggesting that I am telling someone how to do their job. Its childish, unnecessary and quit frankly rude.

Its a three call process always has been and for the foreseeable future will be, I CANT CHANGE THAT. The DB elsewhere populates THEIR spreadsheet which has three cells, Call 1, Call 2 and Call 3 and will happily do so even with null values. I now would like help, not criticism, to put in place to deal with the null values in the code provided in the initial post, can this be done yes or no. If it can then great please show me, if not then I will not proceed any further and tell them I cant help.

We're getting closer to the point. The PROCESS is three calls. True. That doesn't mean you design the table exactly the way you design a spreadsheet. And that's where the problem comes in. Three fields in an Access relational database table labelled Call1, Call2, Call3 is the problem. That's a spreadsheet design. That is not a relational database design. The fact that there are three calls is not the key to getting it right. The fact is that Access--and all other relational databases--work best when you create a table like this:

tblContact
=======
PrimaryKey
ProspectID -- Foreign key to the Prospect table
ContactDate
ContactType -- one of three currently

Each time you make a contact, you add a row with the date and type (call, email or DM). The table can have from 0 to 6 rows for each Prospect. You do not have to enter a record until you actually make that contact. You can add a constraint to limit calls per Prospect to no more than three and emails to no more than two, or whatever the rule is.

Unfortunately, the current method (modelled after spreadsheets) has a design that FORCES you to account for all five six contacts, one way or the other. Either you enter something or there is a null in an existing field in that Prospect record. And that, in turn, adds the complication of having to check for those nulls.

tblProspect
========
ProspectID
Call1
Call2
Call3
Email1
Email2
DM

The reason the problem here exists has nothing directly to do with the business process. It has to do with the table design. Related but different things. You don't have to change the business model to support the table, no. You should change the table design to more closely align with the relational database model, as opposed to the spreadsheet model.

You don't have to do it. More than once over the years I've seen cases where there is so much data invested in a less-than-appropriate table design that a full re-factoring is more work than simply putting a little band-aid on it.

The beauty of the relational database design is that no extra coding has to happen to account for tasks that are allocated, but not completed. All you have to do is add the task when it occurs, Done.

Again, no one intentionally tried to abuse anyone. It's a matter of combined multiple decades of experience here. We've seen this exact problem dozens of times. We probably aren't as patient as we should be when we see it yet again. That's not your fault.

 

[allen675]

@GPGeorge Thank you all makes sense. I will have a re look at the code I have elsewhere i.e. the code that populates the spreadsheet and see if this can be changed to work with a new child table. If it involves more work than I was expecting then I will just scrap the whole thing.

Can't understand why no one will help me with the code I have really can't understand that.

 

[GPGeorge]

@GPGeorge

Spoiler: etc

Thank you all makes sense. I will have a re look at the code I have elsewhere i.e. the code that populates the spreadsheet and see if this can be changed to work with a new child table. If it involves more work than I was expecting then I will just scrap the whole thing.

Can't understand why no one will help me with the code I have really can't understand that.

In that blog post I linked, one of the points I raised is exactly this one.

 

[The_Doc_Man]

Did you see the second paragraph of my post, #2 of this thread? It discusses a way to NOT change your table structure, but it does require a small bit of coding adjustment for the case of closure after the #2 call.

 

[June7]

The query sql could handle null to replace with empty string or some text:

Nz([Phone_Call_#1], "") AS C1, Nz([Phone_Call_#2], "") AS C2, Nz([Phone_Call_#3], "") AS C3 FROM Client

Not entirely clear to me where your code is having issues with Null.

Keep in mind for future development: strongly advise not to use punctuation/special characters (underscore is only exception) in naming convention.

 

[allen675]

@GPGeorge apologies for my silence have been working quite a bit!

So lets assume I have 'Normalized' call attempts and now have a child table called 'CallAttemptsT' and being mindful I'm having to shoehorn this data into an Excel spreadsheet(THIS CANNOT BE CHANGED) which still has the three separate cells call 1, call 2 & call 3. I assume I now need to change the query that populates said spreadsheet. This query is looking at data in the 'Client' table but also 'NoteHistory' child table(using @theDBguy SimpleCSV code) to collate notes. How do I make that work with the code below which is populating the spreadsheet? Assuming I fix this then the initial request and therefore code will be fixed as we then wont have null values?

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
Dim FilePath As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim FileName As String

blnEXCEL = False

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = False

' Set up file path to CurrentProject
FilePath = Application.CurrentProject.Path

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open(FilePath & "\Tracker")

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("Tracker")

' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A3") ' this is the first cell into which data go

Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("EmailQ", dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then

      rst.MoveFirst

      If blnHeaderRow = True Then
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
      End If

      ' write data to worksheet
      Do While rst.EOF = False
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
            Next lngColumn
            rst.MoveNext
            Set xlc = xlc.Offset(1, 0)
      Loop

End If

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True   ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

 

[allen675]

The query sql could handle null to replace with empty string or some text:

Nz([Phone_Call_#1], "") AS C1, Nz([Phone_Call_#2], "") AS C2, Nz([Phone_Call_#3], "") AS C3 FROM Client

Not entirely clear to me where your code is having issues with Null.

Keep in mind for future development: strongly advise not to use punctuation/special characters (underscore is only exception) in naming convention.

@June7, thank you. Do you mean like this:

strSQL = "SELECT [CustomerID], [Broker], [Lead_Date], [Client_FN], [Client_SN], [Email_Address], [Mobile_No], [Email_Sent], [SMS/WhatsApp_Sent], Nz([Phone_Call_#1], "") AS C1, Nz([Phone_Call_#2], "") AS C2, Nz([Phone_Call_#3], "") AS C3 FROM Client" _
            & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID

As getting Syntax Error as attached