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

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:

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

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

In ElseIf section, you have:

Was this by design or was it intended to be:

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

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