Hi All,
First time posting but I would like to thank you for all the questions you have unknowingly answered.I am pretty new using access and these forms have been really helpful. I am having an issue that I can’t seem to find a solution to.
I am receiving some data that comes in with a date in text in the format of YYYY in column A and Week number in text in the format of WW in column B. I am trying to make a query that combines them into one column and something I can run some criteria against. For example, show me all for previous 4 weeks.
My attemptI am using the Expression Builder)
Field: DateAdd("ww",
![Week #]-1,DateSerial(
![Year],1,5))
Criteria: Between Format(DateAdd("ww",-1,Date()),"yyyyww") and Format(DateAdd("ww",-4,Date()),"yyyyww")
The problem is the result will include 20186-20189 along with 201851 - 20192 when I would expect for it to only return 201851 - 20192. Today being WK3 of 2019. I suspect this is because of the format in my Field.
Year Week Attempt today 4weeks past
2018 6 20186 20193 201851
2018 7 20187 20193 201851
2018 8 20188 20193 201851
2018 9 20189 20193 201851
2018 51 201851 20193 201851
2018 52 201852 20193 201851
2019 1 20191 20193 201851
2019 2 20192 20193 201851
How can I make Access realize that 2018 is a year in date format, 52 is a WK in date format, combine them into YYYYWW format so that I can run some criteria against?
First time posting but I would like to thank you for all the questions you have unknowingly answered.I am pretty new using access and these forms have been really helpful. I am having an issue that I can’t seem to find a solution to.
I am receiving some data that comes in with a date in text in the format of YYYY in column A and Week number in text in the format of WW in column B. I am trying to make a query that combines them into one column and something I can run some criteria against. For example, show me all for previous 4 weeks.
My attemptI am using the Expression Builder)
Field: DateAdd("ww",
Criteria: Between Format(DateAdd("ww",-1,Date()),"yyyyww") and Format(DateAdd("ww",-4,Date()),"yyyyww")
The problem is the result will include 20186-20189 along with 201851 - 20192 when I would expect for it to only return 201851 - 20192. Today being WK3 of 2019. I suspect this is because of the format in my Field.
Year Week Attempt today 4weeks past
2018 6 20186 20193 201851
2018 7 20187 20193 201851
2018 8 20188 20193 201851
2018 9 20189 20193 201851
2018 51 201851 20193 201851
2018 52 201852 20193 201851
2019 1 20191 20193 201851
2019 2 20192 20193 201851
How can I make Access realize that 2018 is a year in date format, 52 is a WK in date format, combine them into YYYYWW format so that I can run some criteria against?