Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: I dont how to add 35 exta days on a field from to date?

    I am stuck any help would be greatly appreciated.
    I have an invoice page (form) and a button to raise invoice. Everytime I click the button it raises an invoice and it automatically creates today's date in the InvoiceDate field. However, I also have another field called ' ExpectedPaymentDate' and this field is supposed to automatically add 30 days from the InvoiceDate so that the database gives me reminder when the invoices are due. I have sorted out auto reminder part but I unfortunatley could not automate the 30 days from the InvoiceDate to automaticallhy appear in the "ExpectedPaymentDate' field.

    By the way, I am writting half the code in here which shows the InvoiceDate'
    Private Sub cmdInvoiceClient_Click()
    Dim strAccountRef As String
    Dim strSQL As String
    Dim intInvoiceExists As Integer
    Dim ReportName As String
    Dim rsNewDetails As New Recordset 'Making a recordest of new entry, to update fields with financial figures


    'Me.Requery - cant use requery since it's based ona query and sorted by date of order
    Me.InvoiceDate = Format(Date, "Medium Date")
    Me.Recalc

    'New code added by Emal on 01/12/07 .
    If Len(Me.SageInvNumber) > 0 Then intInvoiceExists = 1

    ' If invoice has already been created for this ID then
    ' tell the user, otherwise create an invoice for this project.
    If intInvoiceExists = 1 Then
    MsgBox ("An invoice has already been created for this project")

    Else
    'Create new record in Invoice table
    'The autonumber field Invoice.id becomes the unique Invoice number
    strSQL = "INSERT INTO Invoice ( Bookingid ) SELECT " & Me.AccountRef & ";"
    DoCmd.RunSQL (strSQL)

    temp_SageInvNumber = Generate_Seger_Invoice(Bookingid)
    End If
    Any help would be much apprecaited.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can use the DateAdd function to add 30 days, but why would you save a value that can be so easily calculated from the invoice date?
    Paul

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi Paul, Thank you for your time. Honestly, i dont know how to enter the DateAdd function. The reason I want to automate this is to save us time. Right now, we manually add and enter 30 days on after we raise each invoice but since this is really wastes a lot of time that is why I want to automate this.

    I would be grateful if could you write the fuction (code) in your response to automate this at under the same click procedure of the invoice button.

    Thanks again.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm all for automation, but like I said, why save it at all? If it's always 30 days, you can always calculate it from the invoice date, and you don't have to worry about keeping it updated.

    If you insist on saving it, look at DateAdd in Help. It's pretty simple, but if you have trouble with it, post your effort and we'll fix it.
    Paul

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think what paul is getting at is that you don't need to add 30 (or 35) days to a value and store it in the db

    you can generate that value at anytime you require, either in a query, a report a form, wherever.

    examples of use of the dateadd function can be
    in a query
    DueDate: dateadd("d",35,<mydatevalue>)
    where <mydatevalue coudl be InvoiceDate

    in a function eg a forms on current event
    DueDate = dateadd("d",35,<mydatevalue>)
    ..assuming that yoiu have a control called DueDate on the form, or if you wish to assign the value to a varaible called DueDate

    in a report or form for a controls value
    dateadd("d",35,<mydatevalue>)

    in Access/JET you don't actually need to use something like dateadd as a datetime value in integer is the number of days since a specific point in time, so adding 35 to an existing datevalue gives that date + 35.
    ie you could use <mydatevalue>+35 in place of the dateadd phrases above.


    HTH
    Last edited by healdem; 12-28-07 at 14:21.

  6. #6
    Join Date
    May 2006
    Posts
    386
    Hello Healdem,
    Thank you for your kind advice and very detailed and helpful response.
    I appreciate what Paul is saying, but the reason I want to automate this 35 days is that we dont normally remember to chase overdue invoices. 2ndly, when I raise an invoice, the invoice date is automatically created so I was thinking if I could automate the 'ExpectedPaymentDate' then that will be very helpful as my other forms will be enabled to give us reminder for over due invoices based on the ' ExpectedPaymentDate' field.

    Yes, you are right, I have a field two fields on the form called ' Invoicedate' and 'ExpectedPaymentDate'. ExpectedPaymentDate is the field i want to give me another date 35 days more than the 'InvoiceDate' e.g. If the invoice date is 28/12/07 then the 'ExpectedPaymentDate' should be 28/01/08.

    I hope I have explained my situation clearly and I would appreciate if you could suggest which of your suggestions will serve my purpose. Thank you so much for your help.
    Emi-UK
    Love begets Love, Help Begets Help

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're completely missing the point, but I'm not going to beat my head against the wall.

    Me.ExpectedDate = DateAdd("d", 35, Date)
    Paul

  8. #8
    Join Date
    May 2006
    Posts
    386
    Hi Paul, My apologies, my last response was in reply to healdems response.
    Thank you for your help Paul and I did not mean to disturb or bother you again.
    Emi-UK
    Love begets Love, Help Begets Help

  9. #9
    Join Date
    May 2006
    Posts
    386
    Hi Healdem, I have tried inserting
    Me.ExpectedDate = DateAdd("d", 35, Date)
    which paul suggested, but this did not work my Reminder Form. Could you please guide me how to enter the same procedure in a Query? Right now I have
    <=Now()
    under the 'ExpectedPaymentDate' criteria but I wanted the code to say to the query under the 'ExpectedPaymentDate' criteria that IF ExpectedPaymentDate is Greater 35 days THAN InvoiceDate Then only those invoices should be listed when the query is run.

    Thank you for your help.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    open a query in design mode
    select a free column
    cut and paste the following.....
    expr1: Date()+35

    date() is a function which returns the current date (now() return the current system date & time) Access uses a datatype for dates which stores dates and times. if you use now() you will include the time as well as the date. date() returns the current date with a time of 00:00:00

    if you wanted to generate a due date then replace date() with the name of the column which contains the invoice date

    eg if your Invoice date was called InvoiceDate then
    DueDate: InvoiceDate + 35
    will create a column in the query called DueDate with a value equal to the invoice date + 35 days. That should have the same effect as dateadd("d",35,InvocieDate)

    a note of caution the above isn't tested

    have a look in the help file for date(), Now(), DateAdd() functions

    the date you used would fail as the runtime / query parser is expecting a variable or column name date, rather than the function date()
    Last edited by healdem; 12-28-07 at 19:05.

  11. #11
    Join Date
    May 2006
    Posts
    386
    Thank you very much for your continued help. It is perfect now. It works exactly the way I wanted it to. Thank you.
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

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