DLookup using equals or less than

[cstickman]

Hey guys,

I received such great help yesterday with another DLookup issue so I am hoping someone can help with this one.

I have a table with account notes and I am trying to find one code and the date it happened. Then find another code that is a date less than or equal to the first code date.

Below is what I have so far and I am getting nada for results.

 Me.txtfiling = DLookup("NIIN", "tblaccountnotes", "[acctnumber]= '" & Me.txtacctnum & "' AND ([code]= 'XNON' AND [code] <= 'XASU')")

So I want to find the code XNON, but it has to be a date equal or less than the code XASU.

I read about nested SQL statements, but not sure if that would work or how to write it. Any help would be greatly appreciated. Thanks!!

 

[CJ_London]

a date equal or less than the code XASU

where does the date come into it?

 

[plog]

1. Quit randomly inserting CODE tags into your posts. Why is the <= 'XASU')") part in its own section? Does that mean something? Confusing. Put all your code together.

2. As CJ has asked, you make no mention of a date in your code.

3. Demonstrate what you want to occur with data. Show us some sample data in tblaccountnotes (include all relevant fields with names) and then tell us what specific value you want your code to find. Examples help, unworking code snippets don't.

 

[cstickman]

The code tags I am not sure why it is randomly splitting that apart. Maybe because the code has [] and it is cancelling the code tags.

So what I am trying to do is get a date based on system code 'XNON', but it has to be date that is equal or less than system code 'XASU'.

The dates come from the table in column labeled NIIN

In another text box I have the date displayed from 'XASU'. The code 'XNON' has to happen in the system before code 'XASU' so I am trying to get the date for 'XNON' that occurred before the system code 'XASU'.

I hope that makes more sense now.

Here is sample data:
ID acctnumb custname NIIN time code notes
1 123 Donald 1/1/17 XNON
2 123 Donald 1/2/17 XASU
3 123 Donald 1/3/17 XNON

So I am trying to get the code XNON that happened before XASU. Code XASU will only be in the table once, but code XNON can have hundreds. So I just want to identify code XNON that happened before or on the same day as code XASU.

 

[plog]

Yes, I bet you are right about your code field screwing up the formatting.

As for your explanation, it helps a little, however I still would like an example. Provide data. Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
Sally, 12/23/2016, 19
David, 1/8/2017, 45
Tim, 2/9/2015, 67

 

[cstickman]

tblaccountnotes
acctnumb, custname, NIIN, code
123, Donald, 1/1/17, XNON
123, Donald, 1/2/17, XASU
123, Donald, 1/3/17, XNON

 

[plog]

That's the data, now what do you want to happen?

 

[cstickman]

I would like to get the date for code XNON that happened before code XASU. The XNON code can happen same day or before.

 

[plog]

You are going to need 2 lookup calls. One to get the XASU date, then another to use that date.

Notice I didn't say 2 Dlookups, that's because you've posted an example that I suspect might be too simplified. Is it possible to have more than one XASU record? If so, you would use a DMax or a DMin to retrieve the date you want. Then turn around use that date in your Dlookup that you have posted initially.

 

[cstickman]

I thought that my DLookup was too simple as well, but did not know really how to move forward.

The XASU code can only be ran once per account number. So do you have an example of how the two lookup values would work?

One thing to mention the XASU date is being populated in textbox txtfiling already if we can use it some how.

Thank you for taking the time to help me with this problem.

 

[gemma-the-husky]

with a date

"datevalue <= #" & format(XASU,"long date") & "#"

The date has to be surrounded by octothorps. if you are might a non-US date format domain, you can get date ambiguity. So #11 dec 2016#.

using 12/11/2016 introduces the ambiguity. Is this 12th Nov, or 11th Dec?

I generally use "long date" to resolve this. There are other methods.

 

[CJ_London]

Is it possible to have more than one XASU record?

or more than one XNON record before the XASU date?

 

[cstickman]

CJ_London,

Yes it can numerous XNON before the other code. So I would need the one closest to the XASU code.

Gemma - I have two huskies myself, 1 female and 1 male. They are the best dogs in the world. My girl (Aiyana) is deaf and we rescued her from a shelter. I have never seen a stronger bond between her and I in any other of my dogs. She is fantastic and you would not even know she was deaf.

 

[plog]

Yes it can numerous XNON before the other code. So I would need the one closest to the XASU code.

'Closest' is different than 'before'. In the example you provided both of those XNON records are equally close to the XASU record. I suggest you provide better sample data. Please provide enough to cover all cases and fully demonstrate what you want. Provide sample data that demonstrates what happens in these cases:

Multiple XASU records.
XNON records before and XASU record
No XASU record
No XNON record
XNON records equidistant from XASU record

 

[cstickman]

So here is an actual example
acctnum, NIIN, code, notes
123, 1/1/17, XALOAD, order loaded
123, 1/3/17, XNON, Order tested
123, 1/5/17, XNON, Order tested
123, 1/7/17, XAINS, Order inspected
123, 1/9/17, XNON, Order tested
123, 1/15/17, XASU, Order completed
123, 1/17/17, XNON, order tested
123, 1/18/17, XAINS, Order inspected
123, 1/18/17, XNON, Order tested
123, 1/19/17, XASHIP, Order shipped

So I need to know the date for XNON that was right before the XASU. What I am trying to do is the unit needs to be tested before it is coded as XASU. The engineer needs to test it within so many days depending on the item before the order can be coded as completed, sent to final inspection and final testing. Until the final inspection the part needs to be tested every couple of days until finally shipped. So I need to ensure they tested it say within 5 days before being marked as completed.

 

[plog]

I focused on your data and this explanation:

So I need to know the date for XNON that was right before the XASU.

To achieve that you can use this line of code:

=DLookup("[NIIN]", "tblaccountnotes", "code='XNON' AND [NIIN]<#" & DLookup("[NIIN]", "tblaccountnotes", "code='XASU'") & "#")

 

[cstickman]

That works perfect, but how do we change the code to check if it happened on the same day?

Thanks for all of your help!!

 

[plog]

Change the < sign before the second DLookup to <=

 

[cstickman]

I was able to try changing it to <= before the second DLookup. I ran a few accounts this morning and noticed that the code is pulling the date for the first XNON code instead of the code by XASU.

Right now it is bypassing all the XNON codes and just reporting the date for the very first one in the table. Any idea why it is skipping all of them, but the very first one. It reminds me of VLOOKUP where it just reported the first one it found in the spreadsheet. Anyway that we can fix it?

 

[plog]

Sorry about that. Change the first DLookup to DMax.