Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 2nd, 2003, 02:27 AM
Authorized User
 
Join Date: Jul 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select Query Code

Hi All,

I have a form based on select query. On the form is a backward and forward button to allow the user to move backward and forward through the data.

The data is presented by month and I have an incredibly cumbersome piece of code that doesn't work in all situations and I know there must be an easier way.

Below is a copy of that code for the forward button, which works via a month counter and then a select statement for that counter value. Really sucks doesn't it?

Please help.

Stephen

Dim cstrOldSQL As String
intNumber = intNumber + 1
'Selects which month ahead of Month Now
If intNumber = 1 Then
If Month(Now()) + intNumber < 12 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+1) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf Month(Now()) + intNumber > 0 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()+1) And Month([Show Date1])=Month(Now())+1) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
End If

ElseIf intNumber = 2 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+2) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 3 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+3) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 4 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+4) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 5 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+5) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 6 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+6) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 7 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+7) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 8 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+8) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 9 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+9) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 10 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+10) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 11 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+11) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber = 12 Then
cstrOldSQL = "SELECT Contract.Artist, Contract.Employer, Contract.Venue, Contract.[Show Date1], Contract.[Show Date2],Contract.[Show Date3], Contract.[Show Date4],Contract.Fee,Contract.Deposit,Contract.[Deposit Paid] FROM Contract WHERE ((Year([Show Date1])=Year(Now()) And Month([Show Date1])=Month(Now())+12) AND ((Contract.Cancelled) Is Null or (Contract.Cancelled)='0')) ORDER BY Contract.Artist,Contract.[Show Date1];"
ElseIf intNumber > 12 Then
MsgBox "No other Months Available"
End If
If intNumber < 12 Then
Me.RecordSource = cstrOldSQL
Me.MonthCounter = intNumber
msg = Me.MonthCounter
Me.Requery
End If
 
Old July 4th, 2003, 10:35 AM
Authorized User
 
Join Date: Jun 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wow...that is pretty cumbersome. For starters, all of your cstrOldSQL statements are the same with only one variation based on intNumber so this would be much easier to read and troubleshoot:

Code:
If intNumber < 12 then
cstrOldSQL = "SELECT Contract.Artist, " &_
                    "Contract.Employer, " &_
                    "Contract.Venue,  " &_
                    "Contract.[Show Date1], " &_
                    "Contract.[Show Date2], " &_
                    "Contract.[Show Date3],  " &_
                    "Contract.[Show Date4], " &_
                    "Contract.Fee, " &_
                    "Contract.Deposit,  " &_
                    "Contract.[Deposit Paid]  " &_
              "FROM Contract  " &_
              "WHERE ((Year([Show Date1])=Year(Now()) " &_
              "AND Month([Show Date1])=Month(Now())+" & intNumber & ") " &_
              "AND ((Contract.Cancelled) Is Null  " &_
              "OR contract.Cancelled)='0')) " &_
              "ORDER BY Contract.Artist, " &_
                       "Contract.[Show Date1];"
Me.RecordSource = cstrOldSQL
Me.MonthCounter = intNumber
msg=Me.MonthCounter
Me.Requery
Else: MsgBox "No other Months Avaliable"
End If
If you notice, the only change to your SQL variable is in the WHERE section. This is an easier way of defining your SQL.

You also mentioned that it doesn't work in all situations; which situations would that be?

-Shay Shepston
 
Old July 6th, 2003, 11:31 PM
Authorized User
 
Join Date: Jul 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Shay,

Thanks for your help it has solved my month problem and also removed all that code. I just didn't know how to put a variable into a select statement. Many Tbanks.

I now am trying to adjust the year, once the code gets past monthcounter 12 and it will not work, just keeps in 2003. Could you have a look a let me know if there is a way to do this.

Again many thanks for you input.

Here is my code...

'This routine counts months forward
Dim cstrOldSQL As String
intMonthCounter = intMonthCounter + 1
If Month(Now()) + intMonthCounter < 13 Then
cstrOldSQL = "SELECT Contract.Artist, " & _
                    "Contract.Employer, " & _
                    "Contract.Venue, " & _
                    "Contract.[Show Date1], " & _
                    "Contract.[Show Date2], " & _
                    "Contract.[Show Date3], " & _
                    "Contract.[Show Date4], " & _
                    "Contract.Fee, " & _
                    "Contract.Deposit, " & _
                    "Contract.[Deposit Paid] " & _
              "FROM Contract " & _
              "WHERE ((Year([Show Date1])=Year(Now()) " & _
              "AND Month([Show Date1])=Month(Now())+" & intMonthCounter & ") " & _
              "AND ((Contract.Cancelled) Is Null " & _
              "OR (Contract.Cancelled)='0')) " & _
              "ORDER BY Contract.Artist, " & _
                    "Contract.[Show Date1];"
Me.RecordSource = cstrOldSQL
Me.MonthCounter = intNumber
msg = Me.MonthCounter
Me.Requery

'This routine counts months and years forward
ElseIf Month(Now()) + intMonthCounter = 13 Then
intYearCounter = intYearCounter + 1
intMonthCounter = 1 - Month(Now())
cstrOldSQL = "SELECT Contract.Artist, " & _
                    "Contract.Employer, " & _
                    "Contract.Venue, " & _
                    "Contract.[Show Date1], " & _
                    "Contract.[Show Date2], " & _
                    "Contract.[Show Date3], " & _
                    "Contract.[Show Date4], " & _
                    "Contract.Fee, " & _
                    "Contract.Deposit, " & _
                    "Contract.[Deposit Paid] " & _
              "FROM Contract " & _
              "WHERE (((Year([Show Date1])=Year(Now())+" & intYearCounter & ")" & _
              "AND Month([Show Date1])=Month(Now())+" & intMonthCounter & ") " & _
              "AND ((Contract.Cancelled) Is Null " & _
              "OR (Contract.Cancelled)='0')) " & _
              "ORDER BY Contract.Artist, " & _
                    "Contract.[Show Date1];"
Me.RecordSource = cstrOldSQL
Me.MonthCounter = intYearCounter
msg = Me.MonthCounter
Me.Requery
'MsgBox "No other Months Avaliable"
End If

End Sub


 
Old July 7th, 2003, 05:19 PM
Authorized User
 
Join Date: Jun 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In ElseIf section, you have:

Code:
Me.MonthCounter=intYearCounter
Was this by design or was it intended to be:

Code:
Me.MonthCounter=intMonthCounter
If that is not the problem, let me know what it is doing wrong. Do you get an Error? Do you get the incorrect Month? Does it do nothing?

-Shay Shepston
 
Old July 9th, 2003, 01:14 AM
Authorized User
 
Join Date: Jul 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Shay,

Once again thank, yes that elseif was a typo.

I now have code running nicely and moves forwards and backwards through months and years.

Thanks a great deal.

Regards
Stephen






Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Query gregalb SQL Server 2000 2 November 15th, 2007 10:56 PM
SELECT from SELECT query? seananderson Access 1 October 12th, 2007 12:40 AM
Need help on select query arul1984 SQL Server 2000 2 July 4th, 2007 01:49 AM
Select query help minhtri SQL Server 2000 4 March 28th, 2005 06:59 PM
select query collie SQL Server 2000 2 January 17th, 2005 03:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.