Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98

    Unanswered: DateDiff question

    Hi,

    I have a table which has the following fields:

    Notice_ID (PK)
    Start date
    End date
    Duration in days

    I made a query and tried to add the following code to the 'Duration in days' field:

    DateDiff("d";[Start_date];[End_date])


    But it comes up with no results.

    Has anyone got any ideas on what I am doing wrong?

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: DateDiff question

    Originally posted by charlottevk
    Hi,

    I have a table which has the following fields:

    Notice_ID (PK)
    Start date
    End date
    Duration in days

    I made a query and tried to add the following code to the 'Duration in days' field:

    DateDiff("d";[Start_date];[End_date])


    But it comes up with no results.

    Has anyone got any ideas on what I am doing wrong?
    Well first problem I see is that you have semi-colons in the arguments... They should be commas...
    Also, you said that your fields are named "Start date" and "End date", but in the expression you have Start_date (with an underscore) and End_date... Which is correct?

    Try either ... DateDiff("d",[Start date], [End date])
    or... DateDiff("d", [Start_date], [End_date])

    HTH

  3. #3
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98

    DateDiff question

    Originally posted by Trudi
    Well first problem I see is that you have semi-colons in the arguments... They should be commas...
    Also, you said that your fields are named "Start date" and "End date", but in the expression you have Start_date (with an underscore) and End_date... Which is correct?

    Try either ... DateDiff("d",[Start date], [End date])
    or... DateDiff("d", [Start_date], [End_date])

    HTH
    Hi and thanks! I made a spelling mistaken in the expression above, but that doesn't seem to be the problem. When I do the DateDiff in a field in the form it works perfectly. What I can;t do however is save the value in the field.

    Any other ideas on what I am doing wrong?
    Last edited by charlottevk; 02-11-04 at 04:12.

  4. #4
    Join Date
    Feb 2004
    Posts
    199
    1) are you shure - du you need to save calculated data in the table?
    do you know what is Nornalized Data Base, if you try to awoud saving calculaterd data in you database, you'l get more dinamical application and avoid data inconsistance.
    You can get calculated data via Query any time same way you get iti on the form.

    2) but if you have big database and need calculated data often, you may need field with it, in this case you can use a code or action query to fill this field while data inserting or updating (unfortunately there is no trigers in Access DB)

  5. #5
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    hi,

    yeah I know about nomalisation and I want to save the data in the field to use it afterwards to use it more in different contexts.

    My question was that I can't seem to place the function I have. I don't know where and how to put the code in the field so it only saves the value that I . I have the following code:

    If CheckboxSignoff = True then
    DiffDate("d",[Date_incoming],[Date_outgoing])
    Else
    DiffDate("d",[Date_incoming],Date)
    End if

    Any ideas more than welcome!

  6. #6
    Join Date
    Feb 2004
    Posts
    199
    1) there is no way to put in a Table any code or function running on insert or update event in MS Access;
    2) you can use form based code with afterinsert or afterupdate events;
    3) you can design and run an Action Query that mnodifies the table as you need.

    when do you need the field updated?
    once, afterinsert or afterupdate?

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by charlottevk
    hi,

    yeah I know about nomalisation and I want to save the data in the field to use it afterwards to use it more in different contexts.

    My question was that I can't seem to place the function I have. I don't know where and how to put the code in the field so it only saves the value that I . I have the following code:

    If CheckboxSignoff = True then
    DiffDate("d",[Date_incoming],[Date_outgoing])
    Else
    DiffDate("d",[Date_incoming],Date)
    End if

    Any ideas more than welcome!
    Good Morning...

    Your problem is that you're not assigning the value to the field... You want an assignment statement... You have the DateDiff part, but where's the field name or variable that you are assigning the value to... There has to be a "something =" on the left side of the expression...

    It should look like this...

    If CheckboxSignoff = True then
    YourFieldName = DiffDate("d",[Date_incoming],[Date_outgoing])
    Else
    YourFieldName = DiffDate("d",[Date_incoming],Date)
    End if

    HTH

  8. #8
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    hi,

    Thanks for all the help so far! The problem is still very much present. It may sound like a really stupid question but I have been struggling so much with it now that I can;t see what to do anymore.

    I have a textfield and all I want it to do is show me the following outcome of this code:


    Private Sub textfield()
    If Afgemeld = True Then
    Textfield = DiffDate("d", [Date_now], [Date_signoff])
    Else
    textfield = DiffDate("d", [Date_now], Date)
    End If
    End Sub

    Is this possible and how can I do this? In a query or in the form itself? And ifso where do I place the code. Codes aren;t really the problem, it's more a question of how and where to put what.

    PLEASE HELP!!!

  9. #9
    Join Date
    Feb 2004
    Posts
    199
    "Private Sub textfield()" - ?!
    what are you tring to do ?!
    your control is named as textfield and same time you have a Sub with same name?

    Please be more clear, explain your task, what do you need:
    1) to have a table with fields calculated with datediff function, once only;
    2) to have a form with command button to fill the table record with that formula;
    3) to have an Input form with code that fills that field automatically when user inserts a new record

  10. #10
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    Originally posted by Kakha
    "Private Sub textfield()" - ?!
    what are you tring to do ?!
    your control is named as textfield and same time you have a Sub with same name?

    Please be more clear, explain your task, what do you need:
    1) to have a table with fields calculated with datediff function, once only;
    2) to have a form with command button to fill the table record with that formula;
    3) to have an Input form with code that fills that field automatically when user inserts a new record
    I'm sorry, I'm not being very clear.
    What I want to do is have a field in the form that shows the user how many days between the start and finish date of a notification. If there is is no end date I want it to calculate the days between the startdate and the current date.
    If you recommend I do this in a query: how and where do I place the code.
    If you recommend I do this in a form: again, how and where do I place the code?
    If I

  11. #11
    Join Date
    Feb 2004
    Posts
    199
    Aha! this is what we needed
    you should just change you query
    change

    DateDiff("d",[Start date], [End date])

    with

    DateDiff("d",[Start date], nz([End date],Date()))

    taht's all, you don;t need eny coding
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

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