Search button for more than one field search

[aym]

Hi everyone

Please I need help as am a novice in Access. I have been trying create search button.

I create a query with the following fields

Customer ID
Product ID
Customer name
Date of transaction
Amount paid
Balance

I want the search button to search more than one Field i.e.

1. Product name(Combo)
2. Transaction Date to have search range like "Start date" and "End Date".

Please help me with a code and procedure.

Thanks for your usual assistance.

 

[Gasman]

Review how Allen does it.

 

[jdraw]

aym,
I would caution you on using field names with embedded spaces--they will come back to haunt you.

 

[Ken Sheridan]

I want the search button to search more than one Field i.e.

1. Product name(Combo)
2. Transaction Date to have search range like "Start date" and "End Date".

I would base the query on a second query which draws upon your original query, and references an unbound combo box and two unbound text boxes as parameters in the header of a form in which the values are entered. This second query should be the RecordSource of the form:

SELECT *
FROM [NameOfQueryGoesHere]
WHERE ([Product ID] = Forms![NameOfFormGoesHere]![cboProduct]
    OR Forms![NameOfFormGoesHere]![cboProduct] IS NULL)
AND ([Date Of Transaction] >= Forms![NameOfFormGoesHere]![txtDateFrom]
    OR Forms![NameOfFormGoesHere]![txtDateFrom] IS NULL)
AND ([Date Of Transaction] < Forms![NameOfFormGoesHere]![txtDateTo] + 1
    OR Forms![NameOfFormGoesHere]![txtDateTo] IS NULL)
ORDER BY [Date Of Transaction];)

In the above cboProduct, txtDateFrom, and txtDateTo are the names of the unbound controls in the form's header.

In the AfterUpdate event procedure of each of the three controls requery the form with:

Me.Requery

When you select or enter a value in each control this will reload the form's recordset, progressively restricting the rows returned on the basis of the values in the controls. By testing each parameter for OR IS NULL this makes them optional, so you can select or enter values in as few or as many as you wish. Note how the date range is defined as on or later than the start date and less than the day following the end date. This caters for any rows in the table which might have a non-zero time of day element. You can't assume with confidence that such rows don't exist unless you've made specific provision in the table definition to disallow such date/time values. If a BETWEEN...AND operation were used any such rows with a date on the last day of the range would not be returned.

I'd echo jdraw's caution against the inclusion of spaces (or other special characters) in object names. Always use CamelCase or represent a space by an underscore character like_this.

 

[Uncle Gizmo]

I have been trying create search button.

Deleted, the link goes to the wrong page? No idea why!!!

Am investigating!