Listbox with selection of start and end date

A

Anne

Below I have the code for 2 print selections. In the first one, the user
selects on Employee from a combo box. I would like to replace combox
selection with a selections form a list box, see 2nd sample.
Any help would be appreciated
Anne

Private Sub Print1_Click()
Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [EmployeeID] = " & Me!cboMoveTo
End If

If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmplCboDates", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
End Sub
--------------
Private Sub cmdPrint_Click()
Dim varSelected As Variant
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeID] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
DoCmd.OpenReport "rptEntry", acViewNormal, , strSQL
End If
End Sub
 
A

Anne

When I try to combine the listbox with the date boxes which I have as shown
below, it selects the date range, but not the employees from the list box,
it shows all employees, so I am missing something somewhere. In the original
list box SQL the last line opens the report with reference to strSQL. I
think that is missing somewhere, but I don't know where to put what to
retain the selection from the list box.
Anne

Private Sub CmdPrintEmplData_Click()
Dim varSelected As Variant
Dim strWhere As String
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmpl", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
End Sub



Wayne Morgan said:
What problems are you running into?

--
Wayne Morgan
MS Access MVP


Anne said:
Below I have the code for 2 print selections. In the first one, the user
selects on Employee from a combo box. I would like to replace combox
selection with a selections form a list box, see 2nd sample.
Any help would be appreciated
Anne

Private Sub Print1_Click()
Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [EmployeeID] = " & Me!cboMoveTo
End If

If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmplCboDates", acViewPreview, ,
strWhere
DoCmd.RunCommand acCmdZoom75
End Sub
--------------
Private Sub cmdPrint_Click()
Dim varSelected As Variant
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeID] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
DoCmd.OpenReport "rptEntry", acViewNormal, , strSQL
End If
End Sub
Click to expand...
Click to expand...
 
W

Wayne Morgan

1) Try an extra set of parenthesis enclosing the entire IN statement. They
can be there all the time without hurting anything, so you can leave them
there even if no date range has been selected. This probably won't matter, I
think the second problem is the main one.
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
Click to expand...
strSQL = "([EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & "))"

2)
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
Click to expand...

You change from strSQL to strWhere and never tie the two of them together.
Also, you are leaving the "True" concatenated in all of the time.

--
Wayne Morgan
MS Access MVP


Anne said:
When I try to combine the listbox with the date boxes which I have as
shown below, it selects the date range, but not the employees from the
list box, it shows all employees, so I am missing something somewhere. In
the original list box SQL the last line opens the report with reference to
strSQL. I think that is missing somewhere, but I don't know where to put
what to retain the selection from the list box.
Anne

Private Sub CmdPrintEmplData_Click()
Dim varSelected As Variant
Dim strWhere As String
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmpl", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
End Sub
Click to expand...
 
A

Anne

With those changes I got the employee selected but with all data. If the
individual codes work by themselves, is there way you can just tie the two
codes, like, select the list box code and instead to go to print report, go
to the date selection, select the corresponding dates and then print report?
Anne

Wayne Morgan said:
1) Try an extra set of parenthesis enclosing the entire IN statement. They
can be there all the time without hurting anything, so you can leave them
there even if no date range has been selected. This probably won't matter,
I think the second problem is the main one.
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
Click to expand...
strSQL = "([EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & "))"

2)
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
Click to expand...

You change from strSQL to strWhere and never tie the two of them together.
Also, you are leaving the "True" concatenated in all of the time.

--
Wayne Morgan
MS Access MVP


Anne said:
When I try to combine the listbox with the date boxes which I have as
shown below, it selects the date range, but not the employees from the
list box, it shows all employees, so I am missing something somewhere. In
the original list box SQL the last line opens the report with reference
to strSQL. I think that is missing somewhere, but I don't know where to
put what to retain the selection from the list box.
Anne

Private Sub CmdPrintEmplData_Click()
Dim varSelected As Variant
Dim strWhere As String
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmpl", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom75
End Sub
Click to expand...
Click to expand...
 
W

Wayne Morgan

Yes, the two can be tied together. Taking your previous post, try these
changes.

Private Sub CmdPrintEmplData_Click()
Dim varSelected As Variant
'Dim strWhere As String
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

'strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
If strSQL <> "" Then
strSQL = strSQL & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
Else
strSQL = "[WeDate] >=#" & _
CDate(Me!txtDateFrom) & "#"
End If
End If
If Not IsNull(Me!txtDateTo) Then
If strSQL <> "" Then
strSQL = strSQL & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
Else
strSQL = "[WeDate]<=#" & _
CDate(Me!txtDateTo) & "#"
End If
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmpl", acViewPreview, , strSQL
DoCmd.RunCommand acCmdZoom75
End Sub
 
A

Anne

That did it, it is working
Thank you so much. Need to study this a little so I can re-use the changes
you made.
Annelie

Wayne Morgan said:
Yes, the two can be tied together. Taking your previous post, try these
changes.

Private Sub CmdPrintEmplData_Click()
Dim varSelected As Variant
'Dim strWhere As String
Dim strSQL As String
For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = strSQL & Me!lstSelect.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
strSQL = "[EmployeeId] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"
End If

'strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!txtDateFrom) Then
If strSQL <> "" Then
strSQL = strSQL & " AND [WeDate] >= #" & _
CDate(Me!txtDateFrom) & "#"
Else
strSQL = "[WeDate] >=#" & _
CDate(Me!txtDateFrom) & "#"
End If
End If
If Not IsNull(Me!txtDateTo) Then
If strSQL <> "" Then
strSQL = strSQL & " AND [WeDate] <= #" & _
CDate(Me!txtDateTo) & "#"
Else
strSQL = "[WeDate]<=#" & _
CDate(Me!txtDateTo) & "#"
End If
End If
DoCmd.OpenReport "5AnyWeekDetailbyEmpl", acViewPreview, , strSQL
DoCmd.RunCommand acCmdZoom75
End Sub


--
Wayne Morgan
MS Access MVP


Anne said:
With those changes I got the employee selected but with all data. If the
individual codes work by themselves, is there way you can just tie the
two codes, like, select the list box code and instead to go to print
report, go to the date selection, select the corresponding dates and then
print report?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List box selection with date selection 1
Clear Multiselect List box 3
My code seems to hate me.... 2
Create Report On the Fly 1
HOW TO Simplify following code 2
SQL problem 9
Error 3075 w/ query 5
Listbox double click event 8

Top