Incrementing textbox to assign value

[cbende2]

Hello all,

I have 52 unbound text boxes on a form.

I'm trying to create a For loop that will assign each textbox a value, here's my code:

Private Sub Form_Load()

Dim O(6, 52) As Integer
Dim i As Integer

For i = 0 To 51

    O(1, i + 1) = DCount("Field1", "Table1", "DateValue([Field1]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,1,8)")
    Me.Text(i + 1).Value = O(1, i + 1)
    
Next i

End Sub

After I get this figured out, I'll be trying to figure out how I can increment the DateSerial by the 52 weeks of the year.

So basically each textbox(1-52) will have a count for each week of the year.

Hopefully this makes since, but that is for a future post, right now I'm just trying to figure out if I can increment a textbox to give it a value.


Thank you!

[CJ_London]

assuming your controls are called text1, text2 etc then try this

For i = 0 To 51

    Me("Text" & i+1) =  DCount("Field1", "Table1", "DateValue([Field1]) Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,1,8)")
    
Next i

I have no idea what the dcount is supposed to do - looks like it will fill all your boxes with the same value between Jan 1st and Jan 8th of last year

[cbende2]

Yep, that worked. Thanks Ajax,

And I have text1-text52.

I'm trying to figure out a way to increment the weeks now. So each textbox is assigned 1 week of the year.

[cbende2]

So text1 will be Jan1-Jan8
text2 will be Jan8-Jan15
text3 will be jan15-Jan21
and so on...

[CJ_London]

with regards your 'count by week', be aware there can be 53 weeks in the year.

[CJ_London]

So text1 will be Jan1-Jan8
text2 will be Jan8-Jan15
text3 will be jan15-Jan21

Are you sure - so you want to add values on Jan8 to both week 1 and week 2?

[cbende2]

Are you sure - so you want to add values on Jan8 to both week 1 and week 2?

I guess not, but you get what I'm saying

[CJ_London]

an imprecise answer gets an imprecise suggestion. Look at using the dateadd function to determine the two between dates. I question you needing the datevalue function if field1 is a datetime field. You can also look at the format function to determine a week number

good luck with your project

[cbende2]

My field is a Short text field.

I'll see what i can do.

Thanks Ajax