Entering Dates
Oct 26, 2007
I need to create a query so that I can run a report that will show records entered between certain dates. For instance lets say
tblTMS has fields IDNo, DateReceived, Name, Address
tblWeeks has fields StartDate, EndDate, WeekNo
(all dates are in the format dd/mm/yyyy).
I want the user to be able to enter a date (possibly by a popup txtbox when trying to run the report)and for it find what week number it relates to in tblWeeks and also what records from tblTMS has entries in the TblTMS.DateReceived between the tblWeeks.StartDate and tblWeeks.EndDate of that particular week.
So the output of the query so show records in the following order.
tblWeeks.WeekNo
tblTMS.IDNo
tblTMS.Name
tblTMS.Address
I've been trying to do this but for some reason can't figure it out. I have done this kind of thing before but now can't get my head around it.
Any help would be greatly appreciated
View Replies
ADVERTISEMENT
Nov 30, 2006
hi everyone,
i have a query that is used to generate a report. the report is based on date so i enter the following into the date criteria
Between [Please enter the date you wish to start the report] And [Please enter the date you wish to end the report]
it works fine however the problem is that my date is set up as medium date format which means 01-01-06. if i type 01.01.06 then it returns NO results.
How can it be avoided so that you can type ANY format of date and it will return correct results.
thanks
View 9 Replies
View Related
May 12, 2014
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.
View 1 Replies
View Related
Aug 28, 2013
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 Related
Apr 9, 2015
I 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?
View 4 Replies
View Related
Sep 7, 2006
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.
View 3 Replies
View Related
Jul 8, 2014
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
View 2 Replies
View Related
Nov 15, 2011
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?
View 10 Replies
View Related
Aug 29, 2006
Hi There,
I have built a very basic DB and am running it alongside our existing Excel system of recording customer data.
I have transferred a lot of data from the Excel spreadsheet to the DB Table using Cut & Paste. For all future incoming data is there any way I can just enter it onto the Excel spreadsheet and it is automatically transferred to the DB table or am i consigned to entering two lots of the same data?
Best Regards
Keith:(
View 4 Replies
View Related
Jul 13, 2005
I have a form in which the user must put in a description in a text box. Within the description, the user will have to reference a PDF file that he will put a hyperlink to. Is this possible for him to do?
View 1 Replies
View Related
Jul 13, 2006
Hey,
I am creating a form and one of the fields are 'Area Code'. Input will be along the lines of 02 and 03, but whenever I do this it deletes the 0, just leaving a 2.
How do I make access leave the 0 in the field?
Thanks.
View 5 Replies
View Related
Apr 7, 2008
Heloo all,
I have a table, and a data access page for data entry.... is it possible for me to add a mulltiple records at a time.... please help
View 1 Replies
View Related
Jan 24, 2005
I have a form with a sub-form
eg
Purchase Order with main details on (Po Number, Supplier etc)
with a sub form carrying the line items to be ordered.
Table PO
Form PO
Table POSUB
Form POSUB
When entering main order details into Form PO, why do the fields in the related table(Table PO) immediately get populated when the the focus gets transfered to the sub-form (Form POSUB). with users quiting the database illegally (not by the cancel records button) the result is unwanted records in the Table PO.
What I want to do is complete the input fields in the main and sub forms without any records being commited to the tables until the "Save Record" button is pressed.
Thankyou
View 2 Replies
View Related
Mar 22, 2005
I have a "Client" table which has a "Date of Birth" field and the problem I am having is on in the data entry form the users type in "2/25" instead of "2/25/1965" on for the most part that's OK. But if the client DOB is 1930 or before then 2/25/2029 comes up.
I have found that the date format is tied to the Windows Regional settings and we are working on a network that doesn't allow me to adjust the settings.
I have tried to put a validation on the table for the field but then that makes it so you HAVE to enter something and can not leave that field blank if you want.
Any ideas?
View 1 Replies
View Related
Jun 7, 2005
I have a form [tblStock]with a combobox bound to the Primary key [ContactID] of a table [tblContacts].
Also on the form is a subform[subContacts] bound to a query that is based on the value of the combobox
on the form. So that the details of the combobox[ContactID] is shown in the subform [subContacts].
The trouble is that i dont seem to be able to add a new ContactID in the combobox and fill in the rest
of the details in the subform.
Problem 1 is that the focus moves to a textbox that i have on the form[tblStock] when
i enter the first character into the combobox.
Problem 2 is that when i continue to click into the combobox and type the ContactID that
i have typed does not get ammended to the table[tblContacts].
I am very new to Access, it probably shows, but this problem is driving me mad....Anyone?
View 5 Replies
View Related
Jul 4, 2005
I've been working on a database (attached) for a health trust. I think the relationships are right but I'm having a problem entering data. The subform shows the correct data but I can't figure out how to enter data using a form.
Can someone point me to a tutorial - I have searched the forums - honest!!
View 8 Replies
View Related
Feb 21, 2006
I have a db with the following fields: ID, Customer first name, customer last name, account number, date, time, score1, score 2.
My problem is this: We are running a promotion in which the customers receive a score. This score must be entered correctly as we are highly regulated, however the users are constantly fat fingering or miss typing the data. I want to force the score 1 and score 2 fields to match before the record can be saved.
Any suggestions on this would be greatly appreciated; I have to go in every night to correct these errors manually, which defeats the purpose of this db.
View 14 Replies
View Related
Mar 17, 2006
I have couple of forms where...The users enters a name of the river where he/she was fishing in, I also have an option where the user can browse for previous entered rivers.I am wondering how this works, how do I let the information entered go automatically into the browse section and how can I prevent from duplicates will appear in the browse for previous entered rivers. thanxp.s is this something that I will work with in the table it self or can I change this in the form ???
View 1 Replies
View Related
May 5, 2006
Hi,
I have a form where you can enter the work you did in a day,
There are many users, each entering data for a single date.
What I want my form to do is, when a date and name is picked (from 2 boxes on the form) it will bring up the record relating to that person for the day.
If there is no record for that person on the day, this will be the first data the person enters.
So for example the form Hours:-
name mcbiyms2 (combo box) date 05/05/06 (text box:- =Date() )
Work Type 1 [ ] Time [ ]
Work Type 2 [ ] Time [ ]
So if somebody has already enetered some data for that day, it will bring this up to add too, if not it will be a new record to be entered.
Does anyone have any ideas?
View 1 Replies
View Related
Jul 25, 2006
I can't seem to find a way to solve this problem:confused:
I'm working on a database to run my window cleaning business. I have a customers table which contains all the details for each customer ie address, name etc.
There is also a Jobs table which will contain a record every time a customer's windows are cleaned.
There is a one to many relationship between them ie, 1 customer to many jobs.
I would like to create a form which would list all the customers which are due to be done and then be able to create a job record for each customer in the Jobs table, showing that it's done, with the click of a button.
For one, I can't get both tables to display on a continous form because of the relationship and then I don't know any other way of creating multiple new records in one go.
Would i have to use VB code for this or is there a simpler way? I just can't get my head around this one:mad:
View 11 Replies
View Related
Oct 30, 2006
I have a form where I want to enter an ID and then see the Name in the next field.
How do I do that? Everytime I enter an ID I get "?Name" in the next field.
The Table is "BudgetLine" and I enter an ID from a table "Item"
Item
ID
Name
View 5 Replies
View Related
Nov 10, 2006
Hello Everyone,
Ok, here's the scoop. I have a tournament / player score tracking database that we are developing.
1. I have a form that I would like to use to select a table number. I use a combo box to select this table number. This part is easy.
2. Once I have selected the table number, I would like to have two sets of fields automatically filled with information from two different queries (ie: player #1 and player #2). Each set of fields will include the player name and the appropriate score fields for the event. The majority of the time the data that will show up will only be the player name since we will be receiving scores and entering the information as it comes in. However, this same form could be used to edit player scores.
3. I would like to be able to tab from one field to the next when entering the information. After all of the information is entered, I would like to then hit a button that will write the information back to the two queries.
The problem that I seem to have is trying to enter information from two queries on the same form. Alas, I have been experimenting with unbound forms, subforms and unbound text fields, but have not been able to get anything to work. Would anyone have some suggestions? Any help is greatly appreciated.
Thanks,
Greg
View 1 Replies
View Related
Oct 1, 2014
I have a table that holds the ID to another table where I pick up the name to display instead of the ID. If a name is added or changed then the ID needs to be added/updated.
The name (and other details) are displayed on a form from 1 to ,many rows.The field that holds the name is a combo box and I can change that field by selecting the bane from the combo box or typing the name in. What I want to be able to do is type in the ID (I'm actually scanning it in from a bar code) but display the name. How do I do this.
As I say this currently works if name is typed (Or scanned in)and the ID is written to the table.On the data tab for the field I have as the control source the ID itself. In the Row Source I have select 5 fields from the second table which includes the ID and name (both from 2nd table). The Bound Column is set to 1. In the Format tab Column count is 5 and column widths is set to 0cm;3cm;0cm;0cm;0cm. This allows the name (2nd column) to be displayed. But the 1st column to be stored on table 1. However I can't scan in the ID which I want.
View 3 Replies
View Related
Nov 3, 2005
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
View 3 Replies
View Related
Apr 4, 2012
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.....
View 2 Replies
View Related
Jun 14, 2005
I'm pretty sure there's a simple solution to this, yet I can't seem to find it.
I have a table that contains the Months of the year with an MonthID:
1|January
2|Febuary
...
I also have a form that contains a combobox with the Months.
When the user chooses the month, a query gets created, returning the MonthID.
I want to store the MonthID, in an invisible textbox, so that I can use it for my calculations.
If I use a textbox, I can't seem to display the result and if I use a listbox, I can't access that value.
How would I make it work?
This is the code used on the combobox:
Private Sub cbToMonth_Change()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("getMonthID")
strSQL = "SELECT Months1.MonthID " & _
"FROM Months1 " & _
"WHERE (((Months1.Month) In ('" & Me.cbToMonth.Value & "')));"
qdf.SQL = strSQL
Me.monthIDTo.Value = "getMonthID"
Set db = Nothing
Set qdf = Nothing
End Sub
View 3 Replies
View Related
