show companies with null values

[jamo]

I have created a form, I need this form to enter amounts and date paid after the companies have paid. I have this code that is filtered by company, it shows amount due. I have this code but the form does bot show any data. I need to show data for those companies in arrears.

 Private Sub CboFilter_Change()
'If the combo box is cleared, clear the form filter.
  If Nz(Me.CboFilter.Value) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False

  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.CboFilter.ListIndex <> -1 Then
    Me.Form.Filter = "[Importer Name] = '" & Nz(Me.Amount_Paid.Value) = "" & _
                     Replace(Me.CboFilter.Value, "'", "''") & "'"
    Me.FilterOn = True

  ' If a partial value is typed, filter for a partial company name match.
  Else

    Me.Form.Filter = "[Importer Name] Like '*" & _
                     Replace(Me.CboFilter.Value, "'", "''") & "*'"
    Me.FilterOn = False

  End If

  ' Move the cursor to the end of the combo box.
  Me.CboFilter.SetFocus
  Me.CboFilter.SelStart = Len(Me.CboFilter.Value)

End Sub

[June7]

Why does the filter show criteria of [Importer Name] = [Amount_Paid]?

Want to provide db for analysis? Follow instructions at bottom of my post.

[jamo]

Thanks I have uploaded a sample with the relevant data

[June7]

The form doesn't display existing records because the DataEntry property is set to Yes. This only allows new record entry.

The form RecordSource does not include ImporterId nor ImporterName.

Why use PurchasingExtended in the RecordSource? Just use the Purchasing table.

SELECT Purchasing.*, Importer.ImporterID, Importer.[Importer Name], Payment.[Receipt NO], Payment.[Amount Paid], Payment.[Date Paid] FROM (Purchasing LEFT JOIN Payment ON Purchasing.PurchasingID = Payment.PurchasingID) LEFT JOIN Importer ON Purchasing.ImporterID = Importer.ImporterID;

Better to filter on ImporterID instead of ImporterName.

Advise not to use spaces, special characters, punctuation (underscore is exception) in names, nor reserved words as names.

[jamo]

thank you going to try

[jamo]

Thanks it works but how can I filter on only those where the receipt numbers are blank, I need those to be filled when the payments are made?

[June7]

Use WHERE clause:

WHERE [Receipt NO] Is Null;

[jamo]

I am reading the formula but I am do not know where to include the where clause

[June7]

Query in Design view.

On the Criteria row under the [Receipt No] field: Is Null

[jamo]

sorry for the slow response has some computer issue. I tried doing this but it does not work the query is coming up blank.

[June7]

It works for me:

SELECT Purchasing.*, Importer.ImporterID, Importer.[Importer Name], Payment.[Receipt NO], Payment.[Amount Paid], Payment.[Date Paid]
FROM (Purchasing LEFT JOIN Payment ON Purchasing.PurchasingID = Payment.PurchasingID) LEFT JOIN Importer ON Purchasing.ImporterID = Importer.ImporterID
WHERE (((Payment.[Receipt NO]) Is Null));

Did you change the DataEntry property? See post #4.

[jamo]

Thanks this works great. thanks