Results 1 to 5 of 5
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    user if statement to get current year value

    I am using a variable to get the year - DateSerial(Year(date),1,1) gives me 1/1/2019. However, I am using this for a parameter for a query and I need to use 2019 for 4/1/2019 - 3/31/2020. So I need an if statement that will use the previous year if it is in Jan, Feb, Mar

    like
    Dim QCurrentMonth as Integer
    QCurrentMonth = Month (Date())

    If QCurrentMonth >0 and < 4 Then


    QCurrentMonth =DateSerial(Year(date)-1,3,31)
    Else
    QCurrentMonth = DateSerial(Year(date),3,31)
    End If

    However, I can't even test if it works because the Month(Date()) does not work (i tried in the immediate window) and in the vba editor it also does not like - If QCurrentMonth >0 and < 4 Then

    I need some help with getting the current month as a number
    I need some help with getting the if statement to determine if it is a 1,2 0r 3

    Thanks

  2. #2
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Ok, it looks like Month(Date()) does work, but just not in the immediate window.

    Also, I can get the if statement to work if I just use < 4 but I would prefer to be able to specify a range like 1-3. In linux I can use a regex and do [1-3] is there anything like that for VBA?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters it does work in the Immediate window:

    ?Month(Date())
    11

    Your test would have to be

    If QCurrentMonth >0 and QCurrentMonth < 4

    Why do you want to complicate things when <4 works? It's what I would use.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    yes, I have used < 4 and it works for now but due to my system and some point I may have to do months 1 and 2 but not 3 and then 4 and 5 etc.

    i tried in the immediate window and it came back blank but I did not put a ? mark in front of it. I will try that.
    Thanks for the logic, I have another place where I need codes 1-3 not 4 or 5 but also 7-9 so I will try it there.
    Thanks for the help.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Given what you're describing I'd probably use Select/Case instead of If/Then. You can specify individual values and/or ranges. From your examples:

    Case 1, 2, 4 To 5
    Case 1 To 3, 7 To 9
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-19-2017, 01:09 PM
  2. Replies: 19
    Last Post: 01-24-2016, 07:48 PM
  3. Replies: 4
    Last Post: 07-13-2015, 02:06 PM
  4. Replies: 3
    Last Post: 06-22-2015, 06:36 AM
  5. Replies: 1
    Last Post: 04-20-2015, 03:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums