I'm an Access DB beginner. I have a database with a SearchForm where the user can enter search criteria, click the Search button and populate the subform with the filtered results.

The query has simple query based on what the user enters in fields in the search form

Like "*" & [Forms]![SampleSearchF]![txtMicrochip] & "*" which work well, but my date filter does not produce any results: Between [Forms]![SampleSearchF]![DateReceivFrom] And [Forms]![SampleSearchF]![DateReceivTo]

The table fields that the date search is based on are Data Type:Date/Time , Format: Short Date The Search Form fields are Format Short Date The subform fields are also Short Date

SearchButton is a requery macro

And when I have the this query criteria in the query, none of the search functions work. Any suggestions where I could look to solve the issue? Any help is appreciated.

Here is my SQL code for the search query,

FROM IndividualsT INNER JOIN SamplesT ON IndividualsT.AnimalID = SamplesT.AnimalID
WHERE (((IndividualsT.SpeciesName) Like "*" & [Forms]![SampleSearchF]![txtSpeciesName] & "*") AND 
((IndividualsT.Microchip) Like "*" & [Forms]![SampleSearchF]![txtMicrochip] & "*") AND 
((IndividualsT.Name) Like "*" & [Forms]![SampleSearchF]![txtName] & "*") AND
 ((SamplesT.Location) Like "*" & [Forms]![SampleSearchF]![txtLocation] & "*") AND
 ((SamplesT.SampleReceived) Between [Forms]![SampleSearchF]![DateReceivFrom] And [Forms]![SampleSearchF]![DateReceivTo]));

SamplesT

IndividualsT

CodePudding user response：

Always specify the data type of the parameters:

Parameters
    [Forms]![SampleSearchF]![txtSpeciesName] Text ( 255 ),
    [Forms]![SampleSearchF]![txtMicrochip] Text ( 255 ),
    [Forms]![SampleSearchF]![txtName] Text ( 255 ),
    [Forms]![SampleSearchF]![txtLocation] Text ( 255 ),
    [Forms]![SampleSearchF]![DateReceivFrom] DateTime,
    [Forms]![SampleSearchF]![DateReceivTo] DateTime;
Select *
From YourTable
Where ...

CodePudding user response：

Likely, your issue is the WHERE logic when form fields are empty. When empty, LIKE expressions return as ** which means anything, so no rows are filtered out. However, empty dates conflict with BETWEEN clause. Consider using NZ to return the column itself if form fields are empty:

(
  SamplesT.SampleReceived 
    BETWEEN NZ([Forms]![SampleSearchF]![DateReceivFrom], SamplesT.SampleReceived)
        AND NZ([Forms]![SampleSearchF]![DateReceivTo], SamplesT.SampleReceived)
);