Only if Today=Friday

[Mhelp]

I have tried this code in queries. "Send" should only be showed if the current day is a friday, however I can´t get it to work:

IIf((Date()+1=DateAdd("d";12;[Baseline]![Baseline dato]) And Day(Date())="vbFriday" And [Person]![Status]="Participate") OR
(Date()+2=DateAdd("d";12;[Baseline]![Baseline dato]) And Day(Date())="vbFriday" And [Person]![Status]="Participate");"Send";"")

If I remove the red part it works, but I will like it only to work on Fridays.

 

[pr2-eugin]

vbFriday is an Integer constant, so remove the double quotes.

Also Day() function will return the Day i.e for today it will return 22. So you need to change it to Weekday(Date(), vbSunday), instead of Day(Date())

 

[Mhelp]

It does not help

 

[pr2-eugin]

Your reply,

It does not help

Does not help either

 

[Mhelp]

Weekday(Date(), vbSunday),is not working either

 

[pr2-eugin]

Are you using this Immediate If in a Query? Show the edited IIF you have.

 

[Mhelp]

IIf((Date()+1=DateAdd("d";12;[Baseline]![Baseline dato]) And
Weekday(Date();vbSunday)=vbFriday And [Person]![Status]="Participate") OR
(Date()+2=DateAdd("d";12;[Baseline]![Baseline dato]) And Weekday(Date();vbSunday)=vbFriday And [Person]![Status]="Participate");"Send";"")


I get a Message that translated to english says someshing about the datatype don't match

 

[pr2-eugin]

How about this?

IIF((Weekday(Date(), 1) = 5) And ((Date() + 1 = DateAdd("d"; 12; [Baseline]![Baseline dato]) And [Person]![Status] = "Participate") OR
(Date() + 2 = DateAdd("d"; 12; [Baseline]![Baseline dato]) And [Person]![Status]="Participate")); "Send"; "")

 

[Mhelp]

thanks for your help, but still not working (however i don´t get any massage - the field just stay blank where it shouldn´t be)

 

[pr2-eugin]

Give an example of what you want, for instance say in words.

In the field name "sampleField" I have a date 21/05/2014, I want to check it with yesterday and if they are same I want the field to be this.

Something along those lines, then I will rewrite the code for you.

 

[Mhelp]

I got it now - I had to go in and change the datatype to text (it was date)

Thank you for your help I really appreciate it

 

[pr2-eugin]

Are you sure it is not the other way around? Most of the functions will struggle to work properly if they are not Dates.

 

[namliam]

Shouldnt the "," in the weekday function be a ";" ??
Also the 1 means the week starts at SUNDAY not monday, if you are looking for a friday, then you should be doing = 6 not = 5 ( or change the 1 to a 2)

If you are using "Date() + 1"
Why use Dateadd function 2 characters later instead of "[Baseline]![Baseline dato] + 12"

Why make things harder than they need to be (and not to mention unreadable) by having the doubling of the "Participate" in there...

All that would make the IIF statement be:

IIF(     (Weekday(Date(); 2) = 5) 
     And [Person]![Status]="Participate"
     And (    Date() + 1 = [Baseline]![Baseline dato] + 12 
          OR  Date() + 2 = [Baseline]![Baseline dato] + 12
         ); "Send"
          ; "")

edit: changing the datatype from date to text is a very bad idea, dates are dates for a reason let them be dates!

 

[pr2-eugin]

Yes Yes Yes ! I was under the impression today was Friday ! I don't know why ! :banghead: It should be a 6 not 5.

Thanks for butting in namliam !

 

[namliam]

Luckily my butt is that big that it is always butting something somewhere.

Have a day off tomorrow or had a long week thusfar?

 

[Mhelp]

Yes Yes Yes ! I was under the impression today was Friday ! I don't know why ! :banghead: It should be a 6 not 5.

Thanks for butting in namliam !

Well, it works now with datatype=date and with I tried with a 5 to test if it works today (I have changed to 6 now, and will see tomorrow if it is working)