Results 1 to 12 of 12
  1. #1
    FruStalin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    18

    Problems with date criteria in IIF statement, help is needed !

    Hello there



    I hope that someone is able to help me with my little problem.
    I have the below sentence in the Access 2007 design querie maker. See the attached file.

    Like IIf([Forms]![frmJournalMain]![chkKritisk]=True;"*";Date()>=[Forfaldsdato])

    But is doesnt work proberly!
    My intension is this: when the checkbutton (chkKritisk) is true then show all record, If chkbutton is false show only dates that is less the [Forfaldsdato].

    This it how it works: When chkbutton is true, all records are shown, if chkbutton is false, non record at all is shown. This is my problem :-)

    When I do this Date()>=[Forfaldsdato]) in the design querie without the IIF statement, it works correct. But in the IFF statement it fails.
    I believe that I need the IIF to be able to switch between the two modes.

    Hope that some of you brilliant guys can help me.

    Regards

    Morten
    Attached Thumbnails Attached Thumbnails Access.jpg  

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Try putting quotation marks around the second part of the IIF:

    Like IIf([Forms]![frmJournalMain]![chkKritisk]=True;"*";"Date()>=[Forfaldsdato]")

    This may be an A2007 thing (I don't use it), but you should also change the ";" to commas:

    Like IIf([Forms]![frmJournalMain]![chkKritisk]=True,"*","Date()>=[Forfaldsdato]")

    HTH

    John

  3. #3
    FruStalin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    18
    Hello John

    Thanks for your effort.
    Neither of you suggestions solve the problem.
    Using "extra" quotation marks doesnt change anything at all. It does exactly the same :-)
    The commas makes an error. I belive that comma is used VBA. My problems is in the querie designer.

    Do You have others suggestions ?

    Regards

    Morten

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Code:
    Like IIf([Forms]![frmJournalMain]![chkKritisk]=True,"*",Date()>=[Forfaldsdato])
    Have you tried replacing only the semi-colon with comma & not changing anything else.

    Edit :
    Alternatively, check out below to see if it gives some guidelines :

    https://www.accessforums.net/showthr...3-Search-Query

    Thanks

  5. #5
    FruStalin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    18
    Thanks for the effort, but it does not solve the problem. In the query desginer the commas is not accepted at all. I get an instant error message!

    The problem must be in the construction of the IIF statement, due to the Date()>=[Forfaldsdato] is working proberly when it stands alone i the query design window.

    It it like the date() statement doesnt recognaize the [Forfaldsdato] field.

    Sometimes you just dont understand that damn program :-)

    Regards

    Morten

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just check out if below gives some guidelines :
    SELECT
    myTable......,
    myTable.....,
    myTable.[Forfaldsdato]
    FROM
    myTable
    WHERE
    (((1)=1)
    AND
    ((IIf([Forms]!frmJournalMain!chkKritisk=True,[Forfaldsdato] Like "*",Date()>[Forfaldsdato]))<>False));

    Thanks

  7. #7
    FruStalin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    18
    Hello recyan.

    Do you suggest that I put all this into the query designer or do you have your mind on VBA code?

    Why the three myTable...? Is that only ment as an example?

    Regards

    Morten

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Oops Sorry, Those 3 mytable's are your other fields in your table. mytable is your table name. First try the iif only with your [Forfaldsdato] field. See how it works.

    Open a Query in the Query SQL Design View & then paste the Code. Then Run the Query. If it runs OK, open the query in Design (Builder ) view & take a look.

    Edit :
    In case you are comfortable using VBA ( I do not know VBA syntax ), check if below (pseudo-code) gives some guidelines :

    strsql = "SELECT field1, field2, [Forfaldsdato], field4, field5 FROM mytable WHERE 1 = 1 "

    IF [Forms]!frmJournalMain!chkKritisk=True THEN
    strsql = strsql & " AND [Forfaldsdato] Like '*'"
    END IF

    IF [Forms]!frmJournalMain!chkfield4=True THEN
    strsql = strsql & " AND [field4] = '25'"
    END IF

    IF TRIM([Forms]!frmJournalMain!formfield5) Is Not Null THEN
    strsql = strsql & " AND [field5] = ' & [Forms]!frmJournalMain!formfield5 & '"
    END IF

    Thanks

  9. #9
    FruStalin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    18
    To my new best friend recyan, it worked
    Your proposol in post #6 did it, without any VBA.
    But the solution doesnt make any sense to me. Could you please explain what is happening after the WHERE SQL statement. (((1) =1 and ..... What is going on?

    Many many thanks

    Mortem

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Take a look at my signature.
    Cannot explain things properly, but will try to do it hoping for corrections & inputs from the experts :
    1) The 1 = 1 .
    Why is it there ?
    Suppose, I have a form with 5 search text fields & the user doesn't enter anything in those fields & submits the Form, then I would like it to return all the records. In this case All the IF's will not feed the query anything, hence the "WHERE 1 = 1".
    To understand it better, take a look at the VBA pseudo-code written earlier.
    In the query code example, which I had given earlier, try removing the "1 = 1" & I think, it should still work in your case.

    2)
    Code:
        (
            IIf(
                [Forms]!frmJournalMain!chkKritisk=True, [Forfaldsdato] Like "*", Date()>[Forfaldsdato]
            )
        )<>False
    This is a bit difficult for me to explain.
    The above criteria evaluates each record for the condition to either a True or a False (-1 or 0) & then we filter it on "<>False".
    To understand it better, Go in to the Query Builder View for your query & give an alias name to the IIf(), something like below.
    Code:
        SELECT 
            field1, 
            field2, 
            Forfaldsdato, 
            IIf([Forms]!frmJournalMain!chkKritisk=True, [Forfaldsdato] Like "*", Date()>[Forfaldsdato]) AS ActiveDate
        FROM 
            myTable;
    Take a look at the results.

    Then add below clause to the query at the end :
    Code:
    WHERE (((IIf([Forms]!frmJournalMain!chkKritisk=True, [Forfaldsdato] Like "*", Date()>[Forfaldsdato]))<>False));
    Take a look at the results.

    Then add, instead of above WHERE clause, below clause to the query at the end :
    Code:
    WHERE (((IIf([Forms]!frmJournalMain!chkKritisk=True, [Forfaldsdato] Like "*", Date()>[Forfaldsdato]))<>True));
    Take a look at the results.

    Hope someone explains this better.

    PS : I still do not know whether the solution that I have suggested to you is an efficient way of doing it, but currently, that is the only way I know.

    Thanks

  11. #11
    FruStalin is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2012
    Posts
    18
    Thanks for your effort, really.

    I will try go into your explanation and see if I get find the deeper meening of it all :-)

    Ragards

    Morten

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Open your query in SQL view.
    Use the line below as your WHERE clause:
    WHERE Date()>=[Forfaldsdato] OR [Forms]![frmJournalMain]![chkKritisk]=True
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Linking a criteria form and other problems....
    By wends in forum Database Design
    Replies: 3
    Last Post: 03-07-2012, 06:45 PM
  2. If statement with Todays Date being criteria
    By robsworld78 in forum Access
    Replies: 2
    Last Post: 08-19-2011, 08:22 PM
  3. Query Criteria help needed
    By iuianj07 in forum Queries
    Replies: 1
    Last Post: 01-21-2011, 02:57 PM
  4. Problems with an If statement
    By slenish in forum Access
    Replies: 1
    Last Post: 03-10-2010, 09:23 PM
  5. Replies: 0
    Last Post: 01-01-2007, 02:26 PM

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