[AccessD] Report criteria using form

The query will be puttin quotes around the string  in txtDepartment so your criteria will 
end up as something like
 Like '"2 West" or "Education"'    (note the single quotes in there)
or
Like "2 West or Education"

The simplest way around it is - don't set any criteria in the underlying query. Have it just 
report all Departments. Then set the criteria when you open the report

strCriteria = "Like " & txtDepartment
DoCmd.OpenReport "MyReport",acViewPreview,,strCriteria

Just make sure you do use quotes around anything like "2 West"

What I tend to do in cases like this to avoid the problems of users getting the quoting 
syntax wrong is create a listbox with "Extended Mutiselect" and fill it with all possible 
department names.  That way the user can just select the departments he wants.
I use something like:

Dim lngLoopcount As Long
Dim lngItems As Long
Dim strItem As String
Dim strOr As String
Dim strDQ As String
strDQ = Chr$(34) 'Double Quotes
For lngCounter = 0 To lstDepartments.ListCount - 1
If lstDepartments(lngcount).Selected Then
   lngItems = lngItems + 1
   strItem = lstDepartments(lngcount).ItemData
   Replace strItem, strDQ, strDQ & strDQ ' take care of any double quotes in Dept name
   If lngItems = 1 Then
      strCriteria = strDQ & strItem & strDQ
   Else
      strCriteria = strCriteria & " or " & strDQ & strItem & strDQ
  End If
End If
Next


> I have built a form to run a report that allows the user to set various
> criteria using the following in the criteria line of the underlying
> query of the report:  Like
> [Forms]![frmAttendenceReports]![txtDepartment].  This works great if I'm
> looking for one particular department such as "education" or all
> departments which is obtained by using "*".  I can't get it to work if I
> want to run the report for two separate departments.  I have tried the
> following entries in the textbox:
> 
> 2 West Or Education
> 
> "2 West" Or "Education"
>   
> 
> I have verified that there are records for both departments.  I have
> put the criteria directly in the query and it does work... I just can't
> get it to work through the form.
> 
> Any suggestions?
> 
> Dwylene Garcia
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.