Help...

[bionicman]

can anyone please help shed some light on this for me? i am not very good with VBA, and i know it just has to be a light coding thing.

Thanks in advance!

 

[pdx_man]

Do you understand why it is that this problem exists? Do you understand how the code flows? It's the old give a man a fish, theory ... I can just give you the answer, but do you understand why? What would be a workaround? Can we use something else besides week numbers? Think about it a sec.

 

[bionicman]

i understand that you are trying to help, and i appreciate it, but i just don't get it. i am sorry if i am becoming a pain, but it is so close to what i want, that it is gonna bug me if i just have to scrap it because i can't figure it out!

i understand if there is nothing there, it uses 1 (or +1 as the case might be) to start the following week.

the only other way i could think to do it is the way i started to do it, entering the date range 4 times.

as i have stated before, i am no good with VBA, and i know just enough access to get me by, i appologize if i just don't understand, but rest assured i am not looking for just an answer, i would like to understand why or why something is not working. in this case, i know why it is not working, i just have no idea how to make it work.

If you feel like still helping me, thanks in advance!
If not, thanks for all that you have done!

 

[pdx_man]

OK, the reason why it is doing what is does, is because there is no information for one of the weeks. The code assumes there is, which is not the case, but it automatically increments the week counter by one. So, how do we do this? Don't use week numbers. Instead, list it out by the day the week begins. To do this, we have to roll the Date of Error back to the previous Monday for that week. That is what the code below does.
Replace all the code behind the report with this:

Option Compare Database
Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
    Me.Label116.Caption = "Week :" & Format(GoBack(Me.Date_of_Error), "short date") & " Total:"
End Sub
Private Sub Report_Close()
    DoCmd.Close acForm, "Frm_Enter_Criteria"
End Sub
Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm "Frm_Enter_Criteria", acNormal, , , acFormEdit, acDialog
End Sub
Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbMonday) = 1
        InDate = DateAdd("d", -1, InDate)
    Loop
    GoBack = InDate
End Function

 

[bionicman]

again, thank you for your time. i am experiencing the same kind of problem as i was before. If an employee has errors on week 1, and 3, but 0 errors for week2, it will only display week 1.

I don't care if i get a row of zeros for the week where there is no data, but i at least would need it to show week 1 and 3, where there is data.

thanks again

 

[pdx_man]

When you add this code, you should not have any week numbers present ... just the week's begining dates.

If there are errors on 09/05/03 and errors on 09/17/03, are you saying these are being grouped together with a 09/01/03 week beginning date?

 

[bionicman]

well i was still using weeks as the terms, but it was showing the beginning mondays.

what i mean is i don't think it is showing them at all. i can run a different report, so i know there are errors on those dates, but if the code hits a week where there is nothing, it seems to stop there, and not check the following weeks.

for instance, if there are errors on the week beginning 9/1, zero errors for the week beginning 9/8, and errors again the week beginning 9/15, it will only show the data for the week beginning 9/1.

 

[pdx_man]

Hmmm,
Using your sample that you gave me, I changed the Date Of Error to exclude the second week, and it ran fine. Gave me results for weeks 09/01/03, 09/15/03 and 09/22/03. Can you post a DB including only the Contacts2 data for the center in question? Or just tell me the dates that you are using, 'cause it should be working for you, unless you have made other changes ...

 

[pdx_man]

OK, our systems may be different on how Access handles the Grouping of the Date of Error by week. Try this:

Change the GoBack function to:

Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbSunday) = 1
        InDate = DateAdd("d", -1, InDate)
    Loop
    GoBack = InDate + 1
End Function

Change the criteria for the Date of Error field in Qry_Another_Option query to:

>=[Forms]![Frm_Enter_Criteria]![Beg_Date] And and < DateAdd('d',6,[Forms]![Frm_Enter_Criteria]![Beg_Date])

Give that a go and let me know!

 

[bionicman]

ok, now i am VERY confused, i am getting an error no matter what i do. i was not getting this error yesterday, even if i go back to just what i had yesterday, i am still getting the error. i am getting a runtime error on this line:

the error says runtime error 2427 "You entered an expression that has no value"

Me.Label116.Caption = "Week :" & Format(GoBack(Me.Date_of_Error), "short date") & " Total:"

Below i have pasted the rest of the code, i have not changed anything other than the things you listed, i do not know why i am getting this error.

Option Compare Database
Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
    Me.Label116.Caption = "Week :" & Format(GoBack(Me.Date_of_Error), "short date") & " Total:"
End Sub
Private Sub Report_Close()
    DoCmd.Close acForm, "Frm_Enter_Criteria"
End Sub
Private Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm "Frm_Enter_Criteria", acNormal, , , acFormEdit, acDialog
End Sub
Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbSunday) = 1
        InDate = DateAdd("d", -1, InDate)
    Loop
    GoBack = InDate + 1
End Function

 

[bionicman]

ok, i got it working!!! YEAH!

i do not get the error if i do not put in this

>=[Forms]![Frm_Enter_Criteria]![Beg_Date] And and < DateAdd('d',6,[Forms]![Frm_Enter_Criteria]![Beg_Da
te])


one quick question. if i put in 10/01/03 as my beginning date, the first week shows up as 9/29, because that was the monday prior to 10/1. the report will only show the data from 10/1 though right? i know it will be displayed as the 29th, and that is no problem.

Thanks again for ALL your help!!!

 

[pdx_man]

What would be a solution to that ... ? Don't settle ... solve. That is the joy of programming. You can make it what you want.

Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbMonday) = 1
        InDate = DateAdd("d", -1, InDate)
        If InDate = [Forms]![Frm_Enter_Criteria]![Beg_Date] Then Exit Do
    Loop
    GoBack = InDate
End Function

Can you tell me what the difference is between:
>=[Forms]![Frm_Enter_Criteria]![Beg_Date] And and < DateAdd('d',6,[Forms]![Frm_Enter_Criteria]![Beg_Date])
and
Between [Forms]![Frm_Enter_Criteria]![Beg_Date] And and DateAdd('d',6,[Forms]![Frm_Enter_Criteria]![Beg_Date])

?

Which is what you want?

 

[bionicman]

I think i understand.... tell me if i am right or wrong

Between [Forms]![Frm_Enter_Criteria]![Beg_Date] And and DateAdd('d',6,[Forms]![Frm_Enter_Criteria]![Beg_Da
te])

this one is telling it to look between the date i enter, and 6 days later (monday-sunday for example).

The greater than or equal to, i don't really understand.

i think i would need the one i put above because it is telling it the date range of the weeks...

is that right? (I know i am probably way off )

 

[bionicman]

ok, now i went to put in the above code

Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbMonday) = 1
        InDate = DateAdd("d", -1, InDate)
        If InDate = [Forms]![Frm_Enter_Criteria]![Beg_Date] Then Exit Do
    Loop
    GoBack = InDate
End Function

and i am still getting the prior monday. now if i am correct, this code is telling it that IF (while it is going back to the prior monday) it comes across the date that i entered, it should display that date instead of the prior monday right?

 

[pdx_man]

My bad,
I had the wrong line there ... sorry. Should have been:

>= [Forms]![Frm_Enter_Criteria]![Beg_Date] And < DateAdd('m',1,[Forms]![Frm_Enter_Criteria]![Beg_Date])
NOT
Between [Forms]![Frm_Enter_Criteria]![Beg_Date] And DateAdd('m',1,[Forms]![Frm_Enter_Criteria]![Beg_Date])

For the criteria for the Date of Error field. And the question would be "What's the difference?"

What would this change do for us?

Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbMonday) = 1
        If InDate = [Forms]![Frm_Enter_Criteria]![Beg_Date] Then Exit Do
        InDate = DateAdd("d", -1, InDate)
    Loop
    GoBack = InDate
End Function

 

[bionicman]

Ok, this worked for me...

Public Function GoBack(InDate As Date) As Date
    Do Until Weekday(InDate, vbMonday) = 1
        If InDate = [Forms]![Frm_Enter_Criteria]![Beg_Date] Then Exit Do
        InDate = DateAdd("d", -1, InDate)
    Loop
    GoBack = InDate
End Function

I see that we just had to change the IF statement to come first, that way it would exit the statement if it came across the date i entered.

as far as the following goes...

>= [Forms]![Frm_Enter_Criteria]![Beg_Date] And < DateAdd('m',1,[Forms]![Frm_Enter_Criteria]![Beg_Da
te])
NOT
Between [Forms]![Frm_Enter_Criteria]![Beg_Date] And DateAdd('m',1,[Forms]![Frm_Enter_Criteria]![Beg_Da
te])

are you asking me if i know what the difference is? or should this go in my report?

If you are asking me the difference, the answer is no. i only know what the between statement means...

 

[pdx_man]

The Between statement is inclusive, meaning that if we use
Between 09/01/2003 And 10/01/2003, it will give us results including 10/01/2003, which we don't want.
We get the 10/01/2003 by our DateAdd('m',1,09/01/2003) function.
So, using
>=09/01/2003 And <10/01/2003 will not include 10/01/2003

So, looks like this is it for this one, eh? Glad I could help.

 

[bionicman]

yes, thank you SO MUCH!!! You have been a great help, sorry if i was a pain

 

[pdx_man]

Nope, not a pain. I just want users of the site to learn why the solution works. It is discouraging when some users come here only looking for the answers to their problems, but have no idea why they even had a problem to begin with, or how the solution solved it. Sometimes we get students just looking for the answers to their homework ... I'm willing to help them, but only if they understand the solution.