I have a table of departed employees. I need to see the records of employees that have departed in the last 90 days. The format for the date is dd-mmm-yy. I am thinking that may be the problem, but i don't know. I don't get an error, I just get a blank query. When I remove the criteria, i can see everyone.
The format for the date is dd-mmm-yy. I am thinking that may be the problem, but i don't know. I don't get an error, I just get a blank query. When I remove the criteria, i can see everyone.
as howey said "The obvious question is how is your date field set up ? It must be set up as a date type not a string."
The date format of 'dd-mmm-yy' is not a preset format under the Date type so that suggests the field is not set as a Date/Time format. If it is a Text type field you will not get the expected results using date criteria. You can check this by looking at the Table in the Design View. Under the Data Type you should have Date/Time selected? But don't change this if it is not Date/Time as you may loose some formats, so Test first. the best way to update the field may be to create a new field with the correct data type then run an update query to populate the dates from the current Date/Text field applying the correct formatting. Then change any forms/queries as needed to work with the proper date format. Once you have a Date Type field either the Between function or the Greator than function should give you the results your looking for.
if you just treat the character field as a date, access will attempt the conversion for you, and if the values allow, it will figure out which numeric portion is the day and which is the year, but where there is ambiguity, it assumes that the year comes first
so, will access convert these strings to a date value correctly? let's see, by adding 0 days
(adding 0 days will barf if the value being added to is not a valid date)
now, obviously, that last one's an invalid date, unless 31 is the year and 02 is the day
, dateadd("d",0,charfield) as newdate0
clearly, it guesses correctly -- not only for 31-feb-02, but also for 09-sep-11 versus 09-sep-33
therefore, in order to fix the "ambiguous" values, you should explicitly insert the century:
therefore, you need to add a new date/time column to the table, then update the entire table, and use the formula provided above for newdate1 to set the value of the new date/time column, after which you can remove the text column