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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-25-2019, 11:32 PM   #16
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 Arnelgp for your kind support. I have tweaked the code to fit my project.

Thanks I will close the thread now as solved

lookforsmt is offline   Reply With Quote
Old 12-02-2019, 10:20 AM   #17
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! Arnelgp,

i had to repost this message as i have another challenge.
It works fine except in one scenario where if the user scans a cheque for any other then the date mentioned in the main form, it still accepts.

I wanted the 4th criteria to check the date (PDC_dueDate) before accepting as Match, else throw a popup message as "Invalid Date" as the 5th Rejection.

i am not sure whether to add the before update on the PDC_dueDate in the subform or add the check on the before update on MICR_Scan.

I tried below code but i am getting error

Code:
    If DCount("1", "tbl_Master", "MICR_ndt = '" & strMIRC & "'" & PDC_dueDate = strDate) < 1 Then
        
        Me.Status.Value = "UnMatch"
        Me.cboRemark = DLookup("RejectReason", "tbl_RejectReason", "SrNos = 5")
I am getting popup message as Invalid Date and i have added 5th Reject reason in tbl_RejectReason

can you assist
lookforsmt is offline   Reply With Quote
Old 12-02-2019, 10:36 AM   #18
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

Make the criteria a string in itself and then debug.print it.

Code:
strCriteria =  "MICR_ndt = '" & strMIRC & "'" & PDC_dueDate = strDate
Debug.Print strCriteria

If DCount("1", "tbl_Master", strCriteria) < 1 Then
        
        Me.Status.Value = "UnMatch"
        Me.cboRemark = DLookup("RejectReason", "tbl_RejectReason", "SrNos = 5")
That will show you your errors.
Dates are normally surrounded by #, so not sure why you have it as a string

HTH

__________________
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-02-2019, 12:15 PM   #19
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! Gasman,
i am not getting any error, but a popup msg Invalid Date with status "UnMatch". But the data is correct, so it should give me status as "Match"

in the immediate window i am getting as False

i have inserted the code you provided and below is my full code on MICR_Scan before update

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) & "")

    
    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
        
strCriteria = "MICR_ndt = '" & strMIRC & "'" & PDC_dueDate = strDate
Debug.Print strCriteria

    If DCount("1", "tbl_Master", strCriteria) < 1 Then
        
        
        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
lookforsmt is offline   Reply With Quote
Old 12-02-2019, 12:25 PM   #20
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

What does the debug.print show?

That will show you your error.? As I said dates need to be surrounded by # most times. This being one of them.?

From an immediate window
Code:
tt=12/01/2016
strcriteria="TransactionDate < #" & tt & "#"
? strcriteria
TransactionDate < #01/12/2016#

? dcount("*","Transactions", strcriteria)
 7
HTH
__________________
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-02-2019, 12:28 PM   #21
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

it shows just "False"
lookforsmt is offline   Reply With Quote
Old 12-02-2019, 12:40 PM   #22
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
it shows just "False"
So your criteria is incorrect.!

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

__________________
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-02-2019, 12:47 PM   #23
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

? strcriteria
False

? dcount("*","tbl_Master", strcriteria)
10
lookforsmt is offline   Reply With Quote
Old 12-02-2019, 12:51 PM   #24
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

StrCriteria is meant to be a string?, not a boolean test, so whatever you set strCriteria to is completely wrong?
__________________
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-02-2019, 12:51 PM   #25
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! 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
lookforsmt is offline   Reply With Quote
Old 12-02-2019, 01:02 PM   #26
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

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.
__________________
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-02-2019, 01:09 PM   #27
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 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
lookforsmt is offline   Reply With Quote
Old 12-02-2019, 01:16 PM   #28
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

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.?
__________________
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-02-2019, 06:59 PM   #29
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 still i m not clear how to check this in immediate window.
lookforsmt is offline   Reply With Quote
Old 12-03-2019, 12:45 AM   #30
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

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

__________________
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
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 12:29 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