You may need to use a "directional" join in your query.  Open one of your
current queries in design mode.  Highlight the "join" line, then right-click
it to get at its properties.  You will want to select the option that
returns ALL of one of the tables (probably your Customer) and ANY of the
other.  This gives you all customers whether or not there are matching
records in the other table.

You did the correct thing in entering the code in the SQL window.

I did have an error in the SQL (at least one).  I referred to RenewalDate in the
Where clause when I should have referred to Renewal.  

Note that you might want to add other fields in the select clause - such as
P.Renewal since if the RenewalDate is null this will display 1/1/1900 and give
you a clue that your data is bad for the relevant record.

If this still requests data be entered, then there are probably other
typing/syntax errors.  IF so, please post what the query asks for.  Also,
"..doesn't seem to work" is not very descriptive.  
Does that mean
--it errors out (what error),
--returns no records,
--returns the wrong records,
--returns some of the correct records
--???

Try this revised query

SELECT tblCustomer.*, P.Renewal, V.VisitDate
FROM (tblCustomer INNER JOIN
  (SELECT C.CustomerID
     , Nz(Max(RenewalDate),#1/1/1900#) as Renewal
  FROM tblCustomer as C Left JOIN tblPayment
  ON C.CustomerId = tblPayment.CustomerID
  GROUP BY C.CustomerID) as P
     ON tblCustomer.CustomerID = P.CustomerID)
INNER JOIN
  (SELECT CustomerID
     , Max([Date]) as VisitDate
  FROM tblVisit
  GROUP BY CustomerID) as V
     ON tblCustomer.CustomerID = V.CustomerID
WHERE P.Renewal < V.VisitDate