Only if Today=Friday (1 Viewer)

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #1

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

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #2

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())

 

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #3

It does not help

 

pr2-eugin

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #4

Your reply,

Mhelp said:

It does not help

Click to expand...

Does not help either

 

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #5

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

 

pr2-eugin

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #6

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

 

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #7

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

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #8

How about this?

Code:Copy to clipboard

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"; "")

 

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #9

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

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #10

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.

Click to expand...

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

 

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #11

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

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #12

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

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 19:45

Joined

Aug 11, 2003

Messages

11,695

• May 22, 2014

• #13

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:

Code:Copy to clipboard

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

Super Moderator

Local time

Today, 18:45

Joined

Nov 30, 2011

Messages

8,494

• May 22, 2014

• #14

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 !

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 19:45

Joined

Aug 11, 2003

Messages

11,695

• May 22, 2014

• #15

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

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

 

M

Mhelp

Registered User.

Local time

Today, 10:45

Joined

May 19, 2014

Messages

16

• May 22, 2014

• #16

pr2-eugin said:

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 !

Click to expand...

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)