Quick one for you geniuses out there!
I would like a command button to print records from a select query displaying dates for the past 2 days but not for todays date.
e.g. today is the 29th, I want to print records for the 27th & 28th.
Data is being recorded hourly and kept for 2 months so there will be new and old data that I don't want printed.
Thanks for the help!!
I am totally new to Access and this is my first database.
I have a database for volunteers in which one table is basically a calendar. In this table I have fields for the month, day of the week (i.e Monday) and day of the month (i.e 21st). There is also a filed called "Special days"
Sometimes I may want to produce a report that selects only certain days such as Saturday, Sunday and other days marked as "special days". Another time I may also want to produce a report that selects other days.
At the moment I have a "test filed" in which I place a 1 for those days i want to select then I choose all those with a 1. This is obviously a very poor construction.
Can anyone offer me a more advanced method?
I have a database which records time spent on various projects and I would like to be able to calculate the time spent on any particular project between two selected dates. The report and the query behind it already works to display the time accrued on all the dates of a project, but I want to be able to break it down to small ranges of dates
I have a form on which the user selects the start date and the end date. How can I apply this calculated information as a criteria in a query so that I can produce a report with the relevant information on it.
I already have the report and the date selection form designed. The dates selected go into two unbound fields which have a date format.
I hope someone can help me.
Rob
Dear All:
I have created a database to keep track of transcripts that are requested. This database has their names, ID number, date requested(With a pop-up calender), transcript type requested(This is a combobox where the choices are "Official Copy" and "Student Copy") and address.
So far, it works great. What I wish to do is run a report that I can choose the begining date and ending date for a specified month to see the total "Official Copies" and "Student Copies" for a given month.
Any ideas out there?
Hats off to Colm!
Regards,
Dion
I have a table containing a list of dates in "MMM YYYY" text format from "Jan 2010" to "Dec 2020".
I would like to restrict the list from the first record (Jan 2010) to the current month using today's date (eg in this case Dec 2013).
I know about
Code:
Format(Date(),"mmm yyyy")
And have tried the criteria
Code:
<Format(Date(),"mmm yyyy")
But doesn't work and haven't got much further....
I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.
I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.
So, I have three tables.
Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost
Table: t_AllMonths
Fields: MonthStartDate
Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate
Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.
My sql looks like this:
SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);
I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."
The input form of the database have one common table containing data for all parameters for row source. As all data was in one table had one ID. My problem is I am trying to run reports from a form after selecting dates and parameters.
For selecting parameters I created dynamic combo box. For dynamic combo box I needed ID and description of the parameters. So I created separate tables for each parameter with ID and Description column. Which ran me into the problem that when I run report based on main input form which has different row source table and my report form has different row source tables and reports doesn't run when I base my queries on the report form.
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
Hello All,
I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!
From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.
I’d be very grateful for any advice/guidance.
Bomac.
I have a table with customer details, probs over 3000 customer, and i need a method of selecting the customer in a form by their surname. Currently i have a combo box, but as you can imagine, this is not practical with so many records. anyone think of any other way to do it?
View 4 Replies View RelatedHi,
I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales).
It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
Any ideas?
Slighlty confused,
Rosxx
Can anyone tell me why sometimes when i'm using access all of a sudden when i click in cells, control boxes etc that i select everything and i cant position the mouse with a string of text with selecting all of it.
VERY frustrating.
cheers,
Spinkung :confused:
hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!
My problem is as follows:
I have a form for my products and a subform for the suppliers, each product can have many suppliers.
I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(
can anyone tell me how to select only the supplier with the lowest supplier price for each product
Cheers guys
Good Afternoon,
I am currently trying to create a query that will allow me to do the following:
Prompt the user with a pop up box, asking:
Please enter start date
Please enter end date
It will then select everything from my table between those two dates (this works), and then it will ignore all those items that have a time after 7am or before 7pm (this works), but I need it to also select all entries that fall on a saturday or sunday. Now because my raw data comes in the form of (australian dates): 7/09/2005 9:39:10 AM, I am using an update query to split the field into two fields: DateFrom and TimeFrom (both are date/time fields). I then need to create another field called Day, which takes the data in DateFrom and converts the date to dddd (using Format(Date()) i assume), giving me a field with the day name in it, ie: Sunday.
I can then use that to differentiate between weekdays and weekends.... as I use MySQL and PHP more often than I do access, I have no idea how to do this.
So in short:
How do I (this is what i think i need):
Select * from tblData WHERE Day!=Sunday OR Saturday AND TimeFrom > 7:00pm OR TimeFrom < 7:00am
Select * from tblData WHERE Day=Sunday OR Saturday
Both of those selections, will of course need to be performed within my date constraits specified by the user.
Am I making sense?
Hi guys,
Still trying to get used to this Access lark.
A table contains a column with numbers i.e - 1,1,3,4,2,2,2,3,4,4
If i just want a query that shows one of each i.e - 1,2,3,4
what do i need in the criteria line?
Thanks in advance
Hallo Everyone,
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
aravind.s
I am working on a query to select the most current value for a given record
based on dates. The record has an associated cost for a given year say 2000.
For years 2001 to 2006 there is no cost. I want to use the cost in 2000 for
the most current cost even though my date criteria specifies say 2002. Can
this be done within the select query?
Thanks
Tony