Queries :: Automate A Harder Date Field
Mar 12, 2014
I run a report based on dates I enter (see below)
>=[Date you wish to view DD/MM/YY] And <=[Enter the Date after DD/MM/YY]
I would like to automate this if possible, the dates are enter are always a set pattern
If its Monday I enter the previous Thursday and Sunday
If Its Tuesday I enter the previous Sunday and Monday
If Its Wednesday I enter the previous Monday and Tuesday
If Its Thursday I enter the previous Tuesday and Wednesday
If Its Friday I enter the previous Wednesday and Thursday
I dont run rthe report on weekends
View Replies
ADVERTISEMENT
Mar 12, 2014
I run a query daily and have to alter the date in the design view (as below).
>=#01/04/2013# And <#11/03/2014#
All I want is for the query to run 2 days behind, so for example today is the 12/03/2014, I use the above, and then daily change this every day.
View 1 Replies
View Related
Mar 18, 2014
The statement below is a snippet from a union query and is repeated 6 times within the SQL Statement for various reasons, I have to manually change this every month, again is there i tiny bit of code I can insert to replace the following so that it automatically runs the previous months data.
WHERE ArrivalDateTime >= 'February 1 2014'
View 5 Replies
View Related
Nov 10, 2005
How would I combine all these instances under one After Update Click:
Private Sub lstAgreementType_AfterUpdate()
If Me.lstAgreementType.Value = "BN" Or Me.lstAgreementType.Value = "BA" Or Me.lstAgreementType.Value = "BT" Then
Me.txtSenateAandCDate.Enabled = False
Else
Me.txtSenateAandCDate.Enabled = True
End If
Private Sub lstAgreementType_AfterUpdate()
If Me.lstAgreementType.Value = "BN" Or Me.lstAgreementType.Value = "BA" Or Me.lstAgreementType.Value = "BT" Then
Me.txtSentateTreatyDoc.Enabled = False
Else
Me.txtSentateTreatyDoc.Enabled = True
End If
Private Sub lstAgreementType_AfterUpdate()
If Me.lstAgreementType.Value = "BN" Or Me.lstAgreementType.Value = "MN" Then
Me.txtRatsExchanged Date.Enabled = False
Else
Me. txtRatsExchanged Date.Enabled = True
End If
Private Sub lstAgreementType_AfterUpdate()
If Me.lstAgreementType.Value = "BN" Or Me.lstAgreementType.Value = "BA" Or Me.lstAgreementType.Value = "BT" Or Me.lstAgreementType.Value = "MN" Then
Me.lstDepositary.Enabled = False
Else
Me.lstDepositary.Enabled = True
End If
View 2 Replies
View Related
Apr 28, 2014
We have a memo field that folks may add to on different days. Additionally, different users may add notes if the person who started to work on the transaction is out of the office. My manager would like to add a way to include the date and initials of the person that added a new memo automatically after they add a memo. Currently, we don't track user login so I'm assuming we would have to in order to get their initials.
View 3 Replies
View Related
Jan 31, 2008
I have a reasonable large dbase with some 12 tables and some 40 Queries.
In three of the tables, there are fields with dubious names like AA1, CD-3, 227, etc.
I want to change these field names to some more appropriate names like : Testblok , Cspeler, Jaarnummer, etc, etc.
In doing so, I will have to go thru each and every Query ( and at a later stage forms/reports) to change the fieldnames in that query corresponding to the changes I made in the initial table field name.
Would there be another way of doing this, i.e. is there a routine for changes made in the fieldnames, being “automatically updated in the table bound query.
View 6 Replies
View Related
Dec 18, 2014
I want to automate a query so when the user views it they see only the columns that have data. Columns that do not have data are not there.I am using MS 2007
My Access tool is setup to import data into tables and then the user views the data imported. However, if their's no data (and no column to import) I want the query report to not include the column.
View 8 Replies
View Related
Apr 13, 2013
I have 2 queries to check if there is any "double quote" character in any of 12 month columns in a month table of 125K records. I use 2 queries since the maximun criteria in a query is only 8. So I use 8 criteria in the 1st query then use 4 criteria to check the remaining months in the 2nd query. The month table is refreshed and created every month. The 12 month columns are changing from month to month since they are -13 month to -1 month when the month table is created. For example, if the month table is refreshed and created in May 2013, then the 12 month columns are "May 2012", "Jun 2012" .... and "Apr 2013". If the month table is created in Jun 2013, then the 12-month columns are "Jun 2012", "Aug 2012" .... and "May 2013". The end user has little knowledge of Access Databse so he seems confusing how to update the 2 queries on new month table.I am trying to see if there is a way to use 12 criteria in just one query so the end user only deals with one query's update.If there is a way to automate/improve the update of the queries/query, then it would be the best.
View 9 Replies
View Related
Jun 29, 2013
I've created an outlook task to export a common query I do every day, is there a way to automate it so it will happen automatically without me pressing Run?
I use office 2007 on a company laptop with tight network restrictions (so no Windows Task Scheduler)..
View 1 Replies
View Related
Oct 18, 2013
I've been using Access 2007 to run queries on a database where we eventually export results as separate Excel spreadsheets for individual clients. The process is quite involved, using queries to change fields from code letters to words and splitting the database up into different client tables, saving the tables under date order and with different client codes.
I now need to pass this role on to colleagues, so need to make everything as straightforward as possible.
I had thought to use a Form as the user interface, with a minimum number of buttons, however I need either the system or the user to amend the date for the initial table, then to use this new table and run a series of standard queries on it, then produce the separate tables.
I don't think I can just use macros behind the buttons, because the database name is changing each time.
I assume some parts will be too tricky to automate - it will be necessary for colleagues to follow instructions instead.....
View 2 Replies
View Related
Apr 26, 2013
I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.
View 11 Replies
View Related
Jun 8, 2013
I am trying to deduct a datetime field from a date field to decide how many days are in between. It doesn't let me. So do I need to convert the datetime field to a date field first? How would I do that?Or is there a function I can use to calculate the date difference?
View 2 Replies
View Related
May 18, 2013
I am trying to track vegetables that I will be planting in my garden. I will plant some vegetables. I have a StartDate and HarvestDate and Estimated HarvestDate Fields. I also have a field named GrowDays. This field states how long it takes for each vegetable to mature i.e. 75 days or 58 days or 42 days etc. etc. What I want to do in a query is to automatically add the number of days from the GrowDays field to the StartDate and come up with the Estimated Harvest Date. If I plant a Tomato on April 13th and it takes 75 days to mature then I want the EstimatedHarvestDate field to reflect 6/27/2013 which is the StartDate + 75days. I am trying to figure out how to do this when the GrowDays field changes numbers. I can use this "Estimated HarvestDate: DateAdd("d",+75,[GrowDays])" except this only works if the GrowDays Field is 75. I need to be able to accomplish this no matter what the GrowDay field says.
View 2 Replies
View Related
Aug 21, 2014
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last',
IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-'))
AS IncidentYear,
Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days
FROM (tbl_2c_Acc
LEFT JOIN tbl_Value_Proposition
ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No])
LEFT JOIN tbl_Vehicle_Type
ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No]
GROUP BY IncidentYear;
View 8 Replies
View Related
Apr 14, 2014
I have the following fields
[safetycheckdue] is a date field
[safetycheckfreq] is a numeric field
i need an update query to update the field [safetycheckdue] with the value in [safetycheckfreq]
when i run the query, it just shows the existing value with adding the numbers of days to the safetycheckdue date.
View 1 Replies
View Related
Jan 9, 2014
I want to add one year onto a date, I could do this in a query or just add it on on a textbox in the form?
Field Name is IncidentDate from table tbl_incidents
View 4 Replies
View Related
May 24, 2014
I need to show the interval between dates. The dates are all held in the same field.
A/CDate
52601-Mar-14
52601-Feb-14
52601-Jan-14
View 5 Replies
View Related
Jul 22, 2015
I have a query that is pulling a date from a Form. In my Query Criteria, I can put:
Code : >=[Forms]![frmAdhoc].[Date]
or
Code : <=[Forms]![frmAdhoc].[Date]
or
Code : =[Forms]![frmAdhoc].[Date]
And it works fine, but I don't want to hardcode the ">=", "<=" or "="
I would like the user to be able to choose ">=", "<=" or "=", from another field on the Form, so I am trying to code it on the query like this:
Code:
IIf([Forms]![frmAdhoc].[Variable]=">=",>=[Forms]![frmAdhoc].[Date],IIf([Forms]![frmAdhoc].[Variable]="<=",<=[Forms]![frmAdhoc].[Date],IIf([Forms]![frmAdhoc].[Variable]="=",[Forms]![frmAdhoc].[Date])))
But it isn't working for the ">=" or the "<=". It just gives me a blank result.
View 2 Replies
View Related
Feb 22, 2014
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.
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.
View 2 Replies
View Related
Sep 10, 2014
I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
View 14 Replies
View Related
Aug 9, 2015
condition formatting a date field in access.The query used for this field produces a date or "NA" based on the formula below. so when the date shows up as "NA" then i have set the condition formatting of the cell to grey and this works works well. but when it shows up with a date it doesnt format to a grey.the formatting pane has these two expressions
1. Q_Induction_Date = "NA" then grey the cell
2. Q_Induction_Date < DATE() then grey the cell and this DOESNT work. i have also tried the function Now() and that doesnt work either.
Formula i use in the query is
Q_Induction_Date: IIf([y].[INDUCTION_NA]=-1,"NA",IIf(IsNull([induction_date]),"NA",Format([induction_date],"dd/mm/yyyy")))
View 4 Replies
View Related
Mar 4, 2014
Is it possible to use date dependent field names in a query?
I have this table and there are columns based on year and month (formate of names is: 2014,01) I need to select the columns of last month and then 4 months further down in history.
Is there a way to do that as the information in those columns I need to make calculations and graphs with.
View 14 Replies
View Related
Oct 28, 2014
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator
FROM MatterContactsMade
GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator
HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator]))
ORDER BY MatterContactsMade.MatterId;
View 1 Replies
View Related
Jan 27, 2015
I have a field on a table that is a DateTime format and it needs to be that way. I am trying to query that table for all the dates to use in a combobox but I only wat the date not the time. The problem is when I format the field to just date I can't sort it anymore as a date and I can't group them. I still get multiple lines in my combobox because there multiple times for the same day. what is the proper way in a query to extract just the date from a field and still sort it as date ie 1/12/2015 is greater than 12/15/2014?
View 8 Replies
View Related
Mar 26, 2013
I am trying to retrieve data for a particular record.
When Project field matches a certain project number I want it to pick the record with the latest date in the date field field to select certain data fields(Owner & Rating) from that record.
Below is my attempt. However the problem is that displays all records with that project number and not just the record with the latest date.
Code:
SELECT [Combined PRB Roadmap].ProjectNumber, Max([Combined PRB Roadmap].DateField) AS MaxOfFDateField, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
FROM [Combined PRB Roadmap]
GROUP BY [Combined PRB Roadmap].ProjectNumber, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
HAVING ((([Combined PRB Roadmap].ProjectNumber)="NR-4237"));
View 2 Replies
View Related
Jun 19, 2013
I have a table ("tbl_idq_all") with a text field for product codes ("scode"), a date field (dd/mm/yyyy) and a quantity field ("po_qty"). This table therefore holds future receipts of stock for products.
What I am having trouble doing is create a running total of [po_qty] based on [scode] and [Date].
A good example is stock code 10254. This has a quantity of 40,032 arriving 01/06/2013 and a quantity of 30,096 arriving 01/09/2013.
Therefore the running totals should read:
scode | Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 40032
10254 | 01/09/2013 | 30096 | 70128
As you can see from the attached DB I have 70128 repeated twice in the RunningTotal column.
View 8 Replies
View Related
