I have one problem I can't seem to get to the bottom of. I have a database that counts the attendance of my basketball team. Everything works great but I have one problem. Our basketball program is 12 weeks for each student. Now students can join any time but all of them are required to stay 12 weeks to complete the training. How do I get Access to tell me how many weeks a student has left in the program? As i was saying it is easy for me to add together the days of the week that the student attended, but I can't seem to figure out how to get the amount of weeks a student been attending. One more thing, I know this is asking alot but is there a way to have an extra column that allow me to add additional weeks to certain individuals (for the students that like to skip a week or to)?
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
I can see how to iterate over all the controls to find the Tag collection, then do something with it.
Problem is that since I know the name of the tag, I just want to do something with it (height, visibility, etc). But I can't figure out how to reference a tag directly without iterating over all the controls.
eg. file 1 => send to a@hotmail.com file 2 => send to b@hotmail.com file 3 => send to c@hotmail.com file 4 => send to d@hotmail.com file 5 => send to e@hotmail.com
send them at the same time, but different address. Can they do that? I think that it cannot be done this way, right. I think that it only send a file at a time, right. Please let me know. Thanks. Thanks.
I have now opened it in Design view and wish to adjust the width of the controls - text boxes etc but when I try to adjust one, all of the text boxes in that column are adjusted.
The query Q_CurrentJobs is a list of jobs to send to the tech for today. (These are open jobs queried).
I need to run this query on the report (R_CurrentJObs) and send the report to each tech based on their individual jobs. The jobs are connected to each tech by its TechID.
The report list the job information for each tech (JOb number, Location details etc.)
When I click the button to send the email. I want to email each tech individually based on the Q_CurrentJobs query using R_CurrentJobs. (DoCmd.SendObject)
I want to add to every row a combobox with a list of towns depending on the value in the field Country ID.how do I filter the comboboxes individually based on a value in another field on the same row?
Hello, I am new to Access and I am having a problem with something. I have a query which returns, among other things, dates. I need to organize (group) the dates into weeks. Does Access have a function which could do this? Or some other way of grouping the dates? Or How would I got about writing a function in Access? This is done in a Pivot Table right now, and I just use a calculated field which is the date / 7. This gives some god awful numbers (like 5944), and not anything like what I want. I would want the function to return something like, "Feb. 5, 2005 - Feb. 12, 2005". Any suggestions?
So I have a project where I have to get employees to use infopath to fill out details in a form and then submit it, the form gets saved in an access database. I have all that setup and it works perfectly. I uploaded the forms on a sharepoint site and when I open them up from my computer they submit properly to the databse. However when I open up the forms from another computer they don't submit to the database. I know why it does that (because the databases are not on every computer). I can't however figure out a way to solve this problem so ANY help would be amazing. I was thinking to have the access databse on sharepoint as well so the users can download it but won't that just make it that every single computer will have a different saved database? This problem is driving me crazy. Thanks in advance for all the help.
I'm using the following in a query qwhich allows me to enter the week number as the criteria:
DatePart('ww',[Visitdate],2,2)
This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
I have two columns. In one of them I have daily dates and in the other I have some numbers. What I'm trying to do is to sum the numbers weekly by entering this in the field of a query:
Week: Format([Date];"yyyy ww")
The problem is that when I go to datasheet view, it is sorted wrong. It looks like this:
I have a database that I am using to report metrics based on seasonally available data. What I am running into is the problem that the seasonally entered data doesn't start on the same date every year.
An example: I have data that was entered into my database last year starting July 26, 2004. This is considered part of week 1 of my season and I want to compare that data to records that started being entered on August 15, 2005. Which is week 1 of the 2005 season.
Records include the following information: id, date, other irrelevant details
Does anyone have any ideas on how I might be able to accomplish this? Thanks in advance for your help!
Hi there, Was there a recent update or something that may have affected a Union Query in MS Access 2003 (operating system XP Pro, all current updates installed)?
In our database we have a union query that display available sessions and time period of availability, this is done by identifying the session with **available** at the start of each line for some reason it works on some computers and not others.
Does anyone know anything that may effect this code in operating 100% of the time?
thanks in advance.. Colin
The SQL code used is a follows:-
SELECT '** available **','', '',format(A.session_end, "dd/mm/yyyy") as START_DATE,format(A.session_end,"hh:nn") as START_TIME, format(B.session_start, "HH:MM") AS END_TIME, int(DateDiff("n",a.session_end,b.session_start) / 60) AS Expr1, (DateDiff("n",a.session_end,b.session_start) - expr1 * 60) AS Expr2,a.session_start FROM session_last AS A, session_last AS B WHERE (((B.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])) And (B.session_start)=(SELECT MIN(C.session_start) FROM session_last C WHERE C.simulator_code = A.simulator_code AND C.session_start BETWEEN (([Forms]![Bookings]![StartDateInput])) AND (([Forms]![Bookings]![EndDateInput])) AND C.session_start > A.session_start )) AND (((A.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo])) AND ((A.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput]))) AND ((B.simulator_code)=[A].[simulator_code]) AND (([B].[session_start]-[A].[session_end])<>0))
UNION SELECT '',C.customer_code, C.session_type, format(C.session_start, "dd/mm/yyyy"), format(C.session_start,"HH:MM"), format(C.session_end,"HH:MM"), int(DateDiff("n", C.session_start, C.session_end) / 60) AS Expr3, (DateDiff("n", C.session_start, C.session_end) - expr3 * 60) AS Expr4,c.session_start FROM session_last AS C WHERE (C.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])) AND (((C.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo])) ORDER BY 9, 4, 5, 1;
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
My main query has a list of events with the corresponding dates that they occured. I want to create a second query that shows Monday’s date of the current week and the Monday date of the preceding 51 weeks.
How do I get my second query to show the dates for 52 consecutive Mondays starting with the current week (Monday date) and going back 51 weeks.
Then I want to create a third query by combining the first two such that the with the dates and events main query are displayed and grouped by the Monday dates for the year query.
I am trying to create a report which will show the last twelve weeks of orders by client. In the table I have an Order Date and of course the bill to clients. I tried to create a Crosstab query for the wizard and can get month by month but need the last twelve weeks, week by week. Is there a way to do this?
I am trying, and getting nowhere...so would appreciate your help... to create a query where records with dates from 8 weeks, or two months ahead appear in the query.
That is I have contracts ending in two months in my table and I want to run a query on who I should call now to renew contracts.
I have a contract end date in table.
I have tried >=DateAdd("m",2,Now())<=DateAdd("m",2,Now()) only to get all sorts?
>=DateAdd("d",60,Now())<=DateAdd("d",68,Now()) only to get all sorts of things appearing?
Anyone that could help would be great. I have contact info in a TBLCONTACTS and linked to TBLMOBILES via COMPANY_ID
I've got a database used daily by 4 users. It's split into a frontend (10MB) with all the forms/queries/reports/vba and a backend that's just tables (170MB), and the users access the database from a network drive. All additions through the forms are logged to a text file, and at the end of the day, a report is run that prints the day's work to a PDF. The database / workflow has been stable for the last few years, with only minor edits to the code, and no programmatic changes in the last 3 months.
Today, after running a compact-and-repair, I realized that the database contained no entries added within the last 2 weeks. I checked my log files, and sure enough I see that all of my records were at some point added to the database. This is supported by the fact that I have PDFs for every day in the last 2 weeks that show exactly what was done (roughly 30 new records/day).
My first guess was that compact and repair had corrupted the database, and knocked out a chunk of records. Fortunately, I've got daily backups, so I started restoring to yesterday's database. At this point I found that the records were missing from there, and from every backup from the last 2 weeks. Now, it's possible that my backup solution (logMeIn backup) is hosed, but the the log files are getting properly restored by the backup, which leads me to believe the backup is working. So, somehow these records were never saved in the database, yet they magically appeared in my end-of-day reports?
I thought maybe I was getting stuck in some state where the database went read-only and the edits were getting stored in memory but never written to disk, but that doesn't make sense as we occasionally restart the database during the day for other reasons, and the end-of-day reports are always complete, which knocks that out. Having restored to a prior version of the DB, I attempted to make changes / add new records and they appear to be sticking, but I find my faith in Access rather shaken, all the more so because I haven't a clue what went wrong before.
My database includes two separate dates related to a vehicle purchase and default payments resulting in repossession. I need to find out how many weeks passed between those dates.
Example: 03/14/12 Date of automobile purchase 06/06/13 Date of last payment before default
How many weeks in between? And then I need to average all of those figures so my company can see the usual week-term time frame that our customers are defaulting and getting repossessed.
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
I have a textbox in a form. What I want it to do is to display the following weeks Monday date. Sounds simple but requires you to know the Monday dates in advance ...
I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.
How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?
So Query1 returns: 25/08/2014 08/09/2014 15/09/2014
while Query2 returns: 01/09/2014 08/09/2014 15/09/2014 22/09/2014
I want query3 to return: 25/08/2014 01/09/2014 08/09/2014 15/09/2014 22/09/2014
