I made a basic Seniority List for my work place using a simple query that pulls info from a main Employee Table. Easy enough using just a simple query with a sort. Problem is, about a dozen or so of the Employee have left and since been rehired. Their Seniority is based on their Rehire Date and not their Original Hire Date. In the main Employee Table I am pulling this info from, there are two Date Fields...
[Hire Date]
[Rehire Date]
Is there a way to build a query where it can check both fields, use the more recent date, then copy the "winner" in a 3rd field?
Ok let me explainI have a list of 50 people who may have accessed events A,B,C at different times but what I want to do is to put a date in my form, run the quaery which will list all people who have Attended A,B or CWhen I do my normal query, I put the fields in, but if I put a date filter in Event A, it will not show up any person who may have attended event B but not Event A.I thought about running an append query but this would mean running the data several times with possible duplication of client names.Ideally I would like to have my report to showDate: 31/07/2006Name A B CK.Brown Y YJ Blogg Y YK Smith YK Ellum Y Ythen I can just use a count at the bottom :)The methods I have tired will filter event date A, but will not show J BloggIf I create another append for Event B, and append that data to a table, then I will have duplicaion of K Brown.. Any Help Appreciated.Last thing - when I run the append queriy and get my duplications, the fields where I had a Y/N option - if it was YES then I get a -1 instead of 1 ??? Why? and how do I make sure that when appending that the Y answer is a 1 not a -1
I have a query that reports on records according to date. The criterea line of the date field has [Enter Date]. I have confirmed that there are dates from 8/1/06 through 8/10/06 in the table, but when I enter the date 8/10/06 the query returns no records. But it does return records for all other dates (8/1/06 through 8/9/06 inclusive). So then I tried to make the date a range:
Between [Enter Start Date] And [Enter End Date].
I entered 8/9/06 and 8/11/06 in these fields and there in the report were the records for 8/10/06. Then I went back to [Enter Date] as criterea and entered 8/10/06 and no records are returned.
I have a process that lifts a highlighted date from a List Box and puts it in the Criteria of a query. The process manages to move the numbers from one place to another, but ends up giving me a data mismatch in the query. The process is as follows
Private Sub Command8_Click() 'Set it all up for Panel Meeting selection Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String
[Code] ....
The Immediate debug shows...
SELECT * FROM tbl_Panel_Meeting_Dates WHERE tbl_Panel_Meeting_Dates.PanelDates IN ( '1/07/2013' );
I'm pretty sure (althiough always stand to be corrected!) that I need it to come out as
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
Ok I have a tbl with the following fields: Name Date Activity Hours
I have a form with these fields on it and two additional fields: Total Hours Hours this Month
Those fields need to do just what they say, calculate the total hours and calculate total hours for the current month only.
Here is the code that I have thus far, that is not working: SELECT tblVolunteers.Date, tblVolunteers.Name, Sum(tblVolunteers.Hours) AS tot FROM tblVolunteers GROUP BY tblVolunteers.Date, tblVolunteers.Name HAVING (((tblVolunteers.Date)=Month(Now()))) ORDER BY tblVolunteers.Name;
If I take out the "=Month(Now)) portion I can get the total hours, so that part is done, I just figured that out..... I get nothing back on the above sql statement.
Hi! My question is similar to Hondasteed earlier today but a little different. I have a database that has 6 date fields. I would like to develop a query where the user enters a start and end date and the query checks all six of those fields for the appropriate dates and shows only those records who have dates in that range. The dates would change each month as we ran the query again. I would appreciate any help as this has really had me stuck and I'm guessing it's a simple solution. Thanks!
I am trying to finish off my database with this last query. I have two fields named date_to and date_from, within a table tbl_non_avail . I need to show people who are available not between the from and to dates.
I have a parent table [Case] and a child table [Action], in a one-to-many [Access 2003].
The parent has an open date and a closed date.
The child has a received date and a completed date. The child may contain more than 1 record that matches the parent.
Date fields for activities that have not yet happened are blank.
A typical example might be one parent and two child entries for a total of six dates fields.
I am after only a [single] most-recent action date of the six [there could be a tie for that most recent date, and then there would be two records returned in the result].
I am working for a table that includes a list of every parent record with the name and date of the most recent, or latest activity date.
I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:
1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.
2. When I try to access this value using DLookup on a form I get "#error"
The database I am working with contains several different tables and a variety of information. I need to create a query that accesses information from two different tables. Though the tables contain other fields, the fields I am most interested in are:
Table 1: Protocol Information "Protocol ID", "Title", "Expiration Date"
Table 2: Regulatory Submission "Protocol ID", "Description of Submission", "Submitted By", "Date of Submission".
The way the data is stored, each protocol has a Protocol ID, and there is only one record per protocol in the "Protocol Information" table.
However, each time these protocols are submitted to our review board, a log is created in the "Regulatory Submissions" table.
I need to generate a query that pulls only the most recent submission date, so that the query output would look like this:
"Protocol ID", "Title", "Description of Submission", "Submitted By", "Date of Submission" (Where this is the most recent one), "Expiration Date"
From other forums and posts, I tried to create an SQL aggregate function, using Max or Last to get the most recent date. I was successful, but only if the query contained just the "Protocol ID", "Date Submitted", and "Description of Submission" fields. Adding any more, or creating a query to use the outputted data didn't work (it could be that my second query was not written correctly.) I am very novice at creating complex queries, and at SQL.
One additional complication: There are two values in the "Description of Submission" field of the "Regulatory Submissions" table that I am not interested in. They are "Adverse event" and "AE Summary Log". I can successfully filter them out using a criteria expression, but integrating all of these pieces has not been easy (or possible, yet).
I'm out of ideas. This is really complicated, and I apologize. Any assistance would be greatly appreciated.
I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas? Regards:confused:
I need to know a query to pull information from two date fields into one. I have a BuriedStartDate field and an AerialStartDate field. In a third field I have PlacementStart. I would like to put the date into the PlacementStart field which ever is the oldest date bewtween the BuriedStartDate and AerialStartDate. It has been awhile since I have done any queries on Access 2007. I am completely self taught in Access.
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name Start Date Midpoint Date Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1) Table: Default as only one table Total: Expression Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate] Table: Default as only one table Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM! Show: Checked or Unchecked makes no difference Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate] FROM tblCommsLog WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I have come up with another one that may be easy for you.
I have a form that has a list box with two fields in the table. Call the table: STATE TABLE1 Call the first field: STATE NUMBER 1 Call the next field: STATE NAME 1
When I access the list box on the form, I have it so I can see the States Name(STATE NAME1) and I click on it and the (STATE NUMBER 1) is place in another table called: INVENTORY2 in the STATE#2 field. That works all well and good.
I would like to be able to continue this, at the same time I select the (STATE NAME 1) on the list box. I also want to place (STATE NAME 1) into the INVENTORY2 TABLE in another field call (STATE NAME2)
I was going to write an expression to do this but had trouble with finding the STATE NAME 1 field.
I am sure there is an easier way.
PS This table is being used in another application and there is no way to modify any of its field at the time of the transfer. All the fields need to be populated at the time of the transfer.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID Employee Name Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7 ....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
Working in MS Access 2010. Data must be entered and updated in Access only. Trying to figure out if it's even possible to auto populate dates in multiple fields in a form that are triggered when I enter ANY date in a primary date field on the form. I have close to half a dozen fields I'd like to do this for but here are a couple of the fields I'm working with:
Re Insp Date 90 Day Notice(Form field to be triggered and show a date 90 days prior to the trigger date) Re Insp Date 60 Day Notice (Form field to be triggered and show a date 60 days prior to the trigger date) Re Insp Date (Trigger date)
What I'd like to do is enter values in the "Re Insp Date" field (in the form) and have it trigger/auto-fill/auto-populate the "Re Insp Date 90 Day Notice" so that it shows the date 90 days prior to the "Re Insp Date" and the "Re Insp Date 60 Day Notice" so that it shows the date 60 days prior to the "Re Insp Date"........all this, and have it auto-update the queries and tables its linked to.
I've tried looking online and in instructional material to see if there is a formula, equation, macro, an expression or VBA coding that I can use to execute this but am having no luck.
The MAIN OBJECTIVE is to have the the linked query and table update automatically. Possible??
I have tried doing =([Re Insp Date])-90 but no dice...
