10 Replies Latest reply on Aug 10, 2009 11:19 AM by comment_1

    Query using Date

    Smills

      Title

      Query using Date

      Post

      I'm having a problem with the following formula:

       

      If(Sampled Thawed="";If(BLEED DATE>6/16/2009;"Company";"");"")

       

      The first part of the formula works but the date is messing it up the second part of the formula.  I won't recognize the date.  In excel if you turn the date to a value (i.e. 39880), it works but in FM it does not.  Help!

        • 1. Re: Query using Date
          ajmiller
            

          It's probably treating your date as a numerical calculation.

           

          I think this would definitely work 

          If(Sampled Thawed="";If(BLEED DATE>Date(6,16,2009);"Company";"");"")

           

          It might also work if you wrap the date in quotes

          If(Sampled Thawed="";If(BLEED DATE>"6/16/2009";"Company";"");"")

          but I'm less certain 

          • 2. Re: Query using Date
            Smills
              

            Didn't work.  I tried the parenthesees (sp) and it's still putting "company" for everthing without a T in Sample thawed.  In other words, it's still the date issue.  " " turns it into a text field, which I had already tried.

             

            I know it's the date but I can't figure out why.  I've changed the format and everything.  I'm completely stumped.

            • 3. Re: Query using Date
              ajmiller
                

              I guess I can't actually determine what's wrong from the information you've given. 

               

              BLEED DATE is a Date field (and not a Number field) or a calculation that returns a Date? if a calculation is it stored or unstored?

               

              Are the dates in BLEED DATE actually greater (later) than the test date? e.g. 7/7/2009 > 6/16/2009

               

              And Date(6,16,2009) is not just parenthesis, but a call to the Date function.

               

               

              • 4. Re: Query using Date
                Smills
                  

                Correct.  Bleed Date is a date field, non-calculated.  I just double-checked the format too and it's formatted as a date.  I tried the ( ) with the commas but I got a message that there are too many ( or )'s and it highlighted the , in between the date numbers.  I double checked the ( )'s as well and there weren't too many of either.  Finally, yes, there are Bleed Dates all over the board.  I have > than 6/16/2009 and < as well.

                 

                I appreciate your help so much.  I'm at end of the rope on this one.  It kills me when I can't figure out a formula problem. 

                • 5. Re: Query using Date
                  ninja
                    

                  Howdy Smills,

                   

                  Could it be the parenthesis in the worng place?

                   

                  If( Table1::Sample Thawed = ""; If ( Table1::BleedDate > 6/16/2009 ; "Company" ; "" ; "" ))

                   

                  Haven't mocked it up...but here it is for what it's worth.

                   

                  HTH

                  • 6. Re: Query using Date
                    Smills
                       No, didn't work.  Sorry.  Beyond frustrating.
                    • 7. Re: Query using Date
                      comment_1
                        

                      The test should be =

                       

                      If ( IsEmpty ( Sampled Thawed ) and BLEED DATE > Date ( 6 ; 16 ; 2009 ) ; "Company" )

                       

                      This assumes BLEED DATE is a field of type Date, and contains a valid date.



                      Note that is bad practice to hard-code dates (or data in general) in a calculation formula.
                      • 8. Re: Query using Date
                        ajmiller
                           That's interesting. When I look at the Date function template in the calculation window, I see Date(month; day; year) and not Date(year; month; day). I suppose it can disambiguate using the 4-digit number always for the year. 
                        • 9. Re: Query using Date
                          Smills
                            

                          IT WORKED!!!!!  I didn't put the word DATE prior to DATE(6;16;2009).

                           

                          THANK YOU SO MUCH AND PROBLEM SOLVED!!  Yoooohooooo!

                          • 10. Re: Query using Date
                            comment_1
                              

                            ajmiller wrote:
                            When I look at the Date function template in the calculation window, I see Date(month; day; year) and not Date(year; month; day). I suppose it can disambiguate using the 4-digit number always for the year. 

                            No, the order of the parameters is set in stone. It also disregards the local date formats in use.