Date()-1

[ChristopherL]

Hiho..
Just finished my project when I realised one thing..
My query takes values from date()-1, the day before.
But since we only get data from mon-fri, the monday data will be messed up..
Any suggestions on how to solve this?

Right now I have the table[ReportDate]
and uses the where [ReportDate]=Date()-1

 

[Mihail]

Seems that the answer is in your code.
where [ReportDate]=Date()-1

use:
where [ReportDate]=Date()

 

[ChristopherL]

Seems that the answer is in your code.
where [ReportDate]=Date()-1

use:
where [ReportDate]=Date()

The reasen I use Date()-1 is because we run the report at 9 a.m.
Report date gets updated at 4 p.m.

So for mondays I need the dates from friday, that is my issue!

Thanks anyway Mihail!

 

[Mihail]

Take a look to the WeekDay function. Maybe you can find an approach using this function and an IIF.
Anyway, yours explanations are out of my understanding.

 

[ChristopherL]

I'll take a look at the weekday funciton thank you!

What I need help with is pretty simple tho.
We compare values of stockportfolios.
How we value it and how the counterparty values it.
We get the value from our counterparty the day before(therefore Reportdate=date()-1) and compare them with how we value the portfolio.

Therefore we take the report date from yesterday, so on mondays, date()-1 will mean sunday, what I want it to fetch is friday, since it's the latest report date.

 

[CJ_London]

try

where [ReportDate]=dateadd("d",-iif(Weekday(Date())=vbMonday,3,1),Date())

 

[Mihail]

That is a much better explanation
And the CJ's code should do the trick.

 

[ChristopherL]

try

where [ReportDate]=dateadd("d",-iif(Weekday(Date())=vbMonday,3,1),Date())

I believe this would do the trick aswell!
Might be a stupid question, but does this code look the same in access 02? Cause it worked in my access 2010 but not in 02 :/

 

[CJ_London]

Not sure - according to this link it has been available since Access 2000

http://www.techonthenet.com/access/functions/date/weekdayname.php

 

[ChristopherL]

Not sure - according to this link it has been available since Access 2000

http://www.techonthenet.com/access/functions/date/weekdayname.php

I am wondering if it is because the date that I had was enter as text, so I made this column

CurrentDate: CDate([date])

This do not work for me!

where [CurrentDate]=dateadd("d",-iif(Weekday(Date())=vbMonday,3,1),Date()

 

[CJ_London]

not sure if this is your posting, but you are missing a bracked at the end

where [CurrentDate]=dateadd("d",-iif(Weekday(Date())=vbMonday,3,1),Date())

 

[ChristopherL]

not sure if this is your posting, but you are missing a bracked at the end

That was silly out of me thanks
but now when I run the query it says "Enter Parameter Value" for CurrentDate?

 

[CJ_London]

Do you have a field called Date? this

CurrentDate: CDate([date])/QUOTE] implies you do.

If so, Date is a reserved word and using it as a field name will be causing a problem.

I was presuming that currentdate was the name of a filed in your query

 

[ChristopherL]

Do you have a field called Date? this

CurrentDate: CDate([date])/QUOTE] implies you do.

If so, Date is a reserved word and using it as a field name will be causing a problem.

I was presuming that currentdate was the name of a filed in your query

Yes I do have a fieldname called date. It's named so from the dbo that I import from and I dont have any access to change the names in that dbo.

Any suggestions on how to solve that?
And many thanks for your patience with me.

 

[CJ_London]

Personally, I'd slap someones wrist. You say dbo - is it coming from SQL server or similar? if so, it is a reserved word there as well.

Are you linking to the source or importing it? If importing, you can change the name on import.

Other things - try this alternative

format([Date],"dd/mm/yyyy")=format(dateadd("d",-iif(Weekday([COLOR=red]Now[/COLOR]())=vbMonday,3,1),[COLOR=red]Now[/COLOR]()),"dd/mm/yyyy")

 

[ChristopherL]

Personally, I'd slap someones wrist. You say dbo - is it coming from SQL server or similar? if so, it is a reserved word there as well.

Are you linking to the source or importing it? If importing, you can change the name on import.

Other things - try this alternative

format([Date],"dd/mm/yyyy")=format(dateadd("d",-iif(Weekday([COLOR=red]Now[/COLOR]())=vbMonday,3,1),[COLOR=red]Now[/COLOR]()),"dd/mm/yyyy")

Haha I would definetly like to slap my boss's wrist for that! It's linked to a sql server that is once a day updating counterparty values.

The query that I am doing is making a Make-table query where I select the values for the last reported date. That field is inconvenient named date