Use Input Box Value as Creteria

[Ragah21]

I have the follwoing code: and I would like the month that is passed by the
input box be used in the second function as creteria.

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the number of month you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Month in MM format: Enter '0' for All",
"Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where [Submit Date] LIKE '" & strPrompt & "*' " & ""
Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If

End Sub

Public Function Check_Records() As Boolean

If DCount("*", "Step1_Member_Status", HERE) > 0 Then
Check_Records = True
End If

End Function

 

[CautionMP]

Ragah21,
If [tt][Submit Date][/tt] is a date why not use date functions instead of text functions?

         strSQL = "select * from Requests " & _
        "where [b]Month([/b][Submit Date][b])=[/b]" & strPrompt & [b]";"[/b]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Ragah21]

That worked fine Thanks, but still didnt solve my problem: I want the Which_Month sub vlaue from the input box to be part of my CRI

Below is my code:

Private Sub Command11_Click()
Dim LBx As ListBox, criName As String, criStatus As String, Cri As String, DQ As String, itm
DQ = """"

Set LBx = Me!List2
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criName <> "" Then
criName = criName & ", " & DQ & LBx.Column(1, itm) & DQ
Else
criName = DQ & LBx.Column(1, itm) & DQ
End If
Next
criName = "[Assigned Team Member] In(" & criName & ")"
Debug.Print criName

Else '=0 nothing in listbox
MsgBox "Please select an Analyst", vbCritical
Exit Sub

End If


Set LBx = Me!List4
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criStatus <> "" Then
criStatus = criStatus & ", " & DQ & LBx.Column(0, itm) & DQ
Else
criStatus = DQ & LBx.Column(0, itm) & DQ
End If
Next
criStatus = "[Status] In(" & criStatus & ")"
Debug.Print criStatus

Else '=0 nothing in listbox
MsgBox "Please select one or more Status", vbCritical
Exit Sub
End If

Call Which_Month ( the vlaue of this function which is the date, need to be added to my creteria )

If Check_Records Then

Cri = criName & IIf(criName > "", " and ", "") & criStatus
DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing

Else
MsgBox "There are no records to view", vbOK
End If
End Sub

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of month you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Month in MM format: Enter '0' for All", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where Month([Submit Date])=" & strPrompt & ";"
Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub

Public Function Check_Records() As Boolean

If DCount("*", "Step1_Member_Status") > 0 Then
Check_Records = True
End If

End Function

 

[CautionMP]

Ragah21,
So you want to use the value captured using the input box in [tt]Which_Month()[/tt] later in your code for [tt]Command11_Click[/tt]?

Make the [tt]Sub[/tt] a [tt]Function[/tt] and return the month from the function:

Public [b]Function[/b] Which_Month() [b]As Variant[/b]
...
   If Len(strPrompt) > 0 Then
      If IsNumeric(strPrompt) Then
         [b]Which_Month = strPrompt[/b]
         strSQL = "select * from Requests " & _
        "where Month([Submit Date])=" & strPrompt & ";"
...
End [b]Function[/b]

or pass an argument to the sub and use it to return the value:

Public Sub Which_Month([b]Return_Value[/b] as Variant)
...
   If Len(strPrompt) > 0 Then
      If IsNumeric(strPrompt) Then
         [b]Return_Value = strPrompt[/b]
         strSQL = "select * from Requests " & _
        "where Month([Submit Date])=" & strPrompt & ";"
...
End [b]Function[/b]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Ragah21]

Thanks, but it not working.

Ok, All I need is the code the modify my query to include the
Cri = criName & IIf(criName > "", " and ", "") & criStatus
in the where condition for both date and Cri to be true.

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of month you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Month in MM format: Enter '0' for All", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where Month([Submit Date])=" & strPrompt & ";"
Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub