Using Check Boxes and Dates in a Query.

  • Thread starter Thread starter Daryl B. via AccessMonster.com
  • Start date Start date
D

Daryl B. via AccessMonster.com

I'm trying to create a Report from a Query that uses a Form that has Check
Boxes and Dates for its criteria.
Example:
Start Date :________
End Date :________

Paid : Yes/No
Completed: Yes/No
Invoiced : Yes/No

I do not know that much about Queries or SQL in Access.
Must keep in mind all the diffenent possibilities. Some times I just might
use the Between Dates. Sometimes I might use the Between dates and just one
Check box. I need to know how to check for this, and how to deal with it. I
have set up Queries using the Between function along with checking for the
chkBox on the Form. I hope I don't have to create a Query for every
diffenent combination. Pleas Help...Thank you
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Set up a query that gets the data you want w/o the criteria. Then set
up the Report to filter the query to just what is indicated on the form.
Use the Report's OnOpen event to read the data from the criteria form.
Then set the report's Filter & FilterOn properties. You'll need to use
VBA to do this.

Search the Google.com Group's archive in comp.databases.ms-access for
examples of using the Report's OnOpen event to filter data.

This procedure is described in the _Access # Developer's Handbook_ by
Ken Getz, et. al. The "#" is the Access version (95, 97, 2000).

If anyone else knows how to read the Microsoft archive (if there is one)
on this ng, please post.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmP5woechKqOuFEgEQKKEACgmm3edj3h/7pjm4S/YUMxPZiSrQsAoOwN
GPhvcSxt0dPcaVZyNvPin0UT
=mYuP
-----END PGP SIGNATURE-----
 
Thank you for posting MG Foster. I will keep in mind what you have
suggested for further query options. I have modified some funcitons I saw
in another post that gives me all the results I wanted. I use a second set
of check boxes to actualy turn on and off the items I want to use as the
criteria. I will post the code in case anyone else is interested.

Private Sub cmdSearch_Click()
Dim strWHERE As String
strWHERE = ""

If Me![chkIncludeDate] = True Then
If IsNull(Me![txtStartDate]) Or IsNull(Me![txtEndDate]) Then
Call AlertMessage("Must enter a Start Date and End Date!",
"Date Error")
Exit Sub
End If
strWHERE = "DateEntered Between #" & Me![txtStartDate] & "# And #"
& Me![txtEndDate] & "#"
End If

If Me![chkIncludeComp] = True Then
If Len(strWHERE) = 0 Then
strWHERE = strWHERE & "Completed = " & Me![chkCompletedTrue] &
""
Else
strWHERE = strWHERE & " AND Completed = " & Me!
[chkCompletedTrue] & ""
End If
End If

If Me![chkIncludeInv] = True Then
If Len(strWHERE) = 0 Then
strWHERE = strWHERE & "Invoiced = " & Me![chkInvoicedTrue] & ""
Else
strWHERE = strWHERE & " AND Invoiced = " & Me![chkInvoicedTrue]
& ""
End If
End If

If Me![chkIncludePaid] = True Then
If Len(strWHERE) = 0 Then
strWHERE = strWHERE & "Paid = " & Me![chkPaidTrue] & ""
Else
strWHERE = strWHERE & " AND Paid = " & Me![chkPaidTrue] & ""
End If
End If
DoCmd.OpenReport "rptSearch", acViewPreview, , strWHERE

End Sub
 
Back
Top