Calculating a date a date

[djchapple]

I am very new to access and I am still finding my way around the basics.

I have a database that has the automatically generated record number in field 1 and a date in field 2.

I would like to generate the value for the date stored in field 2 using the record number stored in field 1.

Is this possible?

 

[CBrighton]

Can you give an example of what you mean?

Do you mean you currently have:

AutoNumber |    Date
     1     | 01/01/2011
     2     | 01/01/2011
     3     | 02/01/2011

And you want to create a concatonation like:

ConcatonatedField
    101012011
    201012011
    302012011

Or do you mean something else? Give example data & intended results please.

 

[djchapple]

I have a database with a table which is effectively a calendar containg the people who volunteered that day.

Field 1 contains the automatically generated record number and record number 1 relates to 31 Dec 2005.

I want field 2 to contain an automatically generated date so that when i start on a new record the date will be inserted automatically.

So I will get the following effect

Field 1 (automatically generated record number) Field 2 (automatically generated date)
1 31 Dec 2005
2 01 Jan 2006
3 02 jan 2006

The principle of creating a date from a nymber is simple enough - I just have no idea how to get it into Field 2

 

[CBrighton]

I's not something which I've ever done, but it should be simple enough.

If you convert a date into an integer you should get the number of days since the start of that calendar (I don't recall the first day but I'm sure the MS Access helpfiles will tell you if you look at the date/time data type.

It's just a case of finding out what number 31/12/2005 is, then you can use that to create a calculated field.

For example, if the integer 1 was 100 days before 31/12/2005 (which it's not, I think it's something like 1/1/1901) then it would be something like:

CalculatedDate: format((CInt([AutonumberField]) + 100),"Short Date")

Basically, take the autonumber for that date, add however many days there are between the start of the date/time data type and format the result as a date.

 

[CBrighton]

Note: an easy way to see the integer for dates is in excel.

Input a date in a cell then move to the cell below. Press ctrl&@ to copy the above cell but it doesn't copy the formatting until you leave the cell (at least in Excel 2k3).

For example, 1st Aug 2011 comes out as 40756, so if you wanted autonumber 1 to be 1/8/11 you would have to add 40755 to the autonumber field and convert it to date format.

 

[plog]

Actually I wouldn't do it exactly as CB describes if you are using an autonumber. You can't guarantee that autonumbers will appear sequentially (most likely they do, but there can be hiccups in your database that skip a number).

I would search this forum for a custom id system which uses the DMax function. Essentially, you create a new numeric field and input 1 into the first record, then for each new record you use the DMax function to find the highest value, add one to it and insert that value into the new record. From there you can use CB's method.

 

[CBrighton]

Or you could just redo the table with a Number field replacing the autonumber field and have a second temp table with just a single date column.

Whenever you want to add dates input them into the temp table and run an update query which populates the main table with the date in the date field and the date converted to an int in the PK field.

That way you'd have a PK which was automatically created by the append query which is the exact number used for the date in question, meaning no additiional sums are required in calculated fields. a delete query run after the append query will ensure the temp table is cleared after the append.

 

[Brianwarnock]

I've never done any of this and I have to say that I'm a little unsure about the whole idea of autogenerating these fields, however a couple of points , which can be ignored.

If the whole aim is just to get continuos dates the Plog's suggestion could probably work directly on the Date field

If you use the auto number approach, of which ever method , then in the Gotfocus Event of the date field on the Input Form put

me.datefieldname = Dateadd("d",me.numberfieldname,#30/12/2005#)

then as you Tab througth the Form the Date will be entered automaically

Brian

 

[djchapple]

The first records in the relevant section of the database are as follows

Field 1 = ID Field 2 = Date Field 3 Field 4 Field 5

1 30/12/2005
2 31/12/2005
3 1/1/2006

If I use the DateAdd function I presume that I would insert DateAdd("d", [ID], "29/12/2005") but I am not sure how to insert the field called ID.

Also I am not sure where I should insert it - in the table. query or report and then where precisely in that part of the database.

 

[Brianwarnock]

Sorry to be blunt but did you read what I wrote

If you use the auto number approach, of which ever method , then in the Gotfocus Event of the date field on the Input Form put

me.datefieldname = Dateadd("d",me.numberfieldname,#30/12/2005#)

then as you Tab througth the Form the Date will be entered automaically

Brian

 

[djchapple]

Sorry to be blunt but did you read what I wrote

I am full of remorse for my carelessness in not fully reading the rplies in full. I started reasding the replies on my smart phone, which is very slow and very small and i did not get to then end.

I will be more attentive in future.

I really do appreciate all your help and, as a newbie, I need all I can get.

What I am struggling with is th exact foirmat to be used. For exmple the "d" is entered in quotes bit I am not sure whether the fieldname is also in "" or [] or what.

I must go back to the book and get some more basic knowledge as i am getting well out of my depth.

 

[Brianwarnock]

You are correct that the "d" is entered in quotes, as the field name contains no special characters or blanks it can be entered with or without the [], it is one of the resins we say do not use them, in fact when you start to code in the vba editor in the got focus event after entering the me.you will be given a list of possible entries and your field Id will be there but your Date Field will not as it has a blank in it . It will I think appear as Date_Field which is the technique ie most programmers follow.

So me.[Date field] = Dateadd("d",me.Id,#30/12/2005#)

Brian

 

[djchapple]

Thanks again.

What I am realising is how little I know about the whole process you have described. Can anyone suggest a good guide covering this process in resaonable depth but aimed at the novice and any web tutorials/courses that may help me in what appears to be a very steep learning curve.

I now realise that basing the date on the automatic ID, which could change without my knowledge, is not a good idea so I will base it on someopther permanenet data.

I have worked with computers and computing all my working and retired life but though I have had a great deal of experience with mainframe and desktop computers and many of the MS Office programs this is the first time I have worked with Access. So far mu experience has covered just the construction of very simple databases.

 

[Brianwarnock]

My exposure to MS Office came after I retrierd from working on Mainframes and all I know is self taught as the Charity I worked part time for couldn't afford training or books.
This forum was the main source of my education and Pat Hartman the teacher.
Your question about books etc has been asked before, the best way to search the forum is with Google, the how is in a "sticky" in the General forum.

I have attached a mickey mouse DB showing a method of populating the datefield in each new record with the next date, it includes a method of handling the first record.

However I have concerns about your database design, tho that is not my strongest subject, or should I say that is weaker than others.

What you appear to be proposing is 1 record per day containing a variable amount of data fields, this is a no no as far as good data base design is concerned as it can mean having to alter the db strucrure if the number of fields needs to change, it also makes working with the data difficult if not impossible. Read about normalisation, plenty of threads on here.

Brian