I would like a field's default value to return the date of the previous Monday.
For example:
Now = Tuesday, 08-Apr-14
Return Monday, 07-Apr-14
Further example:
Now = Saturday, 12-Apr-14
Return Monday, 07-Apr-14
I've been trying to create a query that will take a date and return the week number of the date.
My original date is formatted m/d/yyyy
I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year
I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)
but the I get a result of 53 in this query, when I expected/need it to be 1.
Having a bit a brain freeze today. I have a field that auotmatically puts a date in when a checkbox is checked I am now trying to add a field which returns the day of the week from this date. Brain now mashed I am sure it's pretty simple but I just can't get it to work
View 5 Replies View RelatedI have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"
I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.
Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:
This month we did 250 units
Last month we did 300 units
so we did 15% less
"Yes my math is not exact"
They don't need to see last months data but i need a way to query the previous record to compare the data
This is the criteria code i use in the query that "Form B" is based on.
Code:
[Forms]![Form A]![Report_Start_Date]
I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.
How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.
I have managed to develop a database for my small business and am feeling very proud of myself. However, on fine tuning I would like to introduce a Date Picker for ease of use. I have a Check in Date and a Check out Date. At the moment they are working perfectly using the Date/Time field. I want to change that field to a datepicker. I have found out how to insert the date picker and it works great when inserted on a new record BUT all the previous dates in the database change to the current date. Is there a way I can still display the previous dates in the field and also introduce a date picker?
View 3 Replies View RelatedI'm having trouble with a Form and getting a text box entry box (Date entry) default to the date entered on the last record, which is linked to a table.
The only way I can get it to work is to type the date into the Text Box's Default property, for example, #11/13/13".
How to get the correct syntax or code into the Default property of the Text Box to make this work? I only want the entry to change versus the previous record/entry if the User changes / enters a new date.
I have a Main form that has button that loads a new Pop Up form for entering new data that will display in the Main form. When all the data is entered I click on a button that Saves the data and exits the form and then runs Re-query on the main form returning to the record that was current before the requery.
I have the following code:
Private Sub cmdSaveTradeAndExit_Click()
DoCmd.RunCommand acCmdSaveRecord 'Save the current record
DoCmd.Close 'Close the current form
Dim CrId As Integer
CrId = Forms!frmTransactionMainActivePopUp.CurrentRecord
Forms!frmTransactionMainActivePopUp.Requery
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
End Sub
But I am getting the following error:
Run-time error '2498':
An expression you entered is the wrong data type for one of the arguments
And the following is in yellow in the debug:
Code:
DoCmd.GoToRecord , Forms!frmTransactionMainActivePopUp, acGoTo, CrId
I'm trying to create a security measure that would prevent a user from accidentally paging down in a form and moving to the next record. I want to create an event macro that would fire if the user accidentally paged down into the next record. The macro would simply move back to the previous record.
What event would I use to build the macro ? What action would return to previous record ?
I run a service related company and I am trying to develop a service database that will automatically calculate the next service due based on the previous service date, the catch is that the service need to happen on the same week day. i.e. if the last service was on Friday 02-08-2013 and it is a monthly service, the next service is due on Friday 06-09-2013. Is there any way that this can be done? PS there are various service periods such as bi-weekly, weekly, 2 weekly, monthly, 2 monthly etc.
View 2 Replies View RelatedCan someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
I have a report that looks up a previous date then finds the data that goes with that date. I went to run the report and the data now is blank. I am thinking it is because of the formula is looking for 1/28/2013 instead of 1/31/2013.
Code:
DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]= DateAdd('m',-1,DateSerial(Year(Date()),Month(Date())-0,0))")
Hi,
I'm looking for a module wich converts a date to a week.
Ex: i type 08/02/2005 and i obtain Week #2
(Some years have 52 and other 53 weeks, depend on the date of the 1st week of the year, an other problem is that some people say the week begins the monday, other say it begins a sunday)
Maybe a built in fonction exists.
Thanks in advance for help.
VINCENT
Ok,
I am making a database which has a set of lessons which have been booked for a particular date.
What I want to do is be able to have a pop-up box ask you for a date for the start of the week. e.g. I want to find lessons from the week starting 02/03/2007 for 1 week (e.g. 2nd - 9th), so I type in the box "02/03/2007" and it comes up with records from that week.
I have tried this in the criteria box:
[Enter date]+7
and
=Date([Enter Date])+7
But nothing works. Also, I decided to add a record with todays date, and then use the criteria =Date(), but that didnt work either!!!!
Help would be greatly apprecated :p
Thanks
Michael
Hi Folks,
I use this to get a week number for a date range:
WeekNo: DatePart("ww",[Timestamp])
Is there a way that I could also get just the first date from that week number to appear in the query as well?
ie. if Week 1 is 3/1/07-3/7/07 the query would show 3/1/07 as well as week 1. TIA
I set up the following code on the Expression Builder
Date() Or Between Date() And Weekday(6) Or <Date()
I want to get all entries of the rest of the week, but it is not working.
Can I set up Weekday(6)?
So if it is Monday i want to see all entries from Monday to Sunday and if it is Wednesday i want to See all entries between Wednesday and Sunday.
Could you please help me! thanks
I have a date field in my table using short date (Assessment Date). I want to keep that and add a field in the table that converts that same date to a day of the week. I'm trying to use a calculated field in my table (AssessmentDay) and can't seem to get the expression right.
View 7 Replies View Relatedhi
i would like to display the date and week number on one of my forms but have no idea about dates and how to code this.
can some one help? id appreciate it it beyond me :confused:
thanks :)
rob
Hello
I have a query where i want to find out all deliveries which are older than 30 days but i want to exclude all weekends.
I know theres a networkingdays function in excel where you can do this but i want to do this in access. Please could any one advise how to do this?
Dear All, I have a field named: “reminder date” in a query and would like to show only records where the reminder date is within the next week whenever the query is run (ie if todays date is 26th may 2006 and I run the query now It would only show records that include the following dates 27,28,29,30,31, july and 1,2 June)
Ive tried the below bit of code but this shows to many records beyond one week.
>=DateAdd("d",+7,Date())
Anybody have any suggestions?
Regards
Jim
Hi,
How can i do the following -
When a date is typed in a text box, ie, 17/01/05
I want a Query to return what Number week in the year it comes from.
In this example the result would be Week "3"
I'm sure this would be easy enough to do if i typed in every day in the year into a table with the
week number beside it.
Is there some code or a more straight forward way.
hope all makes sense, if not i can go into greater detail.
How do I get the day of the week to show up on form? when I set the default in the table, I want it to read Fri. 8/29/14 not just (8/29/14) or what ever day that field has focus.
View 1 Replies View RelatedI have a large table which is updated everyday. Each record will have a different date but there will be many with the same account number. I have a query which will pull one record from each account number according to a user specified start date, another query which will pull by end date, and a third query which uses both to do a calculation on another field. The problem is, if one of the first two queries does not find a record with that date then the calculation will not be done because there is no record. What I need is a way to go back one day if the specified date does not exist. I have tried using an iif statement in a report with the DateAdd function in the true part. Here is what I have
IIf([blinks_test_end]![Reading_Date]=Null,DateAdd("d",-1,[blinks_test_end]![Reading_Date]),[blinks_test_end]![Reading_Date])
[blinks_test_end]![Reading_Date] is the user specified date. When I run the report it asks for blinks_test_end, I think it doesn't recognize that this is the user specified date.
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
View 1 Replies View RelatedHi,
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
Thanks heaps!
C