[AccessD] Create a Search

Virginia,
This works well for me...
I'll try to break down my spaghetti code.

I have an Events Log mdb where users enter significant events.

The table contains, among other things, the fields:
EventDate (When did it happen)
Reporter (Who logged it)
Notes (What happened)

The search form contains fields (Text or ComboBox) for:
BeginningDate (A TextBox populated with the Calendar Control)
EndingDate (A TextBox populated with the Calendar Control)
Reporter (A ComboBox pulling data from a Table with Authorized
Reporters)
Notes (A TextBox populated with the search string for the Notes Field)



The code below creates a string to use as the "WhereCondition" in the
DoCmd.OperReport command.
The string ends up being...
Where...
EventDate >= Forms!Main!beginningdate.value and EventDate <=
Forms!Main!endingdate.value and reporter = 'Waddle SMS' and notes Like
'*' & Forms!Main!txt_notes.value & '*' and category1 Like '*' &
Forms!Main!txt_category.value & '*'

'*** Code Start ***
Private Sub SearchReport_Click()
On Error GoTo Err_SearchReport_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim DateError As Variant
Dim strREPORTER As String
Dim strFollowup As String
    
    If txt_Reporter.Value <> "" Then
    strREPORTER = " and reporter = '" & txt_Reporter.Value & "'"
    Else
    strREPORTER = ""
    End If
    
    If chkFollowup = -1 Then
    strFollowup = " and followup =  -1"
    Else
    strFollowup = ""
    End If
    
    
    stDocName = "LogBook"
    stLinkCriteria = "EventDate >= Forms!Main!beginningdate.value and "
_
        & "EventDate <= Forms!Main!endingdate.value" & strREPORTER

If EndingDate.Value < BeginningDate.Value Then
DateError = msgbox("You have entered an ENDING DATE that is " & Chr(13)
_
& "earlier than the BEGINNING DATE" _
& Chr(13) & Chr(13) _
& "Enter a New Date", _
vbCritical + vbOKOnly, "Date Error")

BeginningDate.Value = Date
EndingDate.Value = Date
BeginningDate.SetFocus
Else
    stDocName = "LogBook"
    stLinkCriteria = "EventDate >= Forms!Main!beginningdate.value and "
_
        & "EventDate <= Forms!Main!endingdate.value" _
        & strREPORTER & strFollowup
    If txtNotes.Value <> "" Then
        stLinkCriteria = stLinkCriteria & " and notes Like '*' &
Forms!Main!txtNotes.value & '*'"
    End If
    If txtCategory.Value <> "" Then
        stLinkCriteria = stLinkCriteria & " and category1 Like '*' &
Forms!Main!txtCategory.value & '*'"
    End If
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
    DoCmd.Maximize
    
End If
Exit_SearchReport_Click:
    Exit Sub

Err_SearchReport_Click:
    Debug.Print Err.Number & " " & Err.Description
    Resume Exit_SearchReport_Click
End Sub
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Hollis,Virginia
Sent: Thursday, July 29, 2004 4:48 AM
To: 'accessD at databaseadvisors.com'
Subject: [AccessD] Create a Search


Does anyone have an example of creating a report by selecting search
criteria from fields? I had a request today to add a custom report
builder to one of my databases.

 

Some of the fields will be text where they can enter what they need and
other criteria will be from a combobox, they also want a date range
field. Then! they want to be able to save the criteria for later use.

 

For example - Document number - text box, Document type will be a
combobox. I want to know all the documents created during a certain
month, quarter, year, etc.).

 

I have tried something like this several times, but when it comes to
having more than one search criteria, I can never seem to make it work -
then adding a date on top of that - well, I am lost.

 

Thanks,

Virginia

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 7/16/2004