Solved Help with handling of Null

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.
 
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.
 
Last edited:
@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?

Code: 
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
 
June7 said:
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.
Click to expand...
@June7, thank you. Do you mean like this:

Code: 
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    7.2 KB · Views: 126
allen675 said:
@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?

Code: 
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
Click to expand...
Hi @allen675. Pardon me for jumping in. You can certainly modify the code to do the same thing with the new table structure, but it's also possible to leave it as-is and simply use a query as your data source, instead of a table.

For example, assuming you created your child table structure as follows:
1659976997565.png


You can see that client #1 had three calls and client #2 had two. If we sort that table in a query, it becomes clearer:
1659977056287.png


Now, to identify the call number for each call per client, we simply modify the query to include that information (based on the sort order):
1659977117567.png


As you can see, we can now identify the call number for each call per client. Once we have that, we can then use a Crosstab query to resemble your old table structure. For example, like this:
1659977192209.png


You can then use the Crosstab query in your code, instead of the original table.

Hope that helps...
 
@theDBguy I'm hearing you and thanks for jumping in with something constructive! So I now have a child Table called 'ContactAttemptT' with customerID, ContactID, ContactDate, ContactType.

Just off subject slightly because I want to keep my form for inputting new leads and amending existing with the same layout. This layout ALREADY doesnt allow you to enter further call attempts until the subsequent has been undertaken. I am trying to keep things as straight forward as I can for the end user. So my question is on the form I have a 'Today' button that appears next to each Call attempt filed, how can I KEEP this button and still populate the new child field? I need this question answered before we go any further because if not the normalised change that everyone seems to be pushing me down opens up a load more work and headaches I don't want! Would a line of code like this do it, please don't laugh:

Code: 
Private Sub Command40_Click()
Me.Phone_Call__1.Value = Date
strSQL = "INSERT INTO ContactAttemptT ([ContactDate], [ContactType]) VALUES (" & [Me.Phone_Call_#1] & "," & "Call"")"
CurrentDb.Execute strSQL, dbFailOnErro
End Sub
 
@theDBguy I'm hearing you and thanks for jumping in with something constructive!
Click to expand...
I guess you didn't see that I recommended a crosstab query back in post #3:( It is the best way to normalize your data but see it in the unnormalized format you are used to.
 
Pat Hartman said:
You can still normalize your schema since that is always right. You can then use a crosstab query to flatten it so you get the three + calls in a single row. The crosstab query wizard only allows you to pick three fields. You can fix this later by adding additional columns or joining to another table in the query.
Click to expand...
Honestly Pat you might as well be talking a foreign language, my Access DB knowledge is very very basic. @theDBguy is very useful and provides a good explanation but still I am a bit stuck!
 
We're 30 posts in now. I do not understand why you still have a problem. Didn't you ever try the Nz(somefield, "") solution? I'm sure it's been mentioned more than once.
 
Pat Hartman said:
We're 30 posts in now. I do not understand why you still have a problem. Didn't you ever try the Nz(somefield, "") solution? I'm sure it's been mentioned more than once.
Click to expand...
We did, evidenced by post #24 but again stuck with syntax error
 
Use apostrophes in place of the quote marks when embedding in VBA SQL statement. Sorry, I should have clarified that before.

Nz([Phone_Call_#1], '')
 
Thanks @June7 done that like so:

Code: 
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

Getting the following error(please see attached) 'Item not found in collection'
 

Attachments

  • Capture.PNG
    Capture.PNG
    5 KB · Views: 114
  • Capture1.PNG
    Capture1.PNG
    22.1 KB · Views: 118
That error is telling you that [Call_#1] isnt in the RecordSet. Check the query that it is derived from.
 
If I return the code to how it was prior to amending i.e.
Code: 
, [Phone_Call_#1],
then a value is returned so it must be in the recordset and therefore the code that needs tweeking?

Put it back to this
Code: 
, Nz([Phone_Call_#1], ''),
then get the error posted in #33
 
Are you using the CT query the DBG is referring to in post #26?
 
Nevermind, I see the SQL statement. You are using aliases, Call 1 AS C1, Call 2 AS C2...

The fields in your recordset are now called C1,C2 and C3.
 
So should I now do this?

Code: 
.HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST!C1)
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST!C2)
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST!C3)
 
You must log in or register to reply here.

Similar threads

A
Pulling table data into Outllook template
Replies
15
Views
602
allen675
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom