Help with different Dlookup with 3 criteria

[lookforsmt]

it shows just "False"

 

[Gasman]

it shows just "False"

So your criteria is incorrect.!

Post your dcount AND your criteria AND the output of the debug.print

 

[lookforsmt]

? strcriteria
False

? dcount("*","tbl_Master", strcriteria)
10

 

[Gasman]

StrCriteria is meant to be a string?, not a boolean test, so whatever you set strCriteria to is completely wrong?

 

[lookforsmt]

HI! Gasman, my DB is already posted in post #12 by arnelgp.
Hope this helps.
I just tried to add the 4th validation (date) code but failed in process

 

[Gasman]

I'm trying to steer you into correcting the error yourself.
If I correct it, you have learnt nothing.?

Why would you have

"MICR_ndt =

as part of a string, yet

PDC_dueDate

is not part of a string?

Build each part seperately, debug print it each time, THEN put it together.

Start with the last part as that is what you have got wrong. Again, that should show you your mistake.

 

[lookforsmt]

Thanks i have tried to learn few vba codes earlier, but it will take me longer than expected.

i have 3 criteria MICR_ndt is one of them. initially i did not put date as criteria

 

[Gasman]

This is just about constructing a string.

You want something like

"MICR_ndt = 'Test' AND PDC_dueDate = #02/12/2019#"

To get that you have to concatenate the static part of text with the variable part (your control/variable values)

Already you should see a glowing error in your code.?

 

[lookforsmt]

Thanks Gasman, but still i m not clear how to check this in immediate window.

 

[Gasman]

It does not have to be in the immediate window. I just used that to test the values/strings. I often do that before I post a possible answer.

Debug.print each part of the string as you construct it.

Look CLOSELY at my 'something like' test string above.
What do you see missing that I have in my string that you do not.?

Then all you need to do is add the characters that are missing in your criteria.

Split that criteria into it's seperate parts and debug.print each one.
Get each correct. At the moment it is only the second one that is incorrect.
Then put them pack together remembering anything else you need to add if you have more than one criteria, then debug.print that and post back here what it shows.

I realise that you might think I am just being awkward, but the fact that you have had to come back to arnelgp (who has gone walkabout somewhere recently) for something like this, only goes to prove my point I believe

 

[lookforsmt]

hI! pls check the below code and the result in IW

    If DCount("1", "tbl_Master", "MICR_ndt = '" & strCriteria & "'" = "' strdate = DateValue('" & Me.PDC_dueDate) < 1 Then

? strcriteria

MICR_ndt = '0001298020207250293177718'21/10/2019

but still getting popup message. the above MICR_ndt is valid for date 21/10/2019. But if it is scanned for anyother date it should be invalid.

 

[Gasman]

Ok, now we are getting somewhere.

Does your criteria look 'ANYTHING' like mine?

Is MICR_ndt supposed to be in that format?

 

[lookforsmt]

NO, it should be with code only without the date

 

[Gasman]

So where do you think you have gone wrong?

As I said before build each part seperately.

The ndt part was working, so concentrate on the date part on it's own.

Then we can take it from there.

Again, look at the sample string I submitted a few posts back. That is what is 'should' look like.

You have to learn this stuff, else you will be back here all the time for the slightest change, pretty much what is happening now.?

I am sure there was a function mentioned a few weeks back that would help people like yourself, but with my memory, I'm blowed if I can remember what it was called.

 

[lookforsmt]

Well i too dont want to ask for help, but if you think that you can recall the function you mentioned then do post it back. It will be great help.

Well i am trying to learn but at the movement i have darkness in front of me, not sure if there is finish line in front or back of me.

 

[Gasman]

Well i too dont want to ask for help, but if you think that you can recall the function you mentioned then do post it back. It will be great help.

Well i am trying to learn but at the movement i have darkness in front of me, not sure if there is finish line in front or back of me.

Whenever you get like that, break the problem down into smaller parts.

Show me what you would use if you were just counting for the date. We'll take it from there.

I'll walk you through it, but you do need to learn. TBH this is pretty simple stuff, so it is good to start with this. Even look at what you have for the previous DCount code (which works?). You have (to me) glaringly obvious errors. However the key to all of this is 'understanding' the parameters and how to construct them. These are the same for all the domain functions (I think?).

 

[lookforsmt]

Thanks Gasman, but now i have given up. i put below code but in IW i am getting result as False

strDate = Me.Parent!PDCDueDate
    
strCriteria = "MICR_ndt = '" & strMIRC & "'" & PDC_dueDate = strDate

If DCount("1", "tbl_Master", strCriteria) < 1 Then
Debug.Print strCriteria

The data entered is valid, however it gives me attached snapshot error.

If you can correct the above code i would appreciate it.
thanks

 

[Gasman]

The False result is beacuse you have not changed anything since the start of this.:banghead:

The data entered might be valid, but your concatenation is not.

Think of the word 'AND' ? plus what I said about dates.

Look again at what the end result should look like (post 28) if you get this correct.?

No point giving up, that is not going to solve the problem. If you need this criteria as well then you have to get it correct.?

 

[lookforsmt]

i have changed the code as

    strCriteria = "MICR_ndt = '" & strMIRC & "'" And PDC_dueDate = strDate

i remember you mentioned to put # for date

#" & Format(Me!Textbox.Value, "dd/mm/yyyy") & "#")

    strCriteria = "MICR_ndt = '" & strMIRC & "'" And #" & Format(Me!PDCDueDate.Value, "dd/mm/yyyy") & "#") = strDate

 

[Gasman]

Almost there.
The acid test is what does the debug.print of strCriteria show?
That is what you are aiming at.

I'm not sure you need to complicate it with the format as mine worked without. I am in the UK and we use dd/mm/yyyy as default. However if you were to use the Format() function I would have thought it would be on the date entered not the date in the table.?

You are starting to swap fields/controls around. Be consistent.

I always write it as

TableField = variable AND NextTableField = variable1 AND lastTableField = variable2

along those lines.

Check your syntax around the AND text. Remember you are trying to build a valid string.

Stick with it, as you are making progress.