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]

If you are still experiencing issues then you should answer my question [blue]What have you done in your report or code that applies the "between..." [/blue]

Duane MS Access MVP

 

[Drisconsult]

Hello dhookdom

Many thanks for your eply.

There is no code with the word "Between" in the Report that provides the two sequentially randon numbers. This code is in four queries that I was using before I was able to get a solution to the randon number problem. The code I am using for this problem is the VBA code that you provided me with, which worked and then suddenly doesn't: very strange indeed.

However in the query qrySELECT 1ST QUARTER, the code in the Criteria window is:

Between 1 and 13

Regards
Terence

 

[dhookom]

I don't understand what you mean by "two sequentially randon numbers". "sequentially" means in numbered order like 1 to 2 or 5 to 6. 4 to 7 would be non-sequential. By "randon" I assume you mean "random" but this would mean any number. Perhaps you mean "dynamic" which would be "changing" as entered by a user while "static" would be non-changing numbers like "Between 1 and 13". The 1 and 13 aren't dynamic.

Do you have any code that opens the report? Can you share the code with us?

Duane MS Access MVP

 

[Drisconsult]

Hello dhookdom

You should forget the the Between Numbers queries, it has nothing whatsoever to do with my problem. Sequentially randon numbers means that the user can select any two sequential numbers from 1 to 52, such as 4 and 23.

I feel that I am using up too much of your valuable time here. The code that opens up the report is YOURS, you sent it to me and it can be seen above. Here is is:

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

Regards
Terence

 

[PHV]

(This line was highlighted in yellow)
Which error message ?

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

 

[skyline666]

Hi Terence,

In response to your queries on how to upload files on here, go to this site

http://www.mediafire.com/

. Simply click the browse button to find where your file is (if you have more then one file, choose a number before this, although I have ever done one at a time), click "upload file..." and wait until it has finished. Then you will presented with a page where you can copy a link to where the file is stored.

At this moment you will be an anonymous user. I would recommend creating an account with them, as this will then store all the uploaded files in your account, so you have easy access to the link of where they are stored. Creating an account is free, and if you werent registered with them, and you loose/forget what the link is, you would have to upload the file again, so its easier to be a member!

Hope this helps,

Andrew

 

[dhookom]

Is [WEEKNUMBER] a field in your report's record source?
Do you have a report named "rptPRINT SELECTED WEEK NUMBERS"?
Can you open the report from the database window?
What is the Record Source SQL view of the Report?

Duane MS Access MVP

 

[Drisconsult]

Many thanks to Skyline for your help.

Hello dhookdom
Yes I have a report named rptPRINT TWO SELECTED WEEK NUMBERS.

The report is opened with a macro on a menu.

I hope you realise that YOU HAVE ALREADY PROVIDED A SOLUTION TO THIS PROBLEM. However retuning to the database after an absence, the report mentioned above produces all weeks antered to date, not selected week numbers.

I think it's time to end this dialogue. I have taken up far too much of everybody's time. So I would like to thank you all for your efforts and this down to experience.

Regards
Terence

 

[dhookom]

You stated:
"The report is opened with a macro on a menu."
But I thought the report was opened from "Private Sub Command4_Click()?"

If the report is already open, the code is worthless.


Duane MS Access MVP

 

[Drisconsult]

Hello dhookdom

Many thanks for all your efforts on my behalf. Unfortunately we are having a communication problem and I do not wish to waste any more of your time.

What is utterly baffling to me is that you have already solved this problem. So as far as I am concerned this matter is now closed.

Regards
Terence

 

[Drisconsult]

Hello dhookdom
I owe you an apology. The reason why your procedure stopped working was because my nephew, in his wisdom, had decided to play around with your code while I was away. This is the reason why your procedure, which worked fine before I left the country, did not when I returned.

For some inexplicable reason he had deleted the first and last lines of the code which you had sent to me. This is what I should have had:

Private Sub Command34_Click()
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 TWO SELECTED WEEK NUMBERS"
DoCmd.OpenReport stDocument, acPreview, , strWhere
End Sub

My nephew had deleted:
Private Sub Command34_Click()
and
End Sub

Which I had to admit I did not notice. When I did notice it and inserted the missing two lines of code, everything worked perfectly.

I do apologise for not noticeing this before I started sending you streams of notes.
Regards
Terence