1) Check out BETWEEN in help (passe Mike (passe Izy))
2) You have enclosed your text with single quotes. You need to use hashes (or pound signs if you are from the US) i.e. # for dates.
3) Are you working in MM/DD/YYYY format?
debug.print "SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" & " and ([eff date]>= " & dtDate1 & " and [eff date]<=" & dtDate2 & ")"
Check that the output looks as you would expect. Run the output (copy/paste) as standalone SQL to check it's giving you the desired results. If it still gives 0 records then I think you have no records meeting the criteria.
Howey's idea is always a good one though remember to insert the #s!
VBA is a bit US centric. When you concatenate dates into a string it changes them to MM/DD/YYYY where ever possible (e.g. 31/01/2005 remains as is since it cannot be a MM/DD/YYYY date but 04/01/2005 is changed to 01/04/2005) irrespective of your local date\ time settings.
Personally I would do something like:
Dim dtDate1 As String
Dim dtDate2 As String
dtDate1 = Trim(Me.txtClosing1)
dtDate2 = Trim(Me.txtClosing2)
IF Not ISDATE(dtDate1) OR Not ISDATE(dtDate2) THEN
'Not two dates. Give some message
Set rs1 = dbs.OpenRecordset("SELECT * FROM PRP160 WHERE trim([transport co])='" & Trim(Me.cmbTransporter) & "'" _
& " and [eff date] BETWEEN #" & dtDate1 & "# and #" & dtDate2 & "#")
how about "SELECT * FROM table where dateExample BETWEEN DateValue('" & startDate & "') and DateValue('" & endDate & "')"
DateValue('dateString') will make a date string of "05/05/2006") cast into a number of 3#### (sorry, i cant remember the number of this date example, to make the date a number so you can use between command.