CODEWITHSUNDEEP
sqldatems-accessbetween
KG12
KG12

Reputation: 77

MS Access Between dates query is not working

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

SampleID AnimalID SampleReceived Location CollectionDate
1 1 18/08/2021 Tassie 10/08/2021
7 1 15/09/2021 Berlin 25/09/2021
13 12 25/09/2021 Sydney 4/09/2021
14 12 24/09/2021 New York 1/09/2021

IndividualsT

AnimalID SpeciesName Microchip Name
1 Parrot 1234 Hugo
12 Koala 853 Sherlock

SamplesTable_DesignView

Upvotes: 1

Views: 1054

Answers (2)

Parfait
Parfait

Reputation: 107767

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)
);

Upvotes: 1

Gustav
Gustav

Reputation: 56016

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 ...

Upvotes: 0

Related Questions