Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    11

    Unanswered: Dateadd with several fields

    I am working in Access 2003, trying to create a database over 250 computers. I need to create an automatic "expiration date" based on the "purchase date" + the "life expectancy". The "life expectancy" has three possible values: "3 years", "4 years" and "5 years".

    I thought I'd define "expiration date" as follows:
    Dateadd("yyyy", [the numerical value of "life expectancy"], "purchase date".

    Being a bit of a novice in this field, though, I don't know 1where to make this definition, 2. how to insert the different fields in the Dateadd string and 3. how to extract the numerical value only from the life expectancy values.

    Any advice would be greatly appreciated!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    My first question for you would be, what data are you storing? Are you storing the purchase date and the life expectancy? If so, there is no need to store the calculated field expiration date IMHO, though others may differ, it really depends on your particular situation.

    Also, how will you be using this; is it for use in a form? Report? How are you entering the other two fields, and where will you be using the third field.
    Me.Geek = True

  3. #3
    Join Date
    Sep 2007
    Posts
    11
    I am storing the hardware-profiles of the computers in order to have a report tell me the need for investments in new computers in the next 1, 2 or 3 years.

    I am now trying to do this by creating a parameter query. I would like the user to enter a standard life expectancy for all computers (3,4,or 5 years). Next I want the database to add the selected value to the purchase date. This should yield an expiration date which, when compared to the current date, should tell me whether the computer should be replaced.

    The ultimate goal is to have a report show the number of outdated computers.

    The problem here is my complete ignorance in SQL and coding in general.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by nikolaistavrogin
    The problem here is my complete ignorance in SQL and coding in general.
    lol, no worries, we were all at that point once. You're on the right track though.
    Quote Originally Posted by nikolaistavrogin
    The ultimate goal is to have a report show the number of outdated computers.
    I'm assuming that in your table you are storing some sort of computer ID, the date the computer was purchased, and it's expected life (and perhaps some other data which is irrelevant to the problem at hand).
    In the detail section of the report, I'd put the computer ID field, the purchase date, the life expectancy, and a blank text box, call it TxtBoxExpDate, or whatever you like. In the On Format event of the Details section of the report, do something like the following:
    Code:
    Me.TxtBoxExpDate = DateAdd("yyyy", Me.FldInterval, Me.FldPurchDate)
    You'll obviously have to change the names to the respective names of your fields.

    This is the basic code you'll need to accomplish what you want; but there are some more things you can do to make your report look better if you want.

    If you don't want the purchase date and life expectancy fields to show, you can always select them and change their visible property to false. This is the easiest way at least. You might be able to accomplish this task without even using those fields, but it may take more work than you're willing/able to do.

    You can also look into the Format function if you want to change how the date is shown in the expiration field. Something like:
    Code:
    Me.TxtBoxExpDate = Format(DateAdd("yyyy", Me.FldInterval, Me.FldPurchDate), "Short Date")
    You may also want to format the report to be more visibly understanding. What I mean is, a couple of dates next to some ID's doesn't tell you much at first glance. So I might do something like this in the Format event again, after the previous code:
    Code:
    If Me.TxtBoxExpDate < Date Then
        Me.TxtBoxExpDate.Background = vbRed
    ElseIf (DateAdd('d', 30, Me.TxtBoxExpDate)) <= Date Then
        Me.TxtBoxExpDate.Background = vbYellow
    Else
        Me.TxtBoxExpDate.Background = vbWhite
    EndIf
    This will make any date that is older than today have a background of red, any date within the next 30 days have a yellow background, and everything else is white. It'll make the report easier to read is all.

    Some things to consider, but this should at least get you started.
    Me.Geek = True

  5. #5
    Join Date
    Sep 2007
    Posts
    11
    Thank you very much for the answers so far! I dimly see my goal in the distance now. One obstacle, though, is that the report doesn’t seem to understand the code Me.Afskrivningsdato = DateAdd("yyyy", Me.Levetid, Me.K&#248;bsdato). I get a message saying that there is no macro called Me. Does this make any sense to you?

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Are you doing all this coding in VB? Where are you putting the code?
    Me.Geek = True

  7. #7
    Join Date
    Sep 2007
    Posts
    11
    I have tried to do as advised and put the code in the On Format event of the Details section of the report. I have based the report on a parameter query asking for life expectancy ("Levetid" or in your example "FldInterval").

    As to whether the coding is in VB or not, I am afraid we are back to the issue of my general coding ignorance. I do not know! In the parameter request I have used the following:

    "Like [Indtast standard levetid: 3, 4 eller 5 &#229;r]" (transl: insert life expectancy: 3, 4 or 5 years).

    And in the report I have tried to use your Dateadd-code with my field names:

    Me.Afskrivningsdato = DateAdd("yyyy", Me.Levetid, Me.K&#248;bsdato).

    Does any of this explain why it doesn't work or why I get the error message saying that there is no macro calle me?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •