After looking at the replies to my post on the above subject, I realize that one needs to be an expert programmer in VB, SQL etc. to design queries/forms to filter records year-wise for all type of record operations. I know nothing about programming. So I have used these codes (of course, with the help of this forum) :
Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me![DyNo]) Then
Me![DyNo] = Format(Nz(DMax("[DyNo]", "[tblAllDet]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "0")
End If
Me![DyNo] = Format([DyNo], "0")
If DatePart("m", Now()) = 1 And [DyNo] = 1 Then
Backup
Dim db As Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAllDet")
db.Execute "DELETE * FROM [tblAllDet];"
rs.Close
db.Close
Else
End If
End Sub
First, with DMax, my DyNo field is reset to No.1 on change of year.
Then, ‘DatePart’ code looks for January and DyNo.1 and does Backup (a module) of existing records.
Lastly, all records in the current DB is deleted so I get a blank database for the new year.
For the previous year’s records, I can rename the backup file year-wise and use.
I have tested this with dummy records and changing system date. I didn’t face any problem. However, I would like the experts of this forum to have a look at the above codes and point out any flaws. Thanks in advance.
Need to generate a unique reference number each record wise while data entering by a form. How to do this by using a command button ? After entering all fields related to the record, when click on the command button, unique reference number for that record need to be generated and save with all other data of the records. in a later event, need to recall the record by using this reference number and also need to make relation ship with some other table by using the reference number.
In my Access forms, i display data by month wise; and i want to give the conditional formatting with two condition;
If date of today is greater than target date for that month and if got value more than 0 - Red color If date of today is lesser than target date for that month and if got value more than 0 - green color
Target date for every month in available in a separate table.
Hi, I have a problem with form design, I want a form like in Northwind sample database: Summary of Sales by Year Report. It use Sorting and Grouping for Footer that I can't find it in Form design. Is it any other way to do it in Form design so I can get the same result like in Report design?. The reason why I want it, because I want to control the size. thankyou in advance for your help.
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.
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 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.
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
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year y = Number of the day of the year (1 to 366) 'can this show three digits all the time? hh = Hour in two digits (00 to 23) nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
There's a lot of info that I need to keep track of by just the year. If I enter it as a normal date, I would need to extract the year every time I need to query and then do what ever. Would it be easier just to extract it once, convert to an integer and use it like that through out the system when I need to query by year?
In my DB im trying to determine the aniversary date of a loan closing. I can't seem to get the query to ignore the year and just show me the loans that closed in upcoming month regardless of what year. This is what I have so far, what else do I need.
thanks, Raydan
SELECT Loans.LoanID, Loans.EndDate, Loans.LoanLender, Loans.CustomerID FROM Loans WHERE (((Loans.EndDate)>DateAdd("m",-1,Date())));
My main report shows deliveries and types by month. At the bottom there is a subreport that totals everything for the year. However if you go back a month or two or three, etc the report show the total for the year, not up until that month. Any Ideas on how to total up up until the selected month?
I have a church database for our choir...we are usually the same group each year ... I have built a database logging all robes, hymnals, etc... I have a table with the contact person (contact), address information, sizes, and yearly dues paid (year)... I would like to just update the year from one year to the next without having to change 135 individually... is there someway to automate this or a function that I can use.... I still would like to have access to the 2006 files to see if someone is making partial payments and check their records (as well pastdue balances) but at the same time move/update to 2007.... is there an easier way to this...I am fairly new to Access and don't mind trying something...I have backups of my database so I can try anything. thanks in advance..
I want a field in a table that is a date but that is the year only. I only want this because I want to be able to filter my charts to show one or a span of years for comparison. I have criteria in a query that states between [forms].[charts].[begin] and [forms].[charts].[end] and the corresponding form that my user puts the desired begginning and ending dates into before pressing buttons that bring up the charts based on the query.
My problem is that I can't even get the table to take a year only date. If I leave the date field as text the between clause in the query won't work. I tried it as a number with >= etc. and that didn't work either.
Please could someone advise me how to set the YEAR in a query - all my data for 2006 is fine but I have uploaded some data for today (01 Jan 07) and ALL by queries / reports now show to 31/12/2006 then at the top, instead of the bottom, reads 01/01/06 ?
I currently have a query that lists the date of something. I just need to have the year in which it is done for a task so I need to somehow find a way to write a function that will just show the year instead of the entire date. Any ideas.
I would like to run a query to return all dates for a year like 2007. I need to ignore the day and month so the user will be asked to enter the year “2007” they require a form list on.
I have used the between date query:- Between [Start Date] And [End Date], and it’s okay but it would be much faster if only one parameter can be typed to return a list of the full year.
Any help would be appreciated, I cant find anything here which helps. Thanks!
Hi All, i need ur help, i have working on database in which one of form/table i m using the day code(e.g 1 to 365). i want that these day value are automatically add on next day.......
For Example: Today Date: 17/08/2005 Day Code: 229 2nd Date: 18/08/2005 Day Code: 230 3rd Date: 19/08/2005 Day Code: 231
i just want when date chenge, day code is automatically chenged.
I have a form that lists the winners of certain awards. As the Heading of that Form I have the following text (as an example):
Best & Fairest Winner for [txtbox].
I have put the simple formula =Now() in the Textbox, but all I want it to do is show the year and not the actual month or day. I looked at the format menu and it doesn't have that option.
Is there some way I can have just the year showing in the textbox without having to manually change it each year.
I have made the txtbox small enough so that it only shows the year on the end, but I was wondering if there is a correct way of doing it.
can i change date format that contain day, month, and year to month and year only.. i try change at fromat at porperties, but it change back into dd/mm/yy at combo box.. this is bcoz i want to filter up my subform that contain parts that purchased by customer by month..
