Need to show random week numbers in a query 1

[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 random 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

 

[dhookom]

Are the desired weeks contiguous or not? Are you storing a date in your table or the week number or both? Are you concerned about year in addition to week?

If the reports are basically the same other than a different subset of records then you only need one report, not four. You just specify a range of dates, weeks, months, quarters, or whatever.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Drisconsult]

Hello dhookom
Yes, the weeks are in ascending alphabetical order.

Both dates and weeks are stored in the tblMONEY

Years do not matter.

I agree with you on the need for one report, but the reason for this request is that my VBA is simply not up to the task. However, it was at the request of the owner that I develop four Reports.

Included is a screenshot of the 3rd Quarter.

Many thanks for your help
Terence

 

[dhookom]

I try not to comprise good development practices. If you want to run the same report but for different date ranges then provide the user with a form that allows the entry of beginning and endding dates. The report is then filtered to this date range. I don't waste my clients' time by creating and maintaining extra reports, queries, etc.

When I asked about "contiguous", I meant the reported weeks had no gaps. Specifically does the user want to be able to report weeks 05, 08, 09, and 22 in one report. Or are the weeks in the report always sequential like 05-15?

"alphabetical order" suggests you are storing the extra week value in a text field. If you have the date value then you don't need the week and if you can't work without it, the week number should be numeric.

What date and/or week fields are in the reports field list and what are their data types?

What is the name of the report?

Are you opening the report from a form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Drisconsult]

Hello dhookom
Many thanks for spending time on my problem.

Field Name Data Type
WKNUMBER Numeric
WKENDING Date/Time

The weeks must always be sequential
There must be no gaps in week numbers
The week numbers are descending
The date is not important
the name of the Report is rptPRINT SELECTED WEEK NUMBERS
All Forms and Reports are opened from a Menu.

Question:
Is it possible to send screenshots with these threads?

 

[dhookom]

I would add two text boxes to your form
Name: txtStartWeek

Name: txtEndWeek

Then use the command button wizard to add a button to your form that opens the report. This will write some code for you that you should edit to read something like:

Dim stDocument as String
Dim strWhere as String
strWhere = "1 = 1 "
If not IsNull(Me.txtStartWeek) Then
    strWhere = strWhere & " AND [WKNUMBER]>= " & _
        Me.txtStartWeek 
End If
If not IsNull(Me.txtEndWeek) Then
    strWhere = strWhere & " AND [WKNUMBER]<= " & _
        Me.txtEndWeek 
End If
stDocument ="rptPRINT SELECTED WEEK NUMBERS"
DoCmd.OpenReport stDocument, acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Drisconsult]

Hello dhookdom

Can't thank you enough for your efforts on my behalf.

I am having problems with your solution. First my Print Menu does the following:

There are eight command buttons all running macros that launch each Report (If I could send jpegs with these messages I could send you screenshots of the forms in question). I will try and simulate the Print Menu. The letter C indicates a small command button about 5mm square.

MJDs Print Menu

C Print All Data
C Print Selected Week Number
C Print Financial Review
C Print 1st Quarter
C Print 2nd Quarter
C Print 3rd Quarter
C Print 4th Quarter
C Return to Main Menu

When I click on the command button for Print 1st Quarter, the Report Preview shows the printout in landscape format.

There is no form available for me to insert the two Textboxes you refer to.

When I click on "Print Selected Week Number". A Parameter Value dialog box appears in which I enter the required week number. All these programs work.

Sincere Regards
Terence

 

[dhookom]

Add to your menu an option to
C Print Any Week Range
Have this button open a new form that you created with the text boxs for entering the start and end weeks.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Drisconsult]

Hello dhookom

Again many thanks for taking the time to try an educate a thickie like myself.

The problem is that there are no forms involved in the printing week selection procedure. I have a query

qrySELECT WEEK NUMBER

that allows the user to enter the week number in the Criteria window of the query with the following text:

Criteria: like [Please Enter Week Number]

In the queries that print the four quarters, the text for the first quarter is as follows:

Criteria: between 01 and 13

Hope this helps.

Sincere regards
Terence

 

[dhookom]

I'm suggesting you to create a form that is opened by the menu option as I described. Find the record source for your report and remove the criteria since the code will apply a filter.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Drisconsult]

Hello dhookdom

Will not waste any more of your time here.

After removing the Criteria and applying your code under the command button, I received an error on the last line before End Sub:

Private Sub Command4_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 SELECTED WEEK NUMBERS"
DoCmd.OpenReport stDocument, acPreview, , strWhere
(This line was highlighted in yellow)
End Sub

 

[PHV]

I received an error
Which error message ?
What is the value of strWhere when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Drisconsult]

Hello dhookom

Entirely my fault. I had a data type mismatch regarding the week numbers and all is well now. Can't thank you enough for your patience with an old fart like myself.

Unfortunately for you, I will be back with more problems because I now know where to get the answers.

Regards
Terence
London

 

[Drisconsult]

Hello dhookom

In June 2007 you solved a problem for me. This was selecting two week numbers and printing the report. I have just returned to this program and would you believe it, it simply lists all the weeks rather than week 2 and 4 that I selected.

The Properties dialog box of the Report shows the following filter, see attachment. If you cannot see the attachment. There is a line of code in the filter statement that reads:

(1 = 1 AND [WEEKNUMBER]>= 3 AND [WEEKNUMBER]<= 6)

I had entered week 2 and week 4. But all eight weeks of the report were shown.

Kind Regards
Terence
London

 

[dhookom]

You image isn't available since we don't have your hard drive.

What is your code that opens the report?

Duane MS Access MVP

 

[Drisconsult]

Hello dhookdom

Many thanks for replying so swiftly. What is so strange is that it was working five months ago. I followed the VBA code that you gave me and it worked perfectly, together with the txtStartweek and txtEndweek Text boxes.

Step 2 states that attachments can be sent, how do I do this? It will certainly help you to understand my problem.

Regards
Terence

 

[dhookom]

I expect you need to upload your file to a web page that will serve it up to whoever wants to view it.

Duane MS Access MVP

 

[Drisconsult]

Hello dhookom
Many thanks for your reply.
I'm amazed that screenshots cannot be uploaded with emails.

What I have done so far is:

1. Create a form with two text fields: txtSTARTWEEK and txtENDWEEK.

2. The WEEKNUMBER field in my database is a TEXT field.

3. I then created on the form, a command button that Reviews and Prints the Report. I made sure that the code you supplied sits behind the button.

This has worked beautifully. I then left the program until now. I then entered Weeks 2 to 7 and the result shows all entries for weeks entered so far.

I am at a loss.
Regards
Terence
London

 

[dhookom]

Why is WEEKNUMBER not a number? If you want to query between two other numbers, you have to convert the text to a number and then use the between.

What have you done in your report or code that applies the "between..."

Duane MS Access MVP

 

[Drisconsult]

Hello dhookdom

Sorry that was an error where I had been experimenting. WEEKNUMBER is of course a number. May apologies for this error
Regards
Terence