Printing sequential week numbers

[drisconsult]

Hello All

I have written an accounts database for a friend. At the moment I have four reports showing:

1st Quarter
2nd Quarter
3rd Quarter
4th Quarter

These are based on Select queries where the criteria widow has the following code for the 1sr Quarter:

between "01" and "13"

This works fine of course. However, the user needs to be able to select two sequential week numbers out of the 52 weeks in any one year. What code is required for this procedure and where would this code be inserted. I am a relative beginner where VBA is concerned. So be gentle with me.

Thank you all for your help, which I know will come
Regards
Terence

 

[drisconsult]

Hello All
Made a small error on the "Between" statement. Should have read
Between 01 and 13
The weeknumber is of course a number.
Regards
Terence

 

[RuralGuy]

How about posting the SQL one of the queries you already have. Someone here will probably show you how to change it to get the results for which you are looking.

 

[drisconsult]

Printing two selected week numbers

Hello RuralGuy
I don't think the query is the problem. I have a form with two text boxes. First text box is called txtSTARTWEEK. The second text box is called txtENDWEEK.

There is a command button on the form that opens a report called rptPRINT TWO SELECTED WEEK NUMBERS. The code behind the button is:

Private Sub Command5_Click()
Dim stDocument As String
Dim strWhere As String
strWhere = "1 = 1 "
If Not IsNull(Me.txtSTARTWEEK) Then
strWhere = strWhere & " AND [WEEKNUMBER]>= " & _
Me.txtSTARTWEEK
End If
If Not IsNull(Me.txtENDWEEK) Then
strWhere = strWhere & " AND [WEEKNUMBER]<= " & _
Me.txtENDWEEK
End If
stDocument = "rptPRINT TWO SELECTED WEEK NUMBERS"
DoCmd.OpenReport stDocument, acPreview, , strWhere
End Sub

The problem is, it has previously worked perfectly. I then left the problem. When I returned to the database and tried the same procedure again, all I could get was all week numbers entered, not the two I had selected. Eventually there would be 52 week entries.

The SQL requested is:
SELECT tblMONEY.WEEKNUMBER, tblMONEY.WKENDING, tblMONEY.MONEYIN, tblMONEY.INTERNET, tblMONEY.PRINTING, tblMONEY.SUNDRIES, tblMONEY.DATEMONEYOUT, tblMONEY.TEXTMONEYOUT, tblMONEY.MONEYOUT, tblMONEY.DATEMONEYOWED, tblMONEY.TEXTMONEYOWED, tblMONEY.MONEYOWED
FROM tblMONEY
ORDER BY tblMONEY.WEEKNUMBER DESC;


Regards
Terence

 

[RuralGuy]

What version of Access are you using running on what OS?

 

[drisconsult]

Hello RuralGuy
Many thanks for getting back to me.
Access 2002 on Windows XP Media
Regards
Terence

 

[RuralGuy]

Have you tried an ascending sort?

 

[drisconsult]

Hello RuralGuy
Yes, I have tried every conceivable event. My problem is that it was working fine. I left the program for a couple of months, then returned to it last week and the problems began.
Regards
Terence

 

[RuralGuy]

It looks like it should work to me. Can you post enough of a sample so we can look at it?

 

[drisconsult]

Hello RuralGuy
Would you be able to download a PDF from the YouSendIt website? I can create a series of screenshots and put them together in Corel Ventura and then create the PDF with Adobe. As I can't find a method for sending screenshots in this forum, I would upload the PDF to YouSendIt with my email address, which you could then dowload. I will go ahead with the PDF just in case you can.
Regards
Terence

 

[RuralGuy]

You can upload to the site by going to advanced reply or press the Post Reply button and scroll down to "Manage Attachments". I'm not sure screen shots will do it but it is worth a try. You can upload bmp and jpeg's directly.

 

[drisconsult]

Hello RuralGuy
There are times when one's brain should go for an overhaul. And this is the time for mine. The error I made is in the first line of the VBA code:

Private Sub Command5_Click()
Dim stDocument As String
Dim strWhere As String
strWhere = "1 = 1 "
If Not IsNull(Me.txtSTARTWEEK) Then
strWhere = strWhere & " AND [WEEKNUMBER]>= " & _
Me.txtSTARTWEEK
End If
If Not IsNull(Me.txtENDWEEK) Then
strWhere = strWhere & " AND [WEEKNUMBER]<= " & _
Me.txtENDWEEK
End If
stDocument = "rptPRINT TWO SELECTED WEEK NUMBERS"
DoCmd.OpenReport stDocument, acPreview, , strWhere
End Sub

It should have read
Private Sub Command29_Click()

Command5 had already been allocated. Now it works fine. Sorry about the time I may have wasted for all concerned.

Regards
Terence

 

[gemma-the-husky]

you can use datediff to determine the week no of date you are checking - you can set the parameters to determine when week 1 starts (ie day of week, and datye in Jan

then you use between etc, to extract the week(s) you want

 

[RuralGuy]

There is so much more to learn when you solve your own problems. Thanks for posting back with your success. It would have taken us quite a while to guess that as a solution.