By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Filter SubForm by changing query criteria

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdToday_Click()
  2.  
  3. Dim sSQL As String
  4.  
  5. sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate =  Date()"
  6.  
  7. Forms!ViewCases![ViewCasesSubform].Form.RecordSource = sSQL
  8.  
  9. End Sub
Dec 12 '07 #1
Share this Question
Share on Google+
9 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdToday_Click()
  2.  
  3. Dim sSQL As String
  4.  
  5. sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate =  Date()"
  6.  
  7. Forms!ViewCases![ViewCasesSubform].Form.RecordSource = sSQL
  8.  
  9. End Sub

Date() is an embedded function, that needs to be exposed as a parameter within the SQL string. Try the syntax this way.
Expand|Select|Wrap|Line Numbers
  1. sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate = " &  Date()
Dec 12 '07 #2

FishVal
Expert 2.5K+
P: 2,653
Are you sure the query returns any records?
Did you run it in query builder?
Dec 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,271
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 (#).).
Expand|Select|Wrap|Line Numbers
  1. sSQL = "SELECT * " & _
  2.        "FROM QueryCases " & _
  3.        "WHERE [StartDate] = " & Format(Date(), '\#m/d/yyyy\#')
Dec 12 '07 #4

NeoPa
Expert Mod 15k+
P: 31,271
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.
Dec 12 '07 #5

P: 52
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
Dec 12 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
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.
Dec 13 '07 #7

NeoPa
Expert Mod 15k+
P: 31,271
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.
Dec 13 '07 #8

P: 52
Thanks guys i got it - here is the code:
Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2.     Dim viewToday
  3.     viewToday = Format(Now(), "mm/dd/yyyy")
  4.  
  5.     sSQL = "SELECT * FROM QueryCases WHERE QueryCases.StartDate = #" & viewToday & "#;"
  6.     Me![ViewCasesSubform].Form.RecordSource = sSQL
Dec 13 '07 #9

NeoPa
Expert Mod 15k+
P: 31,271
You're welcome :)
And thanks for letting us know - you saved me a job later ;)
Dec 13 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.