Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi, all! I am having issues trying to use the date,now, etc. function to show the first of the month. I have an append query that have a date field column with dates that could be older than the current month. I would like the query to identify those dates and substitute them with the first of the moth accordantly. For example:

    IIf ([StartDate]< Month(Date()),have the first of the month be place with format(mm/dd/yyyy),[StartDate])

    Any help is super welcome..

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Try, format(month(now()) & "/1/" & year(now()),"mm/dd/yyyy")
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The logic of this formula

    IIf ([StartDate]< Month(Date()),have the first of the month be place with format(mm/dd/yyyy),[StartDate]) would use the first if the start date is before today not after today.

    That aside, if you want to get the first of the current month you can also use

    DateSerial(Year(date()),Month(Date()),1)

    DateSerial is Locale independent which is not always the case if you use a Formatted Date String

    So IF you want Start date converted to the 1st IF the start date is after today you would need

    IIF([Start Date] > Date(),DateSerial(Year(date()),Month(Date()),1),[Start Date])

    If you want the 1st if Start date is before today then reverse the test to <
    Andrew

  4. #4
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Guys, you are the best!! Thanks for the help; that really solve my problems. By the way, I noticed I didnt place the month function on [startDate] which was preventing the code to execute properly if using RG selection. Below is the correction:

    IIf (Month([StartDate])< Month(now()), Format(month(now()) & "/1/" & year(now()),"mm/dd/yyyy") ,[StartDate])

Posting Permissions

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