Results 1 to 12 of 12
  1. #1
    mattmanusa2002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4

    Problem: Creating an IIF statement in a BETWEEN statement for dates

    Hi everyone,

    I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.
    So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.
    The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:

    Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])



    I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes. Thank you!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by mattmanusa2002 View Post
    ...When I try to search with a start date and end date I am have issues....
    Nothing is jumping out at me saying this will break. What, exactly, is not working/happening?

  3. #3
    mattmanusa2002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4
    If I leave all fields blank and hit run query, it gives me a blank screen. If I completely take out the class dates criteria altogether and run query, I get all of the students in my database. Which is what I want. I want it to do the same thing with the IIF and BETWEEN statements.

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try (no guarantee)

    me.Filter="Between #" & Format(nz([Forms]![Search Form]![Start Date],"01/01/2010"),"mm/dd/yyyy") & "# And #" & Format(nz([Forms]![Search Form]![End Date],Date()),"mm/dd/yyyy") & "#"

  5. #5
    mattmanusa2002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4
    It's asking for a me.Filter parameter. What would normally go here or what do I link this to?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Yup, it is the Null thing. Date Delimiters (#) may or may not be needed. You are getting a blank screen because you are testing for Empty String and the only way to get that in newer versions of Access is to type "" into a field. Use Ajax's suggestion and test for Null.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by mattmanusa2002 View Post
    It's asking for a me.Filter parameter.
    Sounds like you are creating a query. Filter is a property of a Form Object so don't use. Do, try and incorporate the NZ function within your SQL and IIf

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Untested but I might approach it like this, instead of the Nz function. I started to think about it and It seemed difficult to incorporate Nz with the IIf.

    Between IIf([Forms]![Search Form]![Start Date]<>"",[Forms]![Search Form]![Start Date],1/1/10) And IIf([Forms]![Search Form]![End Date]<>"",[Forms]![Search Form]![End Date], Date + 5)

    You might be able to get away with >"" and adding +5 to Date may be an issue, dunno

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It's asking for a me.Filter parameter. What would normally go here or what do I link this to?
    I thought your were filtering a form, if you are putting into a criteria of a query then try

    SELECT *
    FROM myTable
    WHERE myDate Between "#" & Format(Nz([Forms]![Search Form]![Start Date],"01/01/2010"),"mm/dd/yyyy") & "#" And "#" & Format(Nz([Forms]![Search Form]![End Date],Date()),"mm/dd/yyyy") & "#"

  10. #10
    mattmanusa2002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4
    I am using a form to filter, but I am using it as a search criteria for a query. Example: In the Search Form, when I enter the start date and end date and press the "run query" button (I created this button), it opens up the query with all the dates between the start date and end date. My original code works as long as I put the dates in. If I leave them out is where the problem is. It just gives me nothing. I want it to work like there was no criteria to begin with. This would them show all the course from the beginning to now.

    I have tried using all of your guys' codes and they have not worked yet. I appreciate your help.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Post your SQL here. Are you including it in VBA, as a string? If so, there may be little differences in the syntax. SQL that you use in a query object is not always exactly the same as SQL in a VBA string.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am confused.....

    You have a form with two unbound controls "StartDate" (shouldn't use spaces) and "EndDate".
    You have a button that then opens a query????? Why isn't the query the record source for the form?


    I would do this:

    The form record source should be the query. Add the fields you want to view in the form detail section.
    The button and two unbound controls "StartDate" and "EndDate" should be in the form header.

    If the button name is "cmdSetFilter", the code behind the button should be something like:
    Code:
    Private Sub cmdSetFilter_Click()
        Me.Filter="[ClassDate] Between #" & Format(nz([Forms]![Search Form]![Start Date],"01/01/2010"),"mm/dd/yyyy") & "# And #" & Format(nz([Forms]![Search Form]![EndDate],Date()),"mm/dd/yyyy") & "#"
        Me.FilterOn
    End Sub
    And a 2nd button (cmdClearFilter) to remove the filter:
    Code:
    Private Sub cmdClearFilter()
       Me.Filter = ""
       Me.FilterOn = False
    End Sub
    -------------------------------

    I build my filter "on-the-fly". Here is an example by Allen Browne:
    How to build a criteria string form the non-blank search boxes
    http://www.allenbrowne.com/ser-62code.html

    Using this method, the unbound search controls can be left blank. You can have both controls blank, the "StartDate" blank, the "EndDate" blank or enter both "StartDate" and "EndDate" values.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating an IF statement within a Form
    By csd003 in forum Forms
    Replies: 3
    Last Post: 07-29-2014, 01:27 PM
  2. Creating If Statement in form
    By MaxLev in forum Forms
    Replies: 1
    Last Post: 12-11-2012, 02:06 AM
  3. Replies: 4
    Last Post: 04-23-2012, 05:07 PM
  4. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  5. Problem With IF Statement
    By MuskokaMad in forum Programming
    Replies: 0
    Last Post: 03-14-2010, 05:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums