By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Pulling report with two criteria and date; type mismatch on preview

P: 2
Hello

I am still new to Access and I am currently working on debugging some code. I have a form with the following:

Employee Name (combo with select all choice)
Task (combo with select all choice)
Begin date (txt)
End Date (txt)

Both of the combo boxes are pulling from separate union queries

I am trying to get the form to pull a report depending on what criteria is chosen in the form. It works fine if you choose all employees/all tasks, one employee/all tasks, or all employees/one task, but if I try to pull one employee/one task, I get a type mismatch error. (no error code, just a msgbox that states type mismatch). Both of the combo boxes have an ID number(autonumber) and name (txt) fields. I have used -1 in both of the ID's fields for select all employees/tasks.

Here is my code, please let me know how it looks overall and if there is anything I can improve on. I am still learning :) I am almost certain it has to do with line 43 (after the last else) but I have tried several different ways of wording it and nothing has worked.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Command11_Click()
  2. On Error GoTo Err_Command11_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim strWhereEmpl As String
  6.     Dim strWhereTask As String
  7.  
  8.     If IsNull(Me.cmbEmpl) Then
  9.         MsgBox "Select an employee to Preview."
  10.         Me.cmbEmpl.SetFocus
  11.         Exit Sub
  12.     End If
  13.  
  14.     If IsNull(Me.cmbTask) Then
  15.         MsgBox "Select a task to Preview."
  16.         Me.cmbTask.SetFocus
  17.         Exit Sub
  18.     End If
  19.  
  20.     'check to see that ending date is later than beginning date.
  21.     If IsDate(Begindate) And IsDate(Enddate) Then
  22.         If CDate(Enddate) < CDate(Begindate) Then
  23.             MsgBox "The ending date must be later than the beginning date."
  24.             Enddate.SetFocus
  25.             Exit Sub
  26.         End If
  27.     Else
  28.         MsgBox "Please use a valid date for the beginning date and the ending date values."
  29.         Exit Sub
  30.     End If
  31.  
  32.         strWhereEmpl = "EmplID = " & Forms![QRreportform]!cmbEmpl
  33.         strWhereTask = "TaskID = " & Forms![QRreportform]!cmbTask
  34.         stDocName = "QRsub"
  35.  
  36.     If Me.cmbEmpl = -1 And Me.cmbTask = -1 Then
  37.         DoCmd.OpenReport stDocName, acPreview
  38.     ElseIf Me.cmbTask = -1 Then
  39.         DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl
  40.     ElseIf Me.cmbEmpl = -1 Then
  41.         DoCmd.OpenReport stDocName, acPreview, , strWhereTask
  42.     Else
  43.         DoCmd.OpenReport stDocName, acPreview, strWhereEmpl And strWhereTask
  44.     End If
  45.  
  46.  
  47. Exit_Command11_Click:
  48.     Exit Sub
  49.  
  50. Err_Command11_Click:
  51.     MsgBox Err.Description
  52.     Resume Exit_Command11_Click
  53.  
  54. End Sub
  55.  
You guys have already helped me out a lot by just reading your posts. Thanks and thanks again for your help!

Jonathon
Feb 4 '08 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The syntax on your criteria is not correct ..

Try this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command11_Click()
  2. On Error GoTo Err_Command11_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim strWhereEmpl As String
  6.     Dim strWhereTask As String
  7.  
  8.     If IsNull(Me.cmbEmpl) Then
  9.         MsgBox "Select an employee to Preview."
  10.         Me.cmbEmpl.SetFocus
  11.         Exit Sub
  12.     End If
  13.  
  14.     If IsNull(Me.cmbTask) Then
  15.         MsgBox "Select a task to Preview."
  16.         Me.cmbTask.SetFocus
  17.         Exit Sub
  18.     End If
  19.  
  20.     'check to see that ending date is later than beginning date.
  21.     If IsDate(Begindate) And IsDate(Enddate) Then
  22.         If CDate(Enddate) < CDate(Begindate) Then
  23.             MsgBox "The ending date must be later than the beginning date."
  24.             Enddate.SetFocus
  25.             Exit Sub
  26.         End If
  27.     Else
  28.         MsgBox "Please use a valid date for the beginning date and the ending date values."
  29.         Exit Sub
  30.     End If
  31.  
  32.         strWhereEmpl = "EmplID = " & Forms![QRreportform]!cmbEmpl
  33.         strWhereTask = "TaskID = " & Forms![QRreportform]!cmbTask
  34.         stDocName = "QRsub"
  35.  
  36.     If Me.cmbEmpl = -1 And Me.cmbTask = -1 Then
  37.         DoCmd.OpenReport stDocName, acPreview
  38.     ElseIf Me.cmbTask = -1 Then
  39.         DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl
  40.     ElseIf Me.cmbEmpl = -1 Then
  41.         DoCmd.OpenReport stDocName, acPreview, , strWhereTask
  42.     Else
  43.         DoCmd.OpenReport stDocName, acPreview, strWhereEmpl & " And " & strWhereTask
  44.     End If
  45.  
  46.  
  47. Exit_Command11_Click:
  48.     Exit Sub
  49.  
  50. Err_Command11_Click:
  51.     MsgBox Err.Description
  52.     Resume Exit_Command11_Click
  53.  
  54. End Sub
  55.  
Feb 5 '08 #2

P: 2
That worked perfectly!! I knew it was something simple but I just couldn't figure it out. Thanks again for your help
Feb 5 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
That worked perfectly!! I knew it was something simple but I just couldn't figure it out. Thanks again for your help
You're welcome.
Feb 5 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.