Hello.
Just wondering if there was a way to detect the current year? (2006, obviously). Reason being, have an input date of year a block of trees was planted and i want to have an automatic calculation for the 'age of trees'.
Thanks!
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"
Hi All.
I need to generate a query which will show me in Field1 Cost for the current year only and in Field2 Cost from a start date to the current. If is it possible how to do it?
Thanks.
The solution might be in front of my nose but I cannot see it... I have a query with all my records I need to extract only records of the current year, so that now will show all 2006, as we enter into the following year it will show all the 2007.
What is the criteria I should put into the "Date" field I have?
Thanks
Hello,
i have an expiry date for my members and each year they need to renew it, so i would like to have a button that when clicked adds 1 year to the date.
I currently have this query
=DateAdd("yyyy",1,[Commenced Training])
as there membership will expire one year from their joining date.
this is currently shown for hte text box expiry date.
however i cannot work out how to make another one for the button so that when the button is pressed it adds another year and stores its.
the image shows my example
thanks
I want access to generate for me an autonumber, but with the current year as initial value. ( To give new hired employees a unique number). For example, we are now in 2013
If there a new hire, that employee is going to get a unique number 2013001
The next new hire, will get 2013002 and so on
I know how to append a table to set the initial value, but this means, that next year I will have to append a new table again to set the initial value to 2014.
I want access to do this automatically for me, so that next year a new hire will get the unique number of 2014001, the next one will be 2014002 etc...
Hiya
This is my first post here, but I've two questions so I'll divide it into two threads.
Normally I can google these to help find the answers but a bit of a loss at this one, probably beginners stuff to some of you.
Heres a link to the database below
http://casp.gamecommunity.co.uk/database.gif
Total count is easy: =Count(tblMain!ID1)
What I want to do is count the amount of records for each individual month of the current year so I don't have to change the year date everytime a new year comes round. As soon as the next year comes around they all reset to 0.
The clever way would be to count the records for the current month -1 current month -2 etc and automatically update the month labels but I think that would be getting a bit comlicated.
Thanks in advance for any help. :)
Given a specific week no. for the current year... I need to return the first date of that week....
For example : week4 of this year (returns) 19/1/2015
Hi everyone,
I want to calculate the week number of a date in a year starting from the 1 July (of the current year) - as this is the beginning of the Financial Year in Australia.
The starting day of the week is Wednesday.
I've been trying to think how I could use the Access DatePart Function in a query to calculate the week number from sales records.
I have had no trouble calculating the week number from the 1 Jan using the DatePart function. However, the function doesn't allow me to select a different date other than the first week of January.
So as a work around, I've thought of calculating the week number of 1 July in the current year, and just using the DateAdd function to add the week number to the week calculated by the DatePart Function.
Not quite working yet. Here's what I've come up with so far.
WeekNumberCount: DatePart("ww",[DateCalled],4)
I use the following to calculate the "my" starting week of the year:
StartingWeekInYear: DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)
Then I add the two together together:
MyWeekNumberCount: DatePart("ww",[DateCalled],4)+DatePart("ww",DateAdd("ww",0,"1-July-" & Year(Date())),4)
I know it needs some work to be useful for all years. Any suggestions?
all using access 2010. I have a date field. I need to write a query to pull out dates with current year only. ex data: 1/1/10, 1/1/11, 2/1/12, 2/1/13. Need to pull all dates with current year which would be 2/1/13. Tried in query criteria of the date field: =Format(Now(),"yyyy") I received data mismatch. this is a date/time field.
View 4 Replies View RelatedHow do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
View 8 Replies View RelatedI have a report that I am trying to show data for the current year, but with a record count.
So, for example: I have 3-OVI, 3-Conduct Unbecoming, and 3-Did not transport for the current year.
When I run my report, it will show the above data for the current year, but in this format;
OVI-1
OVI-1
OVI-1
Conduct Unbecoming-1
and so on.
What I would like is;
OVI-3
Conduct Unbecoming-3
Did not transport-3
Total-9
If I take out the date code for the current year in my query, then I get the desired results but I get all data.
If I leave the date code in, then I get the data for the current year but I get the first example above.
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
I have a little "y2k issue".
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
tblProject
Project: Myproject
Value: 1000
EndYear: 2014
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
All, using access 2010. How do I display the default value of a date/time field to just the current year instead of using =date() to get the full date.
View 5 Replies View RelatedHello.
So, I have a logon form with a combo box for user name and a text box for the password. When they hit login, the user is then looked up and the password verified and if it's good they're taken to a specific screen that gives them specific options. What i want to know is how to hold the value of the user throughout their entire session? The reason for this being, to keep track of what data they enter mainly.
Any help would be much appreciated!
Thanks!
what is the proper method to check if the current record on a form is the first or last record ?
When i try :
If Me.CurrentRecord = acLast Then .....
it does not seem that it detects the the last record.
Similarly with acFirst
Any ideas ?
Thanks
I have multiple forms, one of which you can view meeting details (form 1), if a mistake is spotted, there’s an ‘amend’ button which brings up a text field to enter in the description.
Now I have another form (form 2) that you can view the meeting details later on, and I want it to display the amendment description if there’s something typed there.
So on form 2, if there’s no amendment description (from form 1), the field stays hiding, but if there’s an amendment description in the field, the field will be visible.
So is there a way to display a field if there’s data entered, or keep it hidden if no data is present?
Thanks in advance
Firstly I have placed this in the general section as I am not quite sure if any vba would need to be created for the following. I would also like to report on the information but the answer may not be done at report level.
Anyway, I have created a very basic database that holds staff attendance and their reason for being absent.
tblattendance:
AttendanceID
AttendanceType
StaffID
AttendanceDate
What i would like to do is group consecutive dates if they are all the same type and show them as DateFrom and DateTo
So for example, for someone who is on holiday between 01/Jan/06 and 06/Jan/06 I could show that as:
DateFrom 01/Jan/06
DateTo 06/Jan/06
I am not sure if that is explained very well but I will try to explain a bit more clearly if needed?
Thanks for any help.
I have a Form based on a table of current Players in a football league.
The Form is used to edit, but mainly add new registrations. The fields are...
Surname FirstName Address Suburb Age Club YearRegd
Is it possible in some way to have an alert or message come up stating that the Player is already registered if the Table already contains a player with the same Surname and FirstName only. This would need to happen after the FirstName has been entered as it is the second field after 'Surname'. I'm not sure if Access can do this type of function or not.
I've searched the Forums but have had no luck.
Any advice/assistance is greatly appreciated.
Thank-you very much.
I am learning how to create custom error messages.
I know about the "OnError" event of the form, and knows how to replace the standard error message.
I wish access will produce an error message saying something like "You must put a value in field X" (custom made msgbox)
How can I identify this 'field X' which caused the error message to be displayed?
Hope I made myself clear.
Thanks for your comments.
Is it possible to detect the Up or Down arrow keys?I've tried using the following in the Form's Key Press event;
Code:
MsgBox "You pressed the " & KeyAscii & " Key"
Which returns the ASCII code for most other key but not the arrow keys
Any VBA code that would tell me whether an xlsb file is password protected?
View 5 Replies View RelatedI have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.
This is being done in a query and we're using Access 2000.
Any help would be greatly appreciated.
Thanks,
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014