 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2007query to pull up all jobs due on WEd. for example
I have a list of jobs in a jobs table and it list which days the jobs need to be done. For example some are just M, some M, W, F , some just W etc. I am trying to grab the current date on the Form ( an unbound field where I use datepart to grab the current DAY I have created a query for the jobs list and would like for the criteria to pull up all jobs say due on Wednesday. I can put the criteria in as Like "*w*" and get what I want. However I would like for it to know that it is wednesday(or whatever the CURRENT DAY is) and grab it from the form(or just from the query-whatever would work) and use this field in the Query Criteria. What am I missing?? How do I write the criteria in the query. Thanks, Barb Create a text box and put data sorce as =Format(Now(),"ddd") or put it as the criteria in the query (Remember to put the field in the same format) This will give you the current day of the week in 3 digit format "Mon" etc Hope this helps Trev > I have a list of jobs in a jobs table and it list which days the jobs need to > be done. For example some are just M, some M, W, F , some just W etc. [quoted text clipped - 11 lines] > Thanks, > Barb Trevor, Thanks for your help. I am still not clear on what to do here. If I have the text box on the form with datasource as set as below. I ALSO need the query behind the scenes to use what is in that textbox as the criteria and want to find say Thu- if it is listed in the Frequecny of the job by ITSELF or for example if it is listed say as T, TH. Do I use this both in the query and on the form and for the query critera would the below info find both Th on its own and in a list say M, T, Th. Thanks so much again for your help, BArb > Create a text box and put data sorce as =Format(Now(),"ddd") > [quoted text clipped - 22 lines] > > Thanks, > > Barb Hi Babs, One problem you have is that you do not use the standard naming convention that the system uses ie Day of the week. The system uses 3 standards as follows:- 1) Mon Format([a date],"ddd") 2) Monday Format([a date],"dddd") 3) 2 (Sun =1, Mon = 2 etc) Format([a date],"w") Since you use non standard you will have to write code in a module or code to check the standard against your non standard day of the week. May I suggest you use standard to make life easier. Ask for help on format function (User Defined) on dates for more information. (If you still want to use non standard reply and will have to write a module procudure for this.) After you have changed to standard just put this in the criteria of the query and it will pick up just the current day of the week. Format(Now(),"ddd") Hope this helps. > Trevor, > [quoted text clipped - 36 lines] > > > Thanks, > > > Barb sorry just got back to working on this. I put your inital recommended code in the citeria. I changed the days to the standard convention of Tue for Tuesday instead of T- no problem with that. However it pulls up all of the records with frequency Tue ONLY if Tue is part of more freq. for example Tue, Thu those records are not coming up. how can I modify the criteria to grab these records??? Thanks, Barb > Hi Babs, > [quoted text clipped - 61 lines] > > > > Thanks, > > > > Barb Hi babs, Not sure where you are up to so lets try this:- Create a Query with the fields you want that then add a date to the Field section like: ddd_Date:Format([Date to Check against],"ddd") [Date to Check against] is a date field you want to use Then in Criteria you can put: In("Tue") for Tue or In("Wed") for Wed or In("Tue","Thu","Sat") for Tue, Wed & Sat or Not In("Tue","Thu","Sat") for Sun, Mon, Wed, Fri Hope this helps Trev > sorry just got back to working on this. I put your inital recommended code > in the citeria. I changed the days to the standard convention of Tue for [quoted text clipped - 71 lines] > > > > > Thanks, > > > > > Barb Trever, Thanks for your help I am still not seeming to explain what I need clearly Right now I want to grab Today's DAY and pull up all records in the query(criteria) with frequecy of doing all the jobs due today say- Wed . I have in the criteria for the Frequency field =Format(Now(),"ddd"). It does pull up ALL Wed ONLY frequency but want it to pull up if it says Mon, Wed, Fri or Mon, Wed or any Combination. Can I somehow use the In with what is already in the criteria row for the frequecy field? Doesn't seem to be working. In("=Format(Now(),"ddd")" ) any ideas? Thanks, Barb > Hi babs, > [quoted text clipped - 102 lines] > > > > > > Thanks, > > > > > > Barb Hi Babs, Now we are changing things, You want to select the dates and then run a query on the selection made. The best way to do this would be using a selection criteria from a combo box on a form. Step 1. Create a combo box on a form that selects the Day you want. (I Have used form1 Step 2 . Amend Query to include this creteria to (No = required) Eval("([Forms]![Form1]![TestDate])") The combo box(TestDate) now contains the day of the week If you want to include multi creteria use another combo box for step one and use next line in criteria for step 2 and so on. Hope this helps Trev > Trever, > [quoted text clipped - 123 lines] > > > > > > > Thanks, > > > > > > > Barb It seems to me you should be using a 3 letter identification for day of the week to distinguish between Tuesday, Thursday, Saturday and Sunday. Here is my solution: Three lettered day: Criteria [field] = CStr(Format(Date(),"ddd")) 1 Lettered day Left(CStr(Format(Date(),"ddd")),1) Either one will work for criteria on an Access Database, if you are using SQL server, then you need to do something completely different. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.