If column is not empty (1 Viewer)

Alhakeem1977 · Apr 25, 2018

Your kind help to do an action if a column is not empty between two dates:
* DDate is the criteria of the dates.
* SenderID is the is the empty or not empty.

Code:

If Not IsNull(DLookup("[SenderID]", "DeptSeq", "[DDate] >= #" & txtDF & "# AND [ddate] <= #" & txtDT & "#")) Then

Thanks in advance

isladogs · Apr 25, 2018

Try

Code:

If Nz(DLookup("[SenderID]", "DeptSeq", "[DDate] >= #" & txtDF & "# AND [ddate] <= #" & txtDT & "#"),"")<>"" Then

OR

Code:

If Nz(DLookup("[SenderID]", "DeptSeq", "[DDate] Between #" & txtDF & "# AND #" & txtDT & "#"),"")<>"" Then

arnelgp · Apr 25, 2018

probably should use Dcount():

If DCount("1", "DeptSeq", "IsNull([SenderID])=False And ([DDate] >= #" & Format(txtDF, "mm/dd/yyyy") & "# AND [ddate]<= #" & Format(txtDT, "mm/dd/yyyy") & "#)") <> 0 Then

isladogs · Apr 25, 2018

arnelgp said:
probably should use Dcount():

If DCount("1", "DeptSeq, "IsNull([SenderID])=False And ([DDate] >= #" & Format(txtDF, "mm/dd/yyyy") & "# AND [ddate]<= #" & Format(txtDT, "mm/dd/yyyy") & "#)") <> 0 Then

Good point to have mentioned needing date format to be mm/dd/yyyy which I neglected in my answer
However, the overall code seems unnecessarily complex to me - its also missing a " after DeptSeq

Here's a modified version of my previous code with date formatting added

Code:

If Nz(DLookup("[SenderID]", "DeptSeq", "[DDate] Between #" & Format(txtDF,"mm/dd/yyyy") & "# AND #" & Format(txtDT,"mm/dd/yyyy") & "#"),"")<>"" Then

arnelgp · Apr 25, 2018

Good eyes.. only typing on my smallphone.

Alhakeem1977 · Apr 25, 2018

Thank you very much for your interaction. I have tested all the codes that have been provided to me all of them doing the same task. Only works at the earliest date for example: If I want to take an action after or equal to any date >= 13-Feb-2018 Then
14-Feb-2018, 15-feb-2018, 16-Feb-2018 If the field code is 14-Feb-2018 the code works fine but if it is on 15-Feb-2018 or grater does not work.

The code required to be used in any field between the two dates of txtDF AND txtDT .

Thank aganin.

isladogs · Apr 25, 2018

Please upload some sample data that works and some that doesn't

Alhakeem1977 · Apr 25, 2018

Please find attached.

isladogs · Apr 25, 2018

I've had a quick look at your database
I think one problem may be your date formatting using ddd/dd/mm/yyyy.
Replace all of these with Short date (dd/mm/yyyy here in the UK)

Next problem is I can't find the code from this thread so I've no idea where to test it
Can you please provide some additional info so someone can assist

Alhakeem1977 · Apr 26, 2018

Hi,
Sorry I was sleeping, exactly the code you will find it in the Update button.

isladogs · Apr 26, 2018

Not at my computer now. Which form?

Alhakeem1977 · Apr 26, 2018

It's in the startup form (Holidays) the code behind (Update) button.

The code should check if there is any data in the table (DeptSeq) column (SenderID) between the dates in the column (DDate) with the following situations :

1. If there is data in the column (SenderID) before the (txtDF) and no data after-Is Null (txtDF) then run the (qryDelete) and then insert the calendar dates from (txtDF) till (txtDT) which the application do that already.

2. If there is data in the column (SenderID) before the (txtDF) and data after- Not Null (txtDF) then do not run the (qryDelete) and return a message box informing the user that there are data in the SenderID column to amended.

3. If there is no data entirely in the current year then when click the Update button will return inserting the whole calendar dates excluding Friday & Saturday and the Holidays from table(Holiday) dates.

In all scenarios, the code returns the whole calendar dates excluding Friday & Saturday and the Holidays.

Thanks in advance.

isladogs · Apr 27, 2018

Sorry about the delayed reply. This got overlooked.

I'm not clear about the purpose of any of this so you need to check what I've done fits your requirements ... which to me seem confused

First of all, I couldn't get DLookup to work either though I can't understand why not.
However a simplified DCount does work

Having said that I'm not sure why you are checking for values with a SenderID as your code deletes ALL records in the date range & then replaces all of them using the DAOAdding procedure

Suggest you either
a) only delete the records with a SenderID & replace just those records
Or
b) delete & replace all of them in which case there is no point checking using the DCount code anyway!

NOTE: The DAOAdding procedure itself could be replaced with a simple APPEND query which would run MUCH faster

I've changed the order of your code as it didn't make sense as written
Modified code below with some comments:

Code:

Private Sub cmdUpdate_Click()
   
On Error GoTo errHandler
   
 [COLOR="SeaGreen"]  'save changes[/COLOR]
    If Me.Dirty Then Me.Dirty = False
   
    Dim N As Integer
  [COLOR="SeaGreen"]  'I couldn't get this to work
    'N = Nz(DLookup("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#"), 0)[/COLOR]
    
   [COLOR="seagreen"] 'This does work[/COLOR]
    N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")
    
    If MsgBox("You are about to execute a major operation ! Do you really want to continue ?" & vbCrLf, vbYesNo, "Major Operation") = vbNo Then
            MsgBox "Your operation execution is canceled !", vbOKOnly, "Operation Canceled"
            Exit Sub
    Else
        If N > 1 Then
            MsgBox N & " records in the selected date range will be deleted.", vbCritical, "Existing Data"
        ElseIf N = 1 Then
            MsgBox N & " record in the selected date range will be deleted.", vbCritical, "Existing Data"
        Else
            MsgBox "No records to delete"
        End If
       
      [COLOR="seagreen"] 'this deletes all existing records - not just those where SenderID Is Not Null[/COLOR]
        CurrentDb.Execute "DELETE DeptSeq.*" & _
            " FROM DeptSeq" & _
            " WHERE (DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#)", dbFailOnError
            
      [COLOR="SeaGreen"]  'this would only delete those where SenderID Is Not Null
        'however it can't be used as your DAOAdding procedure replaces all values[/COLOR]
      [COLOR="seagreen"] '  CurrentDb.Execute "DELETE DeptSeq.*" & _
            " FROM DeptSeq" & _
            " WHERE (((DeptSeq.DDate) Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#) AND ((DeptSeq.SenderID) Is Not Null))", dbFailOnError[/COLOR]
            
[COLOR="seagreen"]
        'add new records - NOT CHECKED or ALTERED
        'However an APPEND query would do the same job and MUCH faster[/COLOR]
        DAOAdding
    [COLOR="SeaGreen"]  '  On Error Resume Next 'NOT NEEDED[/COLOR]
        MsgBox "Your operation execution has been completed successfully.", vbOKOnly, "Successful Operation"
    End If
    
ExitProc:
    Exit Sub
    
errHandler:
    MsgBox Prompt:=Err & ": " & Err.Description, buttons:=vbOKOnly, Title:="Error"
    Resume ExitProc
    
End Sub

Updated version attached for you to work on further.
Good luck with your project

Alhakeem1977 · Apr 27, 2018

Thank you so much, that's exactly what I want, I do not know how to really thank you.

I really love this site.

isladogs · Apr 27, 2018

You're welcome.

Getting feedback like yours is one of the reasons why so many of us help out at this site.

Alhakeem1977 · Apr 27, 2018

Hi Dear,

I got an error '3075' stacks the application if the DeptSeq table is empty to start creating not exist calendar dates from the first date of the current year until the end of it.

I think I have to add to the code ( Is not Null ) but I do not know how to do it.

Code:

  N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & _
"# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")

Sorry for disturbing you with me.

Thanks in advance.

isladogs · Apr 27, 2018

Alhakeem1977 said:
Hi Dear,

I got an error '3075' stacks the application if the DeptSeq table is empty to start creating not exist calendar dates from the first date of the current year until the end of it.

I think I have to add to the code ( Is not Null ) but I do not know how to do it.

Code:

N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & _ "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")

Sorry for disturbing you with me.

Thanks in advance.

No there's an easier solution using error handling. Busy at the moment. Will send instructions later

isladogs · Apr 27, 2018

Hi
Just needed to check which procedure triggered the error and add 1 line of code to handle the error - shown in RED below
Error 3075 occurred because the table was empty - as we know that's the reason, we can tell Access to ignore it

Code:

Private Sub cmdUpdate_Click()
   
On Error GoTo Err_Handler
   
   'save changes
    If Me.Dirty Then Me.Dirty = False
   
    Dim N As Integer
    N = DCount("SenderID", "DeptSeq", "DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#")
    
    If MsgBox("You are about to execute a major operation ! Do you really want to continue ?" & vbCrLf, vbYesNo, "Major Operation") = vbNo Then
            MsgBox "Your operation execution is canceled !", vbOKOnly, "Operation Canceled"
            Exit Sub
    Else
        If N > 1 Then
            MsgBox N & " records in the selected date range will be deleted.", vbCritical, "Existing Data"
        ElseIf N = 1 Then
            MsgBox N & " record in the selected date range will be deleted.", vbCritical, "Existing Data"
        Else
            MsgBox "No records to delete"
        End If
       
       'this deletes all existing records - not just those where SenderID Is Not Null
        CurrentDb.Execute "DELETE DeptSeq.*" & _
            " FROM DeptSeq" & _
            " WHERE (DDate Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#)", dbFailOnError
            
        'this would only delete those where SenderID Is Not Null
        'however it can't be used as your DAOAdding procedure replaces all values
       '  CurrentDb.Execute "DELETE DeptSeq.*" & _
            " FROM DeptSeq" & _
            " WHERE (((DeptSeq.DDate) Between #" & Format(Me.txtDF, "mm/dd/yyyy") & "# AND #" & Format(Me.txtDT, "mm/dd/yyyy") & "#) AND ((DeptSeq.SenderID) Is Not Null))", dbFailOnError
            

        'add new records - NOT CHECKED or ALTERED
        'However an APPEND query would do the same jpb and MUCH faster
        DAOAdding
      '  On Error Resume Next
        MsgBox "Your operation execution has been completed successfully.", vbOKOnly, "Successful Operation"
    End If
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    [B][COLOR="Red"]If Err = 3075 Then Resume Next[/COLOR][/B]
    MsgBox "Error " & Err.Number & " in cmdUpdate_Click procedure: " & vbCrLf & Err.Description
    Resume Exit_Handler
    
End Sub

Updated version attached

Alhakeem1977 · Apr 28, 2018

Very good thank you so much.
Works perfectly without any errors.

If you don't mind, is there a simple way to get those existing records in a text box in the same form but the [ID] number of DeptSeq?
Please do it if you have time I am happy with db you provided to me but it will add a fantastic feature to the application.

Please accept my sincere regards.

isladogs · Apr 28, 2018

Not quite sure what you mean so I've guessed
New form frmDeptSeq with button added to frmHolidays
Hopefully that's similar to what you want

I expect you will want to adapt further yourself

If column is not empty (1 Viewer)

Registered User.

MVP / VIP

..forever waiting... waiting for jellybean!

MVP / VIP

..forever waiting... waiting for jellybean!

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Attachments

Registered User.

MVP / VIP

Registered User.

MVP / VIP

MVP / VIP

Attachments

Registered User.

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread