Help with different Dlookup with 3 criteria (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
? strcriteria
False

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

Gasman

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
StrCriteria is meant to be a string?, not a boolean test, so whatever you set strCriteria to is completely wrong?
 

lookforsmt

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
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

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
I'm trying to steer you into correcting the error yourself.
If I correct it, you have learnt nothing.?

Why would you have

Code:
"MICR_ndt =
as part of a string, yet
Code:
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

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
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

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
This is just about constructing a string.

You want something like

Code:
"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

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
Thanks Gasman, but still i m not clear how to check this in immediate window.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
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:D) for something like this, only goes to prove my point I believe
 

lookforsmt

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
hI! pls check the below code and the result in IW

Code:
    If DCount("1", "tbl_Master", "MICR_ndt = '" & strCriteria & "'" = "' strdate = DateValue('" & Me.PDC_dueDate) < 1 Then
Code:
? 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

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
Ok, now we are getting somewhere.

Does your criteria look 'ANYTHING' like mine?

Is MICR_ndt supposed to be in that format?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
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

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
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

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
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

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
Thanks Gasman, but now i have given up. i put below code but in IW i am getting result as False

Code:
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
 

Attachments

  • MICR_Scan_error1.png
    MICR_Scan_error1.png
    70.3 KB · Views: 92

Gasman

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
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

Registered User.
Local time
Today, 19:05
Joined
Dec 26, 2011
Messages
672
i have changed the code as
Code:
    strCriteria = "MICR_ndt = '" & strMIRC & "'" And PDC_dueDate = strDate

i remember you mentioned to put # for date
Code:
#" & Format(Me!Textbox.Value, "dd/mm/yyyy") & "#")

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

Gasman

Enthusiastic Amateur
Local time
Today, 15:05
Joined
Sep 21, 2011
Messages
14,044
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.
 

Users who are viewing this thread

Top Bottom