Results 1 to 8 of 8
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Expression not returning what is intended


    I am missing something. No error. It just doesn't return the correct answer. On the first image against Crazy clay, there should not be 30.00 in field MngmntSlsPrice011. The SalesInvoice date is 20 Feb 2018, which falls between Fromdate192 and Todate192b. Could it be the format of the date fields? This expression the date is "now", because doesn't fall between the Fromdate and Todate, it should be zero?

    MngmntSlsPrice011: CCur(Nz(DLookUp("SalesPrice192","q02SalesPrice","C mbEnt_ID192 =" & [IntItmID011] & " And #" & Format(Now(),"yyyy mm dd") & "# Between [FromDate192] And [ToDate192b]"),0))
    Click image for larger version. 

Name:	SalesPrice08.png 
Views:	13 
Size:	60.1 KB 
ID:	41489Click image for larger version. 

Name:	SalesPrice05.png 
Views:	12 
Size:	9.0 KB 
ID:	41490Click image for larger version. 

Name:	SalesPrice06.png 
Views:	12 
Size:	14.4 KB 
ID:	41491Click image for larger version. 

Name:	SalesPrice07.png 
Views:	12 
Size:	22.3 KB 
ID:	41492
    Last edited by Perfac; 04-07-2020 at 08:52 AM. Reason: Add something.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I haven't worked with dates much lately, and to add to my forgetfulness on their characteristics is the fact that I use US date format, which means all is easy. So I will take a stab and hope my memory serves. First, the format function returns a string, which for display purposes ought to be OK. However, in your query you use just the date value that the query returns you might be better off. Not sure why you think you need to format a date for a query. An actual date value is stored as a type of double (number) but the format function can display the date in a lot of ways without altering the value. However, if you format 02/18/2018 (February 18) as 18/02/2018 Access will likely return nothing as there is no date with an 18th month (US format). In some cases such as 05/05/2018 getting results would be hit and miss because that one is a valid date regardless of which way you look at it. So remember that how you format your dates means nothing as far as how the values are actually stored. I think you are better off just passing what is expected and format your form and report controls instead. You might see 18/02/2018 in your table field, but the value it represents is really 02/18/2018.
    Last edited by Micron; 04-07-2020 at 08:47 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    In a Dlookup, all the fields you are looking up must be in the domain specified. SalesInvoiceDate011 is not in domain, q02SalesPrice.

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Sorry I made changes to my thread, check it again.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    SalesInvoiceDate011 is not in domain, q02SalesPrice.
    I don't get that? The field and domain is "SalesPrice192","q02SalesPrice" and they are both in the query, and so is the field for the criteria, C mbEnt_ID192
    However, your reply did make me look closer at the whole thing rather than focusing on the date format. This And #" & Format(Now(),"yyyy mm dd") & "# Between is saying where someDate is BETWEEN... You can't do that. The reference for BETWEEN when used like that has to be a field in that domain, not some date you construct. Thus it would have to be where dateField BETWEEN this AND that.

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I use almost the same expression(see below) it works well, only the date differ and here it works referring to the date on the sales invoice. In query q04CogsUnitPrice02 it returns the SalesPrice192 effective as per the Sales Invoice Date. In query q04CogsUnitPrice04 it returns the SalesPrice192 on the current date. Because Now() falls outside the from and to date, it should be zero? Logic tells me the issue is with Now() in the above problem.

    MngmntSlsPrice011: CCur(Nz(DLookUp("SalesPrice192","q02SalesPrice","C mbEnt_ID192 =" & [IntItmID011] & " And #" & Format([forms]![f02SalesInvoice]![DocumentDate004a],"yyyy mm dd") & "# Between [FromDate192] And [ToDate192b]"),0))
    Last edited by Perfac; 04-07-2020 at 09:28 AM. Reason: Change something small.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, you could be right. Now includes time. If your date field does not store a real time component (i.e. it has defaulted to 00:00:00) then virtually nothing will match your field values because 01/01/2019 00:00:00 AM does not match Now if Now happens to be 01/01/2019 02:30:15 PM
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by Micron View Post
    I don't get that?
    Kinda hard to double check now as the original post has been edited...

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

Similar Threads

  1. Replies: 8
    Last Post: 03-19-2018, 02:50 PM
  2. File db will not save the way it was intended
    By Lou_Reed in forum Access
    Replies: 8
    Last Post: 04-07-2017, 11:44 AM
  3. Replies: 6
    Last Post: 09-26-2016, 10:36 PM
  4. Replies: 2
    Last Post: 09-16-2014, 09:00 AM
  5. form not displaying as intended
    By markjkubicki in forum Forms
    Replies: 1
    Last Post: 07-24-2010, 07:04 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