CODEWITHSUNDEEP
databasems-accessms-access-2010
mrk777
mrk777

Reputation: 167

Optional Query Parameters in MS Access

Please help me in identifying the solution for the below problem.

I would like to get "Year" results from the Parameter, else all the results if the parameter is blank.

I have the date format(MMMM - YYYY) data in the Project_Start_Date field. Attached is the image for your reference.

Currently, I'm getting the results if the parameter is blank and if the parameter is entered with date, it is not working (not giving any results)

I tried the criteria - Like IIf(IsNull([Date of the Year]),"*",Year([Date of the Year]))

enter image description here

Upvotes: 0

Views: 286

Answers (2)

Gustav
Gustav

Reputation: 55906

This works here:

PARAMETERS 
    [Date of Year] DateTime;
SELECT ...
FROM ...
WHERE 
    [Date of Year] Is Null Or 
    [Project_Start_Date] Between 
        DateSerial(Year(Nz([Date of Year],Date())),1,1) And 
        DateSerial(Year(Nz([Date of Year],Date())),12,31);

This will use an index on [Project_Start_Date] if present.

Upvotes: 1

smoore4
smoore4

Reputation: 4866

Make an expression with your WHERE statement field Project_Start_date_Yr: Year([Project_Start_date]) and it should work.

Upvotes: 0

Related Questions