Filter SubForm by changing query criteria

[darnnnel]

Hello everyone. i have a fom and subform (they are not linked).The subform has a date field named StartDate. i have a button on the form that when clicked will change the recordset of the subform to show StartDate of today. When i hit the command button, the subform becomes blank.Its driving me crazy.Here is my code:

Private Sub cmdToday_Click()
 
Dim sSQL As String
 
sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate =  Date()"
 
Forms!ViewCases![ViewCasesSubform].Form.RecordSource = sSQL
 
End Sub

[puppydogbuddy]

Hello everyone. i have a fom and subform (they are not linked).The subform has a date field named StartDate. i have a button on the form that when clicked will change the recordset of the subform to show StartDate of today. When i hit the command button, the subform becomes blank.Its driving me crazy.Here is my code:

Private Sub cmdToday_Click()
 
Dim sSQL As String
 
sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate =  Date()"
 
Forms!ViewCases![ViewCasesSubform].Form.RecordSource = sSQL
 
End Sub

Date() is an embedded function, that needs to be exposed as a parameter within the SQL string. Try the syntax this way.

sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate = " &  Date()

[FishVal]

Are you sure the query returns any records?
Did you run it in query builder?

[NeoPa]

As pDog says, you need the RESULTS of the Date() function added to your SQL string (IE it is for VBA to resolve not for the SQL interpreter). However, as a date literal, it should also be enclosed in '#' characters and formatted in M/D/Y format for SQL (See Literal DateTimes and Their Delimiters (#).).

sSQL = "SELECT * " & _
       "FROM QueryCases " & _
       "WHERE [StartDate] = " & Format(Date(), '\#m/d/yyyy\#')

[NeoPa]

Actually, I just ran some tests and, certainly for me, the SQL interpreter DOES resolve Date() correctly when done as you have done. This means it doesn't need to be treated as a literal (it still can be but shouldn't NEED to be). I can't see what's wrong with your SQL. Is it possible that the reference to the control is wrong (even then, why would it change at all if the reference were wrong)? Anyway, in case it helps, check out Referring to Items on a Sub-Form.

[darnnnel]

Thank you so much for your help. do you think you could look at my file. i couldnt attach it here, it is too large - the link is below. i would really appreciate it.

www.imperialelevatorcorp.com/download/date.zip

[puppydogbuddy]

Thank you so much for your help. do you think you could look at my file. i couldnt attach it here, it is too large - the link is below. i would really appreciate it.

www.imperialelevatorcorp.com/download/date.zip

I downloaded your file, but could not open it because I have Access 2000 and you must have Access 2003. Sorry.

[NeoPa]

If you leave instructions as to where to look inside the database (You haven't referred to any query by name), and the db is 2003 & not 2007, then I'll have a look for you from home (when I get there of course). I'd rather not spend ages just hunting around for the relevant part of the database though.

[darnnnel]

Thanks guys i got it - here is the code:

Dim sSQL As String
    Dim viewToday
    viewToday = Format(Now(), "mm/dd/yyyy")
 
    sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate = #" & viewToday & "#;"
    Me![ViewCasesSubform].Form.RecordSource = sSQL

[NeoPa]

You're welcome :)
And thanks for letting us know - you saved me a job later ;)