Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: DLookup Criteria Between Two Dates

    Hi all,

    Trying to use DLOOKUP() to see if a date in my query is in another table and if so, return the Fiscal Month from that table otherwise Null.
    Here is where I am at so far, not quite working.

    The expression you entered contains an invalid date value.
    Code:
    FiscalMonth:IIF(ISERROR(DLOOKUP("FiscalMonth","tblCalendar",[Opened_Date] Between # [tblCalendar].[DateBegin] And [tblCalendar].[DateEnd]),NULL,DLOOKUP("FiscalMonth","tblCalendar",[Opened_Date] Between # [tblCalendar].[DateBegin] And [tblCalendar].[DateEnd]))
    I also tried:

    Code:
    FiscalMonth:IIF(ISERROR(DLOOKUP("FiscalMonth","tblCalendar",[Opened_Date] Between # [tblCalendar].[DateBegin] # And # [tblCalendar].[DateEnd]#),NULL,DLOOKUP("FiscalMonth","tblCalendar",[Opened_Date] Between # [tblCalendar].[DateBegin] # And # [tblCalendar].[DateEnd] #))
    but I receive the same error.

    thanks
    w

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are referring to columns in a query (and effetivley a domain fucntion such as DLOOKUP is a query) then Im pretty certain you do not need to delimit the date columns with #. the SQL engine is smart enough to understand its a date and handle appropriately.

    you would need to delimit a date literal eg 16/11/2015, as if you do not then the SQL engine could well interpret that 16 divided by 11 divided by 2015. As a date column is stored as a number already there is no need for the SQL engine to do other processing for the comparison

    of course depending on where you are using this value (if its in a query what you could do is just do a join directly to the table in question. Domain functions should never, well hardly ever, be used in queries. I don't know the mechanism behind the domain function but I guess it opens a connection, performs a query then closes the connection and returns the data. if you are already using a query then you usually don't need to take the performance hit of using the domain function. and it cna be a severe perfomrance hit
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks healdem,

    I'll add all all dates to my calendar table and create the join to the data table.

    thx
    w

Posting Permissions

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