Try placing a debug.print strSQL right before you open the query. Copy and paste your string into the SQL view of a query and see if you get results there. Often times, I find that if I am not getting expected results seeing the query in the query grid makes it clear as to why.
Dawn Crosier "Education Lasts a Lifetime" This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn as well. -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Gary E. Daniels Sent: Wednesday, September 06, 2006 7:58 PM To: [email protected] Subject: [ms_access] Need VBA Code for date range Hi. Rather new here. Below is an Access 97 VBA subroutine. I am having a problem with the lines - "AND SaleComps.[rptdate]>=" & Me.bminrptdate.Value & " " & _ "AND SaleComps.[rptdate]<=" & Me.bmaxrptdate.Value & " " & _. This is where a beginning date (bminrptdate) and an ending date (bmaxrptdate) are entered on a form. The format is mm/dd/yyyy and the fields are Short Date fields. All the other parameters in the subroutine work and I've isolated the problem to the date lines. When the command button is run, no records are returned. Thanks in advance. Gary ============================================================================ ================== Private Sub cbozip_Click() Dim db As Database Dim qdf As QueryDef Dim strSQL As String Set db = CurrentDb Set qdf = db.QueryDefs("results") strSQL = "SELECT SaleComps.* " & _ "FROM SaleComps " & _ "WHERE SaleComps.Zip='" & Me.Zip.Value & "' " & _ "AND SaleComps.[Tunits]>=" & Me.bminunits.Value & " " & _ "AND SaleComps.[Tunits]<=" & Me.bmaxunits.Value & " " & _ "AND SaleComps.[YrBlt]>=" & Me.bminblt.Value & " " & _ "AND SaleComps.[YrBlt]<=" & Me.bmaxblt.Value & " " & _ "AND SaleComps.[rptdate]>=" & Me.bminrptdate.Value & " " & _ "AND SaleComps.[rptdate]<=" & Me.bmaxrptdate.Value & " " & _ "ORDER BY SaleComps.file" qdf.SQL = strSQL DoCmd.OpenQuery "results" DoCmd.OpenForm "Master Form" DoCmd.Close acForm, Me.Name Set qdf = Nothing Set db = Nothing End Sub Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/ms_access/join (Yahoo! ID required) <*> To change settings via email: mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
