Search results

Hi, I have used this code, and it works nicely. I have the fSetAccessWindow (SW_SHOWMINIMIZED) on the ONload for every form. I have only one small issue, When the user uses another program or opens up mydocuments etc then switches back to access. The Grey background behind my popup forms...

Ah thank you so much :) always feel silly when it something that simple lol Cheers :)

Hi, very quick question. This filter vba doesnt work Me.Filter = "abcode= " & i And "EmpASID = " & f Me.FilterOn = True runtime error 13 type mismatch. Both Abcode and EmpASID are Numerical. So is i and f Me.Filter = "abcode= " & i works fine on its own Me.Filter = "EmpASID...

Hi David, Yes it is solved :) I have adapted your idea of a lookup table and namliam's code to create a lookup table. I ran a simple loop function to record the date with its week number-year (1st day of the week) so i can easily convert a week number to a date and use that date+6 days as a...

Just finished testing it on a few items. I believe it works too :) ty very much :) I will use this to get the weeknumber only. And my code to get the weeknumber + year. Again, thank you very much :) Iv stumbled, tripped and fell along the way while writing this code, but it is now solved :)...

Public Function getWKN(test As Date) Dim prevsun As Date Dim YN As Date WKN = DatePart("ww", test, vbSunday, vbFirstJan1) iyear = Year(test) MsgBox iyear If WKN = 53 Then prevsun = (test - Weekday(test) + 1) If prevsun <> CDate("25/12/" & Year(test)) Then WKN = 1 YN = DateAdd("yyyy", 1, test)...

I think i have it Public Function getWKN(test As Date) As Integer Dim prevsun As Date WKN = DatePart("ww", test, vbSunday, vbFirstJan1) If WKN = 53 Then prevsun = (test - Weekday(test) + 1) If prevsun <> CDate("25/12/" & Year(test)) Then WKN = 1 Else WKN = 53 End If End If getWKN = WKN End...

Yeah, tell me about it lol... :) Basically this system is used to keep weeks "whole" for their absence stats. in my example dates above - week 53 of 2013 is only 3 days long and week 1 of 2014 is 4 days long. They basically start the year 3 days early to capture 1 whole week. Im being paid...

Function DatePartCheck(dateChecked as Date) As Integer Dim yearStart as Date Dim yearCheck, i as Integer i = DatePart("ww", dateChecked, vbSunday, vbFirstJan1) If i = 53 Then yearCheck = Year(dateChecked) +1 yearStart = Dlookup("startDate","lookupTable", "inYear = " & yearCheck) If...

Thanks david, I will write up a lookup table tonight then run your code, it looks promising :) Thanks for all the advice so far :)

Private Sub Command2_Click() Dim Test As Date Dim yy As Date Test = Text0 yy = Year([Test]) If Test >= "25/12" & yy Then MsgBox Weekday("25/12" & yy) If Day("25/12" & yy) = 25 Then WeekNum = 53 Answer = WeekNum & "-" & yy MsgBox Answer End If Else 'it uses the getweeknum function MsgBox...

If the last sunday of the year is the 25th of December then that is week 53, if the last sunday is greater after the 25th then that sunday starts week 1 of the next year. Private Sub Command2_Click() Dim Test As Date Test = Text0 checker = Test - Weekday(Test) + 1 'finds the previous sunday...

? DatePart("ww", "29/12/2013", vbSunday, vbFirstJan1) 53 Gives me week 53-2013 when it should be week 1 of 2014, How would I adjust this using a lookup table?

Unfortunately the option in access do not :( thanks for the support :)

Yeah, you are right, Thanks for spotting it. It also fails (so far) at 12/25/2033, Im still trudging through checking all the results. Looking for a pattern. I have checked manually the week numbers if you start week 1 2022 as 12/26/21 Then 12/25/22 should be week 53 as it its whole week is...

plog You are an absolute legend!!! The longevity of this system will be 10 years max ( so im going to check all several key dates up to 2023) Also I think it is because the week 1 can start in the previous year slightly so it covers 4 years and a bit, hence the 2 extra days. As long...

I'll look into that idea. Thanks david :)

cross posted at" utteraccess Converting-Dates-Week-Nu-t2009192" cant post links yet.

Some of my tests ?DatePart("ww", #12/25/2016#, vbSunday, vbFirstFourDays) 52 ?DatePart("ww", #12/25/2016#, vbSunday,vbFirstFullWeek) 52 ?DatePart("ww", #12/25/2016#, vbSunday, vbjan1) 53 ?DatePart("ww", #12/27/2015#, vbSunday, vbjan1) 53 ?DatePart("ww", #12/25/2016#...

Is that an issue?