Validate future year months to start of the month

[lookforsmt]

Hi! I have DB where I validate the due date before capturing the envelope barcode in the field "envbrcd", this works fine till now. But recently changed the approach of filing the documents for future years (i.e. year 2018 onwards as month wise only instead of date wise)

To be more clear i am demonstrating my DB.
e.g. tbl_Master has below fields:

chqbrcd (This is field with 13 digits characters - This is 1st validation which i don't want to change)
envbrcd (This is field with code "E-080118-1" as the envbrcd code matching to another tbl_Master_envbrcd)
pdc_duedate (This is the actual date of the field "08/01/2018")

The change that i am trying to put in my vba code:
Lets say, "pdc_duedate" date is 08/01/2018 or any date in month of Jan-18, then it validates and allows the user to input in the envbrcd field as "E-010118-1. This will be my new code as, E-010118-1, E-010218-1,E-010318-1, and so for all the future years and months upto 2025

Below is my code currently which i want to amend but not sure how to do this and if the user tries to scan future dated code it gives a popup message that the year is future date and he opens another form for updating these records

Private Sub chq_brcd_e_BeforeUpdate(Cancel As Integer)
    Dim SID As String
    Dim stlinkcriteria As String
    Set rsc = Me.RecordsetClone
    Dim a As String
    Dim b As String
        a = Me.chq_brcd_e.Value
        b = Left(a, 2)
    Debug.Print c
    If b <> "EN" Then
    Me.Undo
    MsgBox "EN is not the first character! Kindly check  " & b
    End If
    Set rsc = Nothing
    
    stlinkcrieria = "[chq_brcd_e]" & "'" & SID & "'"
    'Check tbl_Masterchqbrcd for duplicate barcode
    If DCount("*", "[tbl_Master]", "[chqbrcd] ='" & chq_brcd_e & "' and [duedate] = " & SQLDate(Due_date_e)) = 0 Then
        Me.Undo
        MsgBox "Warning Cheque Barcode " _
            & SID & " has already been Scanned, " _
            & vbcr & vbcr & "Kindly check previous record and Re-scan correct Barcode,", vbInformation _
            , "Duplicate Barcode Information"
    End If
    Set rsc = Nothing
End Sub

Function SQLDate(dt As Date) As String
On Error Resume Next
    If IsDate(dt) Then SQLDate = Format(dt, "\#dd-mmm-yyyy#")
End Function

i am trying to validate any future year date to be validate the 1st date of the month

any help from anyone is much appreciated.
Thank you

 

[pbaldy]

For starters your date format should be changed:

http://allenbrowne.com/ser-36.html#Format

Beyond that, I'm not sure what the question is.

 

[lookforsmt]

Thank you for your response, dear pbaldy

I already have the below code which I want to tweak a bit. But not sure how to add the changes in the below code

If DCount("*", "[tbl_Master]", "[chqbrcd] ='" & chq_brcd_e & "' and [duedate] = " & SQLDate(Due_date_e)) = 0 Then

when I scan the chqbrcd code it checks for the duedate and allows user to scan envbrcd for that particular row. Now I want it to check the duedate but here irrespective of any date it should allow me to scan only for the start of the month as explained in my previous post.

below is the sample table which is attached to a form (before)

chqbrcd duedate envbrcd
E-050117-0010 04/07/2018 E-040718-1

Now the change I want

chqbrcd duedate envbrcd
E-050117-0010 04/07/2018 E-010718-1
E-060417-0011 12/09/2018 E-010918-1

if you noticed in the envbrcd field it allows the user to scan the barcode only for the start of the month date which is 010718 & 010918 and not the date which is 4th Jul & 12th Sep respectively.

I want the code to check the duedate field if any dates for 2018 onwards then allow the user to capture only as the start of the month. Else if it is for 2017 then the existing code should allow the user to capture the duedate

Hope I was clear, else can I attach my db for clear understanding.

 

[Cronk]

For a start, there is a problem with your Dlookup test. Paul has already advised your date format is incorrect. The other thing is the logic is back the front. You want to undo if the count is greater than one.