Query by multiple random fields

Goal:
i wish to be able to search by either entering just one field or multiple fields at random

Situation:
- I have a 1 table with 12 fields.
- on a form i have 10 textboxes and a button

- when the button is pressed, I open a query to obatin any records that correspond to the data given.

The Issue:
as soon as i specify one field to search by, the query doesn't seem to perform properly. are all specifications found on the row of criteria seen as 'AND' statements??

I also have some 'Or:' statements that I use to rule out blank textboxes. however, those get shifted over to a new column and row upon saving.

ie. I enter
or: [txtbox] Is Null

and what happens is that [txtbox] gets moved to a new column at the end of the query and Is Null remains in the same row, but shifts to the same column as the [txtbox].

Thanks for reading so far. I hope I've stated it clearly..

Any help/pointers are greatly appreciated! Thank you very much!!!

Query by Form

I use this code to pull 1 or more search items from a form producing the search results in a report but you can have the results display in a query if you wish. It does look neater with a report though. There is a function that goes with this and runs the query each time you search on the form. The function is called when you perform the search

Private Sub Search_Click()
Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim where As Variant

Set MyDatabase = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
End If

where = Null
where = where & " AND [ConID]= " + Me![ConID]
where = where & " AND [CategoryID]= " + Me![CategoryID]
where = where & " AND [DonotContactID]= " + Me![DoNotContactID]
where = where & " AND [ContactStatusID]= " + Me![ContactStatusID]


If Not IsNull(Me![Meeting End Date]) Then
where = where & " AND [Meeting Date] between #" + _
Me![Meeting Start Date] + "# AND #" & Me![Meeting End Date] & "#"
Else
where = where & " AND [Meeting Date] >= #" + Me![Meeting Start Date] _
+ " #"
End If

Me.Form.Visible = False
Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from tblProspects " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "SearchResults", acViewPreview, "qryDynamic_QBF"
End Sub

If you would like more details or a sample of how this works let me know.

Hayley

ummm....

Hi Hayley,

Thank you very much for the reply. Kind of confused tho. Outputting to a report is fine as well. I didn't know you could go straight to a form. I was thinkinking of getting the query working then producing a report based on the query. That way I could also produce a form from the query as well...

about the function:

Private Sub Search_Click()
Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim where As Variant

Set MyDatabase = CurrentDb()

' Delete the existing dynamic query; trap the error if the query
' does not exist.
If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
End If

Click to expand...

that part seems ok

but i kind of get confused here...

where = Null
where = where & " AND [ConID]= " + Me![ConID]
where = where & " AND [CategoryID]= " + Me![CategoryID]
where = where & " AND [DonotContactID]= " + Me![DoNotContactID]
where = where & " AND [ContactStatusID]= " + Me![ContactStatusID]

Click to expand...

aren't these multiple declarations, but the last where is a culmonation of all the other ones above right? or am I way off???

the next section deals with search between start/end dates right?

If Not IsNull(Me![Meeting End Date]) Then
where = where & " AND [Meeting Date] between #" + _
Me![Meeting Start Date] + "# AND #" & Me![Meeting End Date] & "#"
Else
where = where & " AND [Meeting Date] >= #" + Me![Meeting Start Date] _
+ " #"
End If

Click to expand...

then this part baffles me

Me.Form.Visible = False
Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from tblProspects " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "SearchResults", acViewPreview, "qryDynamic_QBF"
End Sub

Click to expand...

so this function scans a table and returns results according to inputs from a form right??

what i have right now is a button that activates a query. these are the criteria that i have for each field.

criteria: [Year]
or: [Year] Is Null

[Sample]
[Sample] is null

Between [BeginDate] and [EndDate]
[BeginDate] and [EndDate] is null

Like "*" & [product] & "*"
[product] is null

Like "*" & [description] & "*"
[description] is null

Like "*" & [Tests] & "*"
[Tests] is null

Like "*" & [Contact] & "*"
[Contact] is null

Like "*" & [Comments] & "*"
[Comments] is null

If you would like more details or a sample of how this works let me know.

doesn't seem to be working tho.. Not sure what is wrong here...

if it wouldn't be too much trouble, I'd be more than happy to look at any example you give me. Having only picked up this stuff a littlwe while ago I don't know much. You'll have to excuse my slowness. Thank you very much for everything!

Eric

Hi Eric

I have sent a sample to your email address along with an explanation of my code. Please let me know if you don't get it. If anyone else would like a copy of this sample just send me an email as unfortunately the forum won't accept the attachment due to the file size.

Hayley