Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Invoice Date

Discussion in 'Information Technology' started by Ryan, Nov 3, 2005.

Tags:
  1. Ryan

    Ryan
    Expand Collapse
    Guest

    Hi, I hope this question has a simple enough answer.

    I'm trying to create a database that will do the invoicing for my company
    (currently done in Excel).

    Every day I create invoices for the previous days work, but I have to
    invoice it under the previous days date. I know that =Date()-1 does a rough
    job, but the tricky part is on Monday's, where the previous work was done on
    the Friday.

    My access / coding skills are very rusty and I'm doing this project because
    my company has no controls for there invoicing system and employees in the
    past have managed to screw it up. Any help would be greatly appreciated.
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    =DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    news:43071C30-5E8B-4612-9A72-A21A5E41FE15@microsoft.com...
    > Hi, I hope this question has a simple enough answer.
    >
    > I'm trying to create a database that will do the invoicing for my company
    > (currently done in Excel).
    >
    > Every day I create invoices for the previous days work, but I have to
    > invoice it under the previous days date. I know that =Date()-1 does a

    rough
    > job, but the tricky part is on Monday's, where the previous work was done

    on
    > the Friday.
    >
    > My access / coding skills are very rusty and I'm doing this project

    because
    > my company has no controls for there invoicing system and employees in the
    > past have managed to screw it up. Any help would be greatly appreciated.
     
  4. Ryan

    Ryan
    Expand Collapse
    Guest

    I added that line (exactly) as the default value in the Text Box in my form.
    It's bringing up the error #Error. I removed an "I" from the "If" in that
    statement to check if that was a typo and it gave me #Name? instead of the
    #Error.

    Would that be where I put that code and is there anything I have to change
    in the code to customize it?

    Thanks

    "Douglas J Steele" wrote:

    > =DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    > news:43071C30-5E8B-4612-9A72-A21A5E41FE15@microsoft.com...
    > > Hi, I hope this question has a simple enough answer.
    > >
    > > I'm trying to create a database that will do the invoicing for my company
    > > (currently done in Excel).
    > >
    > > Every day I create invoices for the previous days work, but I have to
    > > invoice it under the previous days date. I know that =Date()-1 does a

    > rough
    > > job, but the tricky part is on Monday's, where the previous work was done

    > on
    > > the Friday.
    > >
    > > My access / coding skills are very rusty and I'm doing this project

    > because
    > > my company has no controls for there invoicing system and employees in the
    > > past have managed to screw it up. Any help would be greatly appreciated.

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    AFAIK, you can't use the intrinsic constant vbMonday in the Default
    property. Replace it with the value of vbMonday (2):

    =DateAdd("d",IIf(Weekday(Date())=2,-3,-1),Date())


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    news:B017B6F3-60CD-4922-97E5-0B2E77FE924B@microsoft.com...
    > I added that line (exactly) as the default value in the Text Box in my

    form.
    > It's bringing up the error #Error. I removed an "I" from the "If" in that
    > statement to check if that was a typo and it gave me #Name? instead of the
    > #Error.
    >
    > Would that be where I put that code and is there anything I have to change
    > in the code to customize it?
    >
    > Thanks
    >
    > "Douglas J Steele" wrote:
    >
    > > =DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    > > news:43071C30-5E8B-4612-9A72-A21A5E41FE15@microsoft.com...
    > > > Hi, I hope this question has a simple enough answer.
    > > >
    > > > I'm trying to create a database that will do the invoicing for my

    company
    > > > (currently done in Excel).
    > > >
    > > > Every day I create invoices for the previous days work, but I have to
    > > > invoice it under the previous days date. I know that =Date()-1 does a

    > > rough
    > > > job, but the tricky part is on Monday's, where the previous work was

    done
    > > on
    > > > the Friday.
    > > >
    > > > My access / coding skills are very rusty and I'm doing this project

    > > because
    > > > my company has no controls for there invoicing system and employees in

    the
    > > > past have managed to screw it up. Any help would be greatly

    appreciated.
    > >
    > >
    > >
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Two things ...

    1) You can only use intrinsic constants such as vbMonday in VBA code. In
    expressions like this, or in queries, you have to use the literal value of
    the constant, in this case, 2 ...

    =DateAdd("d", IIf(Weekday(Date) = 2, -3, -1), Date)

    2) Access will "helpfully" place square brackets around the word "Date" in
    the above expression, then throw a wobbly because it can't find a field
    called "Date". To prevent this , add empty parentheses after the word "Date"
    to tell Access you mean the Date function and not a field named "Date" ...

    =DateAdd("d", IIf(Weekday(Date()) = 2, -3, -1), Date())

    --
    Brendan Reynolds


    "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    news:B017B6F3-60CD-4922-97E5-0B2E77FE924B@microsoft.com...
    >I added that line (exactly) as the default value in the Text Box in my
    >form.
    > It's bringing up the error #Error. I removed an "I" from the "If" in that
    > statement to check if that was a typo and it gave me #Name? instead of the
    > #Error.
    >
    > Would that be where I put that code and is there anything I have to change
    > in the code to customize it?
    >
    > Thanks
    >
    > "Douglas J Steele" wrote:
    >
    >> =DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    >> news:43071C30-5E8B-4612-9A72-A21A5E41FE15@microsoft.com...
    >> > Hi, I hope this question has a simple enough answer.
    >> >
    >> > I'm trying to create a database that will do the invoicing for my
    >> > company
    >> > (currently done in Excel).
    >> >
    >> > Every day I create invoices for the previous days work, but I have to
    >> > invoice it under the previous days date. I know that =Date()-1 does a

    >> rough
    >> > job, but the tricky part is on Monday's, where the previous work was
    >> > done

    >> on
    >> > the Friday.
    >> >
    >> > My access / coding skills are very rusty and I'm doing this project

    >> because
    >> > my company has no controls for there invoicing system and employees in
    >> > the
    >> > past have managed to screw it up. Any help would be greatly
    >> > appreciated.

    >>
    >>
    >>
     
  7. Ryan

    Ryan
    Expand Collapse
    Guest

    Works now, Thank you very much

    "Douglas J Steele" wrote:

    > AFAIK, you can't use the intrinsic constant vbMonday in the Default
    > property. Replace it with the value of vbMonday (2):
    >
    > =DateAdd("d",IIf(Weekday(Date())=2,-3,-1),Date())
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    > news:B017B6F3-60CD-4922-97E5-0B2E77FE924B@microsoft.com...
    > > I added that line (exactly) as the default value in the Text Box in my

    > form.
    > > It's bringing up the error #Error. I removed an "I" from the "If" in that
    > > statement to check if that was a typo and it gave me #Name? instead of the
    > > #Error.
    > >
    > > Would that be where I put that code and is there anything I have to change
    > > in the code to customize it?
    > >
    > > Thanks
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > =DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Ryan" <Ryan@discussions.microsoft.com> wrote in message
    > > > news:43071C30-5E8B-4612-9A72-A21A5E41FE15@microsoft.com...
    > > > > Hi, I hope this question has a simple enough answer.
    > > > >
    > > > > I'm trying to create a database that will do the invoicing for my

    > company
    > > > > (currently done in Excel).
    > > > >
    > > > > Every day I create invoices for the previous days work, but I have to
    > > > > invoice it under the previous days date. I know that =Date()-1 does a
    > > > rough
    > > > > job, but the tricky part is on Monday's, where the previous work was

    > done
    > > > on
    > > > > the Friday.
    > > > >
    > > > > My access / coding skills are very rusty and I'm doing this project
    > > > because
    > > > > my company has no controls for there invoicing system and employees in

    > the
    > > > > past have managed to screw it up. Any help would be greatly

    > appreciated.
    > > >
    > > >
    > > >

    >
    >
    >
     

Share This Page