UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query By Form, Access 2016    
 
   
mike60smart
post Jan 16 2020, 08:57 AM
Post#1


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have a number of Unbound Combobox's which allows the user to select a Start Month, An End Month and a Year

I have a command button to run the following OnClick Event:-

CODE
Private Sub Command7_Click()
10    On Error GoTo Command7_Click_Err
      Dim strCrit As String
20    If Me.txtStartMonth > "" Then
30      strCrit = strCrit & "([Months] = " & Chr(34) & Me.txtStartMonth & Chr(34) & ") AND "
40    End If
50    If Me.txtEndMonth > "" Then
60      strCrit = strCrit & "([Months] = " & Chr(34) & Me.txtEndMonth & Chr(34) & ") AND "
70    End If
80    If Me.txtYear > "" Then
90      strCrit = strCrit & "([DueYear] = " & Chr(34) & Me.txtYear & Chr(34) & ") AND "
100   End If
110   If strCrit > "" Then
120     strCrit = Left(strCrit, Len(strCrit) - 5)
        
130   DoCmd.OpenReport "rptFleetMaintenance", acPreview, , strCrit

140   End If

Command7_Click_Exit:
150       Exit Sub

Command7_Click_Err:
160       MsgBox Error$
170       Resume Command7_Click_Exit

End Sub


It works fine if I just select 1 specific Month but as soon as I select a Month Range ie Jan - Apr it returns no records

How can this method be modified to allow for a range of Months?

Any Help appreciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 09:07 AM
Post#2


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

Is [Months] a number (ie 1 - 12), or text?

--------------------


Regards,

David Marten
Go to the top of the page
 
Larry Larsen
post Jan 16 2020, 09:23 AM
Post#3


UA Editor + Utterly Certified
Posts: 24,410
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

What would Debug.Print strCrit look like..??
thumbup.gif
Attached File(s)
Attached File  2020_01_16_14_19_41.jpg ( 21.35K )Number of downloads: 4
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
mike60smart
post Jan 16 2020, 10:08 AM
Post#4


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

It is text


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Jan 16 2020, 10:13 AM
Post#5


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry

If I just select a specific Year then it looks like this:-

([DueYear] = 2020) AND


If I select a range of Months Plus a Year it looks like this:-

([Months] = "Jan") AND ([Months] = "Apr") AND ([DueYear] = 2020) AND

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 10:14 AM
Post#6


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


How do you intend Access to know which months occur between, say, February and June?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 10:37 AM
Post#7


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


CODE
Private Sub Command7_Click()
On Error GoTo Command7_Click_Err

  Dim MonthsInYear As Variant, iMonth As Integer, iFrom As Integer, iTo As Integer
  Dim strCrit As String

  With Me
    If Len(.txtStartMonth & vbNullString) And Len(.txtEndMonth & vbNullString) Then
      MonthsInYear = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Dec")
      iFrom = -1
      iTo = -1
      For i = 0 To 11
        If .txtStartMonth = MonthsInYear(i) Then iFrom  = i
        If .txtEndMonth = MonthsInYear(i) Then iFrom  = i
        If iFrom >= 0 And  iTo >= 0 Then Exit For
      Next i
      If iFrom > iTo Then
        i = iFrom
        iFrom = iTo
        iTo = i
      End If
      For i = iFrom To iTo
        strCrit = strCrit & " OR [Months] = " & Chr(34) & MonthsInYear(i) & Chr(34)
      Next i
      strCrit = "(" & Mid(strCrit, 5) & ")"
    Else
      If Len(.txtStartMonth & vbNullString) Then
        strCrit = "[Months] = " & Chr(34) & .txtStartMonth & Chr(34)
      End If
      If Len(.txtEndMonth & vbNullString) Then
        strCrit = "[Months] = " & Chr(34) & .txtEndMonth & Chr(34)
      End If
    End If
    If Len(.txtYear & vbNullString) Then
      strCrit = IIf(Len(strCrit), strCrit & " AND ", vbNullString) & "[DueYear] = " & Chr(34) & .txtYear & Chr(34)
    End If
  End With

  If Len(strCrit) Then
    DoCmd.OpenReport "rptFleetMaintenance", acPreview, , strCrit
  End If

Command7_Click_Exit:
  Exit Sub

Command7_Click_Err:
  MsgBox Error$
  Resume Command7_Click_Exit

End Sub

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 16 2020, 11:15 AM
Post#8


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

When I select a Month Range and a Year I get the following error:-

Attached File  error.PNG ( 3.26K )Number of downloads: 1



Edit** I added Nov into the Array for Months and now it errors on line 180??

Code is now this:-

CODE
Private Sub cmdInspetionsDue_Click()

10        On Error GoTo cmdInspetionsDue_Click_Error
    
      Dim MonthsInYear As Variant, iMonth As Integer, iFrom As Integer, iTo As Integer
        Dim strCrit As String
        Dim i As Long
20      With Me
30        If Len(.txtStartMonth & vbNullString) And Len(.txtEndMonth & vbNullString) Then
40    MonthsInYear = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Dec")
50    iFrom = -1
60    iTo = -1
70    For i = 0 To 11
80      If .txtStartMonth = MonthsInYear(i) Then iFrom = i
90      If .txtEndMonth = MonthsInYear(i) Then iFrom = i
100     If iFrom >= 0 And iTo >= 0 Then Exit For
110   Next i
120   If iFrom > iTo Then
130     i = iFrom
140     iFrom = iTo
150     iTo = i
160   End If
170   For i = iFrom To iTo
180     strCrit = strCrit & " OR [Months] = " & Chr(34) & MonthsInYear(i) & Chr(34)
190   Next i
200   strCrit = "(" & Mid(strCrit, 5) & ")"
210       Else
220   If Len(.txtStartMonth & vbNullString) Then
230     strCrit = "[Months] = " & Chr(34) & .txtStartMonth & Chr(34)
240   End If
250   If Len(.txtEndMonth & vbNullString) Then
260     strCrit = "[Months] = " & Chr(34) & .txtEndMonth & Chr(34)
270   End If
280       End If
290       If Len(.txtYear & vbNullString) Then
300   strCrit = IIf(Len(strCrit), strCrit & " AND ", vbNullString) & "[DueYear] = " & Chr(34) & .txtYear & Chr(34)
310       End If
320     End With

330     If Len(strCrit) Then
340       DoCmd.OpenReport "rptInspectionsDue", acPreview
350     End If

          
360       On Error GoTo 0
370       Exit Sub

cmdInspetionsDue_Click_Error:

380       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInspetionsDue_Click, line " & Erl & "."

End Sub

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 11:18 AM
Post#9


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


Probably because I don't know my calendar!!!!

I missed out November! doh.gif

Change line 40 to:
CODE
' ...
40    MonthsInYear = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
' ...

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 16 2020, 12:05 PM
Post#10


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

Changed Line 40 and now get this error:-

Attached File  error.PNG ( 3.21K )Number of downloads: 0


Code is now:-

CODE
Private Sub cmdInspetionsDue_Click()

10        On Error GoTo cmdInspetionsDue_Click_Error
    
      Dim MonthsInYear As Variant, iMonth As Integer, iFrom As Integer, iTo As Integer
        Dim strCrit As String
        Dim i As Long
20      With Me
30        If Len(.txtStartMonth & vbNullString) And Len(.txtEndMonth & vbNullString) Then
40    MonthsInYear = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
50    iFrom = -1
60    iTo = -1
70    For i = 0 To 11
80      If .txtStartMonth = MonthsInYear(i) Then iFrom = i
90      If .txtEndMonth = MonthsInYear(i) Then iFrom = i
100     If iFrom >= 0 And iTo >= 0 Then Exit For
110   Next i
120   If iFrom > iTo Then
130     i = iFrom
140     iFrom = iTo
150     iTo = i
160   End If
170   For i = iFrom To iTo
180     strCrit = strCrit & " OR [Months] = " & Chr(34) & MonthsInYear(i) & Chr(34)
190   Next i
200   strCrit = "(" & Mid(strCrit, 5) & ")"
210       Else
220   If Len(.txtStartMonth & vbNullString) Then
230     strCrit = "[Months] = " & Chr(34) & .txtStartMonth & Chr(34)
240   End If
250   If Len(.txtEndMonth & vbNullString) Then
260     strCrit = "[Months] = " & Chr(34) & .txtEndMonth & Chr(34)
270   End If
280       End If
290       If Len(.txtYear & vbNullString) Then
300   strCrit = IIf(Len(strCrit), strCrit & " AND ", vbNullString) & "[DueYear] = " & Chr(34) & .txtYear & Chr(34)
310       End If
320     End With

330     If Len(strCrit) Then
340       DoCmd.OpenReport "rptInspectionsDue", acPreview
350     End If

          
360       On Error GoTo 0
370       Exit Sub

cmdInspetionsDue_Click_Error:

380       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInspetionsDue_Click, line " & Erl & "."

End Sub

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 02:11 PM
Post#11


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


Apologies, Mike!

It might have helped if I had tested this first!

Change line 90 to:
CODE
' ...
90      If .txtEndMonth = MonthsInYear(i) Then iTo = i
' ...


blush.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 18 2020, 04:19 AM
Post#12


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

When I run the report for the period Jan to Dec 2021 I get the following

Attached File  report.PNG ( 9.65K )Number of downloads: 0


It does the same when I choose either 2022, 2023, 2024 ???

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 18 2020, 06:18 AM
Post#13


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


Good morning, Mike,

Somewhere along the way you lost the the criteria when opening the report! Change:
CODE
' ...
330     If Len(strCrit) Then
340       DoCmd.OpenReport "rptInspectionsDue", acPreview
350     End If
' ...

to
CODE
' ...
330     If Len(strCrit) Then
335       Debug.Print strCrit
340       DoCmd.OpenReport "rptFleetMaintenance", acPreview, , strCrit
350     End If
' ...

(I added the Debug.Print line just in case any future debugging is required)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 18 2020, 07:43 AM
Post#14


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

Now that is what I call a work of Art.

Works perfectly.

Many many thanks yet again

cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 18 2020, 08:26 AM
Post#15


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


Great! I'm glad we got there in the end! thumbup.gif

Had your months been stored numerically it would have been much easier - you could have constructed a simple BETWEEN criteria.

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 10:12 PM