Open form based on search criteria

[cactuspete13]

I am trying to set up a form (Switchboard) that will open another form (Case Form) or report (Case Report) based on a series of criteria (Fiscal Year, Quarter, Case Number). On the switchboard there will be three text blocks where users can enter one, two or all three of the above mentioned items, hit either the View Case Form or View Case Report button to view only records that match those criteria. I've attached the VBA code I am working on right now, but when I search using 2013 for the Fiscal Year, I get a "Run-time Error: 13 Type mismatch" message. Below is the setup of the tables and forms, and the vba code.

Table: tblCase; with FiscalYear, Quarter, CaseNbr fields
Form: Switchboard; three text boxes named SearchFY, SearchQuarter, and SearchCaseNbr, and button Command7 (haven't changed the name yet)
Form: Case Form; Controls for FiscalYear, Quarter, and CaseNbr

VBA Code

Private Sub Command7_Click()

DoCmd.OpenForm "Case Form", , , "[FiscalYear] like '" & Me!SearchFY & "*' and [Quarter] like '" & Me!SearchQuarter * "*'"

End Sub



Any help would be appreciated

[pbaldy]

What are the data types of the fields? Do you really want/need wildcards?

[cactuspete13]

All are number except quarter which is text (dropdown option, 1st Quarter, 2nd...). Eventually there will be 4 options for searching and I am trying to get it so that users can search for all cases submitted by John during 1st Quarter 2013, or for all records during an entire fiscal year, quarter or by person. Obviously if they know the exact case number that's easier.

[pbaldy]

You don't want the single quotes around numeric values. Personally I wouldn't use Like and wildcards unless necessary.

[cactuspete13]

Ok, I've moved everything to one form, still trying to do the same thing, type person's name in the text block named SearchOwner to get the form to filter all records to only show when that person is the CaseOwner. I've tried both ApplyFilter macro with the Where Condition set to =[SearchOwner], and I've tried VBA: DoCmd.ApplyFilter , "[CaseOwner] = [SearchOwner]" No luck on either. Any ideas?

[pbaldy]

Names (text fields) do require the delimiters. Similar to this:

http://www.baldyweb.com/wherecondition.htm

[cactuspete13]

Awesome! Works like a charm! Managed to figure out adding multiple criteria but ran into another problem: When I add a second parameter it forces me to enter a criteria. How do I tell it that it is ok if the fields are empty?

[pbaldy]

I would build the string item by item, and only add each if the user has entered something.