Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-03-2019, 02:47 AM   #31
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

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.

lookforsmt is offline   Reply With Quote
Old 12-03-2019, 02:59 AM   #32
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

Ok, now we are getting somewhere.

Does your criteria look 'ANYTHING' like mine?

Is MICR_ndt supposed to be in that format?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 03:07 AM   #33
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

NO, it should be with code only without the date

lookforsmt is offline   Reply With Quote
Old 12-03-2019, 03:19 AM   #34
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 03:29 AM   #35
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

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.
lookforsmt is offline   Reply With Quote
Old 12-03-2019, 03:47 AM   #36
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

Quote:
Originally Posted by lookforsmt View Post
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?).
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 06:03 AM   #37
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

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
Attached Images
File Type: png MICR_Scan_error1.png (70.3 KB, 4 views)

lookforsmt is offline   Reply With Quote
Old 12-03-2019, 06:11 AM   #38
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

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

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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 06:39 AM   #39
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

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
lookforsmt is offline   Reply With Quote
Old 12-03-2019, 06:48 AM   #40
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 07:03 AM   #41
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

i am sure the solution is in front of me, but as i told you i have limited vba knowledge. I am confused at the moment and with the last code that you provided.
lookforsmt is offline   Reply With Quote
Old 12-03-2019, 07:10 AM   #42
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

OK,
This is what I tested out in the IW, which is what I do when I have your problem.

Code:
tt="0001298020207250293177718"
dt=#20/10/2019#
strcriteria = "MICR_ndt = '" &tt & "' AND PDC_dueDate = #" & dt & "#"
? strcriteria
MICR_ndt = '0001298020207250293177718' AND PDC_dueDate = #20/10/2019#
? DCount("1", "tbl_Master", strcriteria)
1
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 12-03-2019, 07:27 AM   #43
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

Thanks Gasman for the patient and explaining me this. Putting the punctuation in the right place is very important.

i have the below working code.
Code:
Private Sub MICR_Scan_BeforeUpdate(Cancel As Integer)
    Dim strMIRC As String
    Dim strDate As Date
    Dim strCriteria As String
    
    intRemarks = 0
    
    strMIRC = Trim(ReplaceChars(Me.MICR_Scan) & "")
    strDate = Me.Parent!PDCDueDate
    strCriteria = "MICR_ndt = '" & strMIRC & "' And PDC_dueDate = #" & strDate & "#"
    
'    TableField = variable And NextTableField = variable1 And lastTableField = variable2
    
    If Len(strMIRC) < 1 Then
        MsgBox "Need to enter Cheque MIRC", vbExclamation + vbOKOnly
        Exit Sub
    End If
    
    If DCount("1", "tbl_temp_Validate", "MICR = '" & strMIRC & "'") > 0 Then
        Me.Undo
        'Cancel = True

        'Message box warning of duplication
        MsgBox "Duplicate Cheque MICR and CreditAC already Scanned " & "" _
            & vbCr & Me.MICR_Scan, vbInformation _
            , " Duplicate MICR"
        Exit Sub
    End If
'----------------------------------------------------------------------------------
'    If DCount("1", "tbl_Master", "MICR_ndt = '" & strMIRC & "'") < 1 Then
'----------------------------------------------------------------------------------

       If DCount("1", "tbl_Master", strCriteria) < 1 Then

Debug.Print strCriteria
'Debug.Print strDate
        
        Me.Status.Value = "UnMatch"
        Me.cboRemark = DLookup("RejectReason", "tbl_RejectReason", "SrNos = 5")
        intRemarks = intRemarks + 1
        'Cancel = True
        'Undo duplicate entry
        '         Me.Undo
        MsgBox "Incorrect MICR Scanned " & "" _
            & vbCr & Me.MICR_Scan & " " _
            & vbCr & "Kindly check and correct Scanned MICR.", vbInformation _
            , " MICR Information"
    End If
End Sub
Thank you once again
lookforsmt is offline   Reply With Quote
Old 12-03-2019, 07:30 AM   #44
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Help with different Dlookup with 3 criteria

Well I made you work for it I admit , but you should get some satisfaction for getting part way there.

Sadly, there is no alternative to learning. You should have learnt a few debugging techniques, if nothing else?

Google has always helped me. That is my first place of call, pretty much for anything these days.

What we did before it, I cannot remember.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
lookforsmt (12-03-2019)
Old 12-03-2019, 07:38 AM   #45
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 558
Thanks: 75
Thanked 2 Times in 2 Posts
lookforsmt is on a distinguished road
Re: Help with different Dlookup with 3 criteria

Rightly, there is no alternate for learning. I have tried to learn this but its difficult to understand from the middle, i need to understand from the beginning like kindergarten.

Yes, before posting any thread i look for Google, everywhere for an answer and some of you guys are amazing who not only provide help but also guide to the right path.
keep it up and God bless you.
Thanks

lookforsmt is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dlookup with two criteria peskywinnets Modules & VBA 7 07-19-2019 04:03 AM
DLookUp as criteria within DLookup chuckcoleman Modules & VBA 15 04-10-2019 11:39 AM
Dlookup with a between criteria cablegirl Queries 4 08-07-2013 06:32 PM
DLookup criteria paul25_uk Forms 4 09-14-2004 12:15 AM
DLookup with 2 criteria. hooi General 5 10-22-2003 01:01 AM




All times are GMT -8. The time now is 10:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World