Filtering on the value of a formatted date field (1 Viewer)

GrandMasterTuck

In need of medication
Local time
Today, 05:32
Joined
May 4, 2013
Messages
129
Hi, geniuses! You guys were such a big help to me last time that I thought I'd pose the next question I have to you, in the hopes you can answer that too!

I have a database with a table of employees, and that has a column named "DaysOff", into which I type a couple of days like this: "SUNDAY AND MONDAY"

Next, I have a form onto which I will display a query of those employees. The form contains a field called DATE in which I display the mm/dd/yyyy date. What I want to do is this:

I only want those employees that have a DaysOff field that DOES NOT CONTAIN the WEEKDAY NAME of the date in the DATE field. So, for instance, if the DATE field read 2/22/2014, and I have three employees as such:

Employee 1 DaysOff "Monday and Tuesday"
Employee 2 DaysOff "Friday and Saturday"
Employee 3 DaysOff "Sunday and Monday"

In that case, ONLY EMPLOYEES 1 and 3 should show up. Employee 2 will NOT show up, because his DaysOff field contains the word "Saturday", and the WEEKDAY FORMATTED value of the DATE field is "Saturday"

I hope that's not too confusing. Again, this needs to be the filter criteria for the query. That query will only return a list of employees that are NOT off on the day in question. If one of their off days is the day in question, the query won't return that record.

Anyone have any ideas? I've tried a few things, but I can't get it to work. If you want, I can list the various things I've tried, but I imagine one of you knows exactly how to do what I need to do, and you won't need my feeble attempts as a springboard.

Thanks again for your help. You guys are awesome.
 

bob fitz

AWF VIP
Local time
Today, 09:32
Joined
May 23, 2011
Messages
4,717
Do not use "Date" as the name of a field. It is a reserved word.
IMHO it would be better to normalize your data by having a table to store details of each employees days off. The attached db shows how you can get what you want with your current table structure.
 

Attachments

  • db2.mdb
    324 KB · Views: 477

GrandMasterTuck

In need of medication
Local time
Today, 05:32
Joined
May 4, 2013
Messages
129
Thanks Bob Fitz... but I solved my own problem. What I had to do was this:

1. I created another date field, and set the Control Source to "Format([DATE], "dddd"), and I called that "WEEKDAY". This copied the data from the DATE field into the new field, but changed it to the name of the day of the week, and saved the data in that format.

2. I then created a query with the filter criteria "Not Like "*" & [Forms]![MyForm]![WEEKDAY] & "*""

And bingo! Works a treat. I just had to create that one copy of the Date field, and base the query off that one, instead of the original one with the date in it.

The mistake I was making before was that I had a copy of the DATE field, but instead of setting the Control Source to that Format() function, I set the field's Format to DATE("dddd"). The problem therein is that by setting a Format, the field's ACTUAL DATA was still the numerical date, and therefore when I ran my query, it wasn't seeing the text of the weekday in there, it was seeing the numeric date instead, even though the field on the form was FORMATTED to show a weekday name. By deleting the Format data for that field, and then setting the Control Source data to that Format() function, I was essentially pulling data from the DATE field, and CONVERTING it to a weekday name, and then saving the weekday name in that field. Then I ran my query off the weekday name field.

I hope that's not confusing to anyone trying to understand what I did. It's all about how Access treats the data behind the scenes, regardless of how your FORMS treat the data for purposes of DISPLAY.

Make sense? :)
 
Last edited:

Users who are viewing this thread

Top Bottom