Solved Search Year of Delivery Date via Combo box on a form (1 Viewer)

[Bean Machine]

Hi all! I have a field in a query in which the day, month, year and time of delivery for an item is stored (mm/dd/yyyy format). I know how to limit to the delivery date in mm/dd/yyyy format but is there a way that I could limit it to simply year. I tried just typing "2020" for example, in the combo box but I get an error saying "The value you entered isn't valid for this field". I checked the field size (as thats one of the fixes it tells me to try) and thats completely fine, I was just wondering how I can either fix the error or get the combo box to only display years and have it effectively filter to those items delivered in that year. I appreciate any help!

 

[conception_native_0123]

there are 2 things involved here. the box and the field itself. the field should remain as a date. if you want the combo to display the year only, you could use datepart() i believe, in the control source, or the right() function to pick out the long numbers of the date. there might be more to it other than that though. but the simplest way might actually be to re-organize your data based on what you want to see. 3 fields for 3 parts of the date, perhaps? 4 fields? 3 for all the different parts, and one for the full fledged date?

there is also other filtering that can go on here i'm sure. many different ways....

 

[June7]

Controls used to enter filter criteria should be UNBOUND, otherwise you change data in record.

Combobox can have a RowSource like: SELECT DISTINCT Year([mydatefield]) AS Yr FROM tablename;

Calculate a Year field in query and use that field to apply filter: SELECT mytable.*, Year([mydatefield]) AS Yr FROM mytable;

Review http://allenbrowne.com/ser-62.html

 

[Bean Machine]

Controls used to enter filter criteria should be UNBOUND, otherwise you change data in record.

Combobox can have a RowSource like: SELECT DISTINCT Year([mydatefield]) AS Yr FROM tablename;

Calculate a Year field in query and use that field to apply filter: SELECT mytable.*, Year([mydatefield]) AS Yr FROM mytable;

Review http://allenbrowne.com/ser-62.html

Yup I always ensure that the control is unbound for filtering purposes, also I will try this out, thanks!

 

[Bean Machine]

Worked perfectly thanks! Will definitely remember this for future projects, appreciate the help!