pdvsa
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_BeforeUpd ate(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
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_BeforeUpd
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
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?
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
If Len("" & Me.txtDateOfIssueSB) = 0 then
If isnull(Me.txtDateOfIssueSB) Or Me.txtDateOfIssueSB = "" then
If Not IsDate(Me.txtDateOfIssueSB) then
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.
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
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.
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
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...
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...
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.
On this IF:
If IsDate(Me.txtDateOfIssueSB
==>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.
ASKER
It does not have something to do with a new record. I know this for sure.
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
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
ASKER
"if I change the FALSE to TRUE in this statement:"
If IsDate(Me.txtDateOfIssueSB ) = False Then
If IsDate(Me.txtDateOfIssueSB
<<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?
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
<< 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?
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
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.
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.
ASKER
hang on...did not see the others 2 posts...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...;)
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?
Ha ha! Wise guy, eh?
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.
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.
>> 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.