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

IS NUll Date Field

100+
P: 147
How do I format the following to work properly?

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnFilterOutstanding_Click()
  3.  
  4. Dim stFilter As String
  5.  
  6.     If "#Tables![tblMilitaryLeave]![DatePaid] ISNull#" Then
  7.     stFilter = "Tables[tblMilitaryLeave]![DatePaid]"
  8.     End If
  9.  
  10.     'Set the Filter property
  11.     Reports!rptMilitaryLeave.Filter = stFilter
  12.     Reports!rptMilitaryLeave.FilterOn = True
  13.  
  14. End Sub
  15.  
  16.  
Thanks
Dan
Feb 9 '09 #1
Share this Question
Share on Google+
11 Replies


beacon
100+
P: 579
I think you need to write it like the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnFilterOutstanding_Click()
  2.  
  3. Dim stFilter As String
  4.  
  5. If IsNull(Tables![tblMilitaryLeave]![DatePaid]) Then
  6. stFilter = "Tables[tblMilitaryLeave]![DatePaid]"
  7. End If
  8.  
  9. 'Set the Filter property
  10. Reports!rptMilitaryLeave.Filter = stFilter
  11. Reports!rptMilitaryLeave.FilterOn = True
  12.  
  13. End Sub
  14.  
I haven't tested this out...that jumped out at me as a possibility though.

* When you are trying to indicate a code block, put brackets [ ] around the word code and end it with brackets around /code.
Feb 9 '09 #2

Expert 100+
P: 1,287
I think you want:
stFilter = "DatePaid IS NULL"
Feb 9 '09 #3

100+
P: 147
No niether of the above is correct, my problem is that it is a date field and the Is Null does not seem to work with it as formated. Is there a way to write it so that it works with a date field?
Feb 9 '09 #4

Expert 100+
P: 1,287
If "#Tables![tblMilitaryLeave]![DatePaid] ISNull#"

This doesn't make any sense as you are referring to an entire column of records. What are you trying to compare?
Feb 9 '09 #5

100+
P: 147
I have a form that adds filters to an open report. I want this one to show only records that have no value in the DatePaid field

and I tried "#Tables![tblMilitaryLeave]![DatePaid] ISNull#" and I got a data type mismatch error.
Feb 9 '09 #6

Expert 100+
P: 1,287
So you don't need the If condition at all. Just set the filter as in post #3 and apply it.
Feb 9 '09 #7

100+
P: 147
Thankyou That was it!!! ifeel dumb but I learned something, thanks again!
Feb 9 '09 #8

NeoPa
Expert Mod 15k+
P: 31,494
In case it helps Dan :
Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).
Feb 16 '09 #9

100+
P: 147
I don't understand what you mean here could you give me an example of my mistake?
Feb 18 '09 #10

NeoPa
Expert Mod 15k+
P: 31,494
I'm not sure Dan. The original was changed by Stewart days ago now, & I haven't kept the email notification. If memory serves correctly though, you were using <> instead of []. EG :
<code>
Your code here
</code>
As it might be if it were HTML (This uses BBCode by the way).
Feb 18 '09 #11

Expert Mod 2.5K+
P: 2,545
NeoPa's recollection matches mine, although I can't remember if it was angle brackets or none that had been used. You had actually marked the code segment clearly, either as

< CODE > ... < /CODE >

or simply

CODE ... /CODE

but in any event without using the brackets, so the delimiters didn't work.

-Stewart
Feb 18 '09 #12

Post your reply

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