Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

Null and Date and StrSQL

Experts,

I seem to not know how to only activate the INSERT code if the txtDateOfIssueSB is NULL.
If I remove the If statement then the INSERT does insert but if I use the IF then it does not.

thank you

Private Sub txtDateOfIssueSB_BeforeUpdate(Cancel As Integer)

        Dim strSQL As String

If Me.txtDateOfIssueSB = "" Or Me.txtDateOfIssueSB = 0 Then
           
           strSQL = "INSERT INTO tblLCAmendHistory (AmendedDate, letterOfCreditID, EndUserID, LCNo, AmendType) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & "'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & 14 & ")"
           
  'Debug.Print strSQL
 
  CurrentDb.Execute strSQL, dbFailOnError
   
   
MsgBox "blah blah", vbInformation
 
 End If
Avatar of mbizup
mbizup
Flag of Kazakhstan image

If Me.txtDateOfIssueSB = "" Or Me.txtDateOfIssueSB = 0 OR isNull(Me.txtDateOfIssueSB )
You can also do this:

If "" & Me.txtDateOfIssueSB = "" OR Me.txtDateOfIssueSB = 0  then


(But do you really need to check for 0, or do you have masked data entry for this field?
More variations/possibilities, just for the fun of it:
If "" & Me.txtDateOfIssueSB = "" Then

Open in new window

If Len("" & Me.txtDateOfIssueSB) = 0  then

Open in new window

If isnull(Me.txtDateOfIssueSB) Or Me.txtDateOfIssueSB = "" then

Open in new window

If Not IsDate(Me.txtDateOfIssueSB) then

Open in new window

Avatar of pdvsa

ASKER

Hi Mbizup,

darnit that was not it.  
If I remove the IF then it does INSERT.

I have other code in this BEFOREUPDATE event and I did not post it thinking that it does not affect what I am trying to do.  Maybe if after seeing it you can see an issue but note that it does INSERT if I do remove the IF statement related to txtDateofIssueSB.


Private Sub txtDateOfIssueSB_BeforeUpdate(Cancel As Integer)

        Dim strSQL As String  'this is only for the txtDateofIssueSB part

    If IsNull(Me.txtFinalMaturity) Or Me.FinalMaturity = "" Then
        
        MsgBox "blah blahhhh", vbInformation

    
    End If
    
If IsNull(Me.txtLCNo) Or Me.txtLCNo = "" Then
        MsgBox "blah  blahhh"
        Cancel = True

   End If
   
   'If Me.txtDateOfIssueSB = "" Or Me.txtDateOfIssueSB = 0 Or IsNull(Me.txtDateOfIssueSB) Then
    If "" & Me.txtDateOfIssueSB = "" Or Me.txtDateOfIssueSB = 0 Then
           strSQL = "INSERT INTO tblLCAmendHistory (AmendedDate, letterOfCreditID, EndUserID, LCNo, AmendType) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & "'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & 14 & ")"
           
  
  CurrentDb.Execute strSQL, dbFailOnError
    
    
MsgBox "blah blah", vbInformation
  End If

Open in new window

Avatar of pdvsa

ASKER

ok I see the other post variations....let me check it out....hang on a sec.... thanks for that
Give this a try...

IsDate = false should catch nulls (and anything else that is not a valid date).   Any of the other statements should work - unless Me.txtLCNo is also null, in which case your update will be cancelled by the previous If-then block.


Private Sub txtDateOfIssueSB_BeforeUpdate(Cancel As Integer)

        Dim strSQL As String  'this is only for the txtDateofIssueSB part

If IsNull(Me.txtFinalMaturity) Or Me.FinalMaturity = "" Then      
        MsgBox "blah blahhhh", vbInformation
End If
    
If IsNull(Me.txtLCNo) Or Me.txtLCNo = "" Then
        MsgBox "blah  blahhh"
        Cancel = True
End If
   
If  IsDate(Me.txtDateOfIssueSB) = False  Then
           strSQL = "INSERT INTO tblLCAmendHistory (AmendedDate, letterOfCreditID, EndUserID, LCNo, AmendType) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & "'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & 14 & ")"
           CurrentDb.Execute strSQL, dbFailOnError
           MsgBox "blah blah", vbInformation
End If 

Open in new window

Avatar of pdvsa

ASKER

darnit, none of them worked.  It does INSERT if I take out that IF statement though.  

For example, what I am doing is entering a date in the txtDateOfIssueSB and Before I do this the field is NULL.  I would think those statements should work if the field was NULL.    

Maybe there is something else you see if after seeing all of it posted above?

thank you...
Avatar of pdvsa

ASKER

OK I have some news

On this IF:
If  IsDate(Me.txtDateOfIssueSB) = False  Then

==>still same thing after I enter BUT if I tab out then go back and delete that date I just entered then it does work.  Seems strange...I dont know if it has something to do with a Me.NewRecord.

Avatar of pdvsa

ASKER

It does not have something to do with a new record.  I know this for sure.  

Avatar of pdvsa

ASKER

little bit of more info:  
if I change the False to TRUE then it does INSERT if entereing a date and tabbing out then then it also INSERTS when I simply change a date from say 12/30/11 to 12/31/11 and I do not want the INSERT if simply changing the date...only want to fire the INSERT if txtDateOfIsseSB is NULL

Avatar of pdvsa

ASKER

"if I change the FALSE to TRUE in this statement:"
If  IsDate(Me.txtDateOfIssueSB) = False  Then
<<BUT if I tab out then go back and delete that date I just entered then it does work>>>

You just entered a date, so the field is no longer null at that point.  The code is set up to ONLY run the insert if the field is null (so it is correctly not firing)

When you delete the date, the field is then null, so the insert works.

Do you want to do the opposite? ie, run the insert if the field is NOT null?


Private Sub txtDateOfIssueSB_BeforeUpdate(Cancel As Integer)

        Dim strSQL As String  'this is only for the txtDateofIssueSB part

If IsNull(Me.txtFinalMaturity) Or Me.FinalMaturity = "" Then      
        MsgBox "blah blahhhh", vbInformation
End If
    
If IsNull(Me.txtLCNo) Or Me.txtLCNo = "" Then
        MsgBox "blah  blahhh"
        Cancel = True
End If
   
If  "" & Me.txtDateOfIssueSB <> ""  Then
           strSQL = "INSERT INTO tblLCAmendHistory (AmendedDate, letterOfCreditID, EndUserID, LCNo, AmendType) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & "'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & 14 & ")"
           CurrentDb.Execute strSQL, dbFailOnError
           MsgBox "blah blah", vbInformation
End If 

Open in new window

<<  I do not want the INSERT if simply changing the date...only want to fire the INSERT if txtDateOfIsseSB is NULL >>

Hmmm...

Do you mean insert if txtDateOfIsseSB IS NULL or insert if txtDateOfIsseSB WAS NULL?

ie: do you want to insert if txtDateOfIsseSB was previously null, but is being changed by the user?

If my last block of code did not work, try this (according to the understanding in my previous comment):


If  "" & Me.txtDateOfIssueSB.OldValue = ""  Then
           strSQL = "INSERT INTO tblLCAmendHistory (AmendedDate, letterOfCreditID, EndUserID, LCNo, AmendType) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & "'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & 14 & ")"
           CurrentDb.Execute strSQL, dbFailOnError
           MsgBox "blah blah", vbInformation
End If 

Open in new window

Avatar of pdvsa

ASKER

ok the above works now but I have to tweak it to NOT fire the INSERT if the txtDateOfIssueSB is NOT NULL (meaning that I am simply changing a date from 12/30 to 12/3) then do not fire the INSERT.   It fires teh INSERT on NULL and if NOT NULL.  

right now the INSERT does fire if the txtDateOfIssueSB was NULL but if I change the date in txtDateOfIssueSB then the INSERT event still fires.  

How can I modify to only fire on If NULL?  Hope that makes sense.  
Avatar of pdvsa

ASKER

hang on...did not see the others 2 posts...
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa

ASKER

ahhhh i see now it was the .oldvalue  trick.  

that was tricky.   \


I have tested it and it works perfectly now.  
I dont completely understand why you need to use oldvalue. I would think that if using BeforeUpdate you ARE looking at the oldvalue.  Anyways, that is what experts exchange if for...to figure out these things that normal peeps like me would lose minutes on life.

this is what I changed:
If  "" & Me.txtDateOfIssueSB <> ""  Then
to:
If  "" & Me.txtDateOfIssueSB.OldValue = ""  Then


thank you mbizup!  you da man...;)
>> you da man...;)


Ha ha!  Wise guy, eh?
Avatar of pdvsa

ASKER

<There is so often some off-the-wall angle to them!
I had wondered about that.  I think that I cant explain what it is I want to do sometimes as is the case here in this question.   It was a tricky one for me.  
You explained things really well here.  It just tooks some back-and-forth  :)

>> I dont completely understand why you need to use oldvalue.

If you really want to see how this works (this is pretty neat), take a look at what happens in your underlying table as you enter data normally on your form.  Just open up the table and look at the stored data as you enter new data in a textbox, then click on a different control, and then move to a different record.


mbizup - Attached is one genuine "Atta-Girl"!

User generated image