Make table weekday function

[ChristopherL]

Hi there! I am currently working on my iif weekday function in a make table query.
What I want it do is that if its monday take date()-3, else date()-1.

I cant get it to work..

DateAdd("d", IIf(Weekday([upload_date]) = 2, -3, -1)

I tried this, wont work..

 

[pr2-eugin]

You are missing the third Argument for the DateAdd function..

However the use of th DateAdd in MakeTable worries me a bit..

 

[bob fitz]

Perhaps:

DateAdd("d", IIf(Weekday([upload_date]) = 2, -3, -1),[upload_date])

 

[ChristopherL]

You are missing the third Argument for the DateAdd function..

However the use of th DateAdd in MakeTable worries me a bit..

Hmm okay, how do you suggest id do it then?

 

[ChristopherL]

Perhaps:

DateAdd("d", IIf(Weekday([upload_date]) = 2, -3, -1),[upload_date])

Tried putting this as a parameter but it didnt work..
Any suggestion how you would solve it?
Column name is upload_date and just need something that says if monday do date()-3 else date()-1..

Thanks for trying before tho!

 

[pr2-eugin]

It should be..

DateAdd("d", IIf(Weekday([upload_date]) = 2, -3, -1), Date)

What I meant with the MakeTable worries me is..
1. The field name will be a Date Value.. Not a great field name as it will include Special characters..
2. It seems like your field name changes everyday, will it not be hard to Query the table later?

Only trying to help, no need for sarcasm..

Hmm okay, how do you suggest id do it then?

Good luck..

 

[ChristopherL]

It should be..

DateAdd("d", IIf(Weekday([upload_date]) = 2, -3, -1), Date)

What I meant with the MakeTable worries me is..
1. The field name will be a Date Value.. Not a great field name as it will include Special characters..
2. It seems like your field name changes everyday, will it not be hard to Query the table later?

Only trying to help, no need for sarcasm..
Good luck..

It definetly wasn't sarcasm, I just thought that since it worried you, you might had an other solution for it then me.. Since I am quite a rookie at access! So I was merely trying to seek your guidance!

For 1. The field is retrieved through a linked database so can't change it...
2. The field name doesnt change, only new values gets appended to the database.. So i will always need to get the date from date()-1 unless its monday, then date()-3

 

[pr2-eugin]

Okay my bad,

I thought you were creating a Table.. I did not realize you were adding records to existing table.. I apologize.. Well the code I gave in Post#6 should work..

 

[ChristopherL]

Okay my bad,

I thought you were creating a Table.. I did not realize you were adding records to existing table.. I apologize.. Well the code I gave in Post#6 should work..

No worries!
I am creating sort of a new table, I am making a copy of a linked table, and fetching only from a certain date! However I just put your #6 as a new field, and tried it as a criteria.. didnt work

 

[pr2-eugin]

Access is a bit weird when it comes to Dates.. See the International Date Problem.. See if that sheds some light on the matter..

 

[ChristopherL]

Access is a bit weird when it comes to Dates.. See the International Date Problem.. See if that sheds some light on the matter..

Yup still won't get it to work.. But thanks for the help so far

 

[ChristopherL]

SOLVED:
I nested myself in this problem, heres the very simple and handy answer...

where IIf(Weekday(Date())=2;Date()-3;Date()-1)

 

[pr2-eugin]

Could you please provide the SQL?

 

[ChristopherL]

Could you please provide the SQL?

Ofcourse, this works for me now.
Think I over-thinked the situation and created a problem on my own..

SELECT dbo_pf_tree_mb.child_name, dbo_pf_tree_mb.upload_date, Left([child_name],20) AS child_nameT INTO getSDportfolios
FROM dbo_pf_tree_mb
WHERE (((dbo_pf_tree_mb.upload_date)=IIf(Weekday(Date())=2,Date()-3,Date()-1)) AND ((dbo_pf_tree_mb.name)="SD_EQ_Secondary_#x#_STO"));

 

[pr2-eugin]

Well glad you have it sorted now..

 

[ChristopherL]

Well glad you have it sorted now..

Thanks for helping!