Results 1 to 3 of 3
  1. #1
    SusanCoder is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    30

    syntax to use a variable in "between date and date" statement

    Looking for syntax advice. I'm building the queries in the Query Designer. I have determined the current fiscal begin and end date from Date(Now) as named variables, and am trying to use the begin and end date variables from the first query in a subsequent query in a "between date and date" statement. I'm getting this error msg: query must contain at least one table or query.



    SQL from my first query to determine the current fiscal begin and end date. This query is working:
    Code:
    SELECT IIf(Month(Now()) In (10,11,12),"10/1/" & Year(Now()),"10/1/" & Year(Now())-1) AS FFYBeginDate, IIf(Month(Now()) In (1,2,3,4,5,6,7,8,9),"9/30/" & Year(Now()),"9/30/" & Year(Now())+1) AS FFYEndDate;
    The results for FFYBeginDate=10/1/2015, and the results for FFYEndDate=9/30/2016.

    My subsequent query results in the error message mentioned above:
    Code:
    SELECT zpreqryFundsFFYCalc1.FFYBeginDate, zpreqryFundsFFYCalc1.FFYEndDate, FundsSubTable.UniqueID, FundsSubTable.Code, FundsSubTable.DateOfService, FundsSubTable.Amount, FundsSubTable.Note
    FROM zpreqryFundsFFYCalc1, FundsSubTable
    WHERE (((FundsSubTable.Code) In ("52","56")) AND ((FundsSubTable.DateOfService) Between [zpreqryFundsFYCalc1].[FFYBeginDate] And [zpreqryFundsFYCalc1].[FFYEndDate]))
    ORDER BY FundsSubTable.Code, FundsSubTable.DateOfService DESC;
    Thanks for your advice.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't think you need all that

    If your FY runs from October through September (i.e. FY 2016 runs from 10/1/2015 through 9/30/2016) then your formula is as easy as:

    FY: datepart("yyyy", [datefield]) + iif(datepart("m",[Datefield]) >=10, -1,0)

    EDIT: Sorry clicked the wrong button

    From here it's a short step to get the beginning and end dates

    i.e.

    FYBegin: dateserial(datepart("yyyy", [datefield]) + iif(datepart("m",[Datefield]) >=10, -1,0) -1, 10,1)

    FYEnd: dateserial(datepart("yyyy", [datefield]) + iif(datepart("m",[Datefield]) >=10, -1,0) , 9, 30)

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

Similar Threads

  1. Replies: 4
    Last Post: 03-07-2016, 10:08 PM
  2. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  3. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  4. Replies: 2
    Last Post: 09-29-2012, 11:22 PM
  5. Replies: 4
    Last Post: 03-14-2012, 09:05 AM

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