Problem with my query.

[Guest]

I have no idea why this isn't working but I have a query that runs 4
different searches (5 total) based on data inputed through a Form called
[Start Up].

Here is what I have in my query (qrySearch):

Type:
Criteria: [Forms]![Start Up]![ddlType]

Date:
Criteria: Between [Forms]![Start Up]![txtDatein] And [Forms]![Start
Up]![txtDateout]

Client:
Criteria: [Forms]![Start Up]![ddlClient]

Title
Criteria: Like "*" & [Forms]![Start Up]![txtSearch] & "*"
or
Details
Criteria: Like "*" & [Forms]![Start Up]![txtSearch] & "*"

*ddl____ means a combo box sourced to a table*

So when I run the query from a button on the Start Up form, I get either
nothing or everything.

Through trial and error I have found that if I erase the ciretia in all the
fields except Title and Details. The search works perfectly. But if I want
to refine the search and add the criteria for say, Type, the results are
usually only a few records but not necessarily ones exclusive to the Type I
selected in the form.

I don't know if this makes much sense but I am pretty sure my query is built
correctly. It is just that I get very weird results.

If you have any information, or more likely, need any information from me,
please let me know.

Thank you,

Billy

 

[John Vinson]

I don't know if this makes much sense but I am pretty sure my query is built
correctly. It is just that I get very weird results.

If you have any information, or more likely, need any information from me,
please let me know.

My guess is that the OR logic is incorrect. If you have several
criteria on the first line, and only one on the second line, then the
single criterion on the second line will be applied alone.

Please open the query in SQL view and copy and paste the SQL text to a
message here. It may look cryptic if you're just getting started, but
it is the *real* query and it's actually clearer than trying to
replicate the grid.


John W. Vinson[MVP]

 

[Van T. Dinh]

Another possibility is that you might have set an incorrect column for the
BoundColumn in the ComboBox.

In Access, ComboBox can have multiple columns in the row source and the
value returned when you reference (as posted) will be the value of the of
the BoundColumn for the selected row. Thus, if you set the BoundColumn
incorrectly, the Query will pick up the wrong value.

 

[Guest]

Here is my SQL for my query.

SELECT Archive.ID, Archive.Type, Archive.Date, Archive.Client,
Archive.Title, Archive.[#], Archive.Format, Archive.Length, Archive.Details,
Archive.Location
FROM Archive
WHERE (((Archive.Type)=[Forms]![Start Up]![ddlType]) AND ((Archive.Date)
Between [Forms]![Start Up]![txtDatein] And [Forms]![Start Up]![txtDateout])
AND ((Archive.Client)=[Forms]![Start Up]![ddlClient]) AND ((Archive.Details)
Like "*" & [Forms]![Start Up]![txtSearch] & "*")) OR (((Archive.Title) Like
"*" & [Forms]![Start Up]![txtSearch] & "*"));