Using Is Null in Update query

[moosecck]

I have a table tblCreditApplicationInput which holds all our customer
data for those who are applying for credit from our company. I
created a report called rpt_Bank_Ref_Letter which we send to banks in
order to check our customer references. When we process data for the
letters to be sent, we run a make table query to create the
tblMailMerge_Bank_References data which will then be used to feed to
the report we send to the banks. On the report we have 3 lines called
1st Request, 2nd Request and Final Request. These 3 fields are also
columns in our tables. When we run the print report process, I need to
have the report enter the date the report is run into the 1st Request
column of the table. If I run the report again next week for the same
customer, I need to have the query look at the 1st Request column in
the table, and if there is a date there, have the date the report is
run again go into the 2nd Request column. Then if I run the report for
the same customer the following week, I need the query to look at the
table and check for dates in the 1st Request and 2nd Request columns,
and if there are dates there enter the date the report is being run
into the Final Request column. Code below:

Query Code for First_Request
UPDATE tblMailMerge_Bank_References SET
tblMailMerge_Bank_References.Bank_Ref_First_Request = Date()
WHERE (((tblMailMerge_Bank_References.Bank_Ref_First_Request) Is
Null));

Query Code for Second_Request
UPDATE tblMailMerge_Bank_References SET
tblMailMerge_Bank_References.Bank_Ref_Second_Request = Date()
WHERE (((tblMailMerge_Bank_References.Bank_Ref_Second_Request) Is
Null) AND ((tblMailMerge_Bank_References.Bank_Ref_First_Request) Is
Not Null));

Query Code for Final_Request
UPDATE tblMailMerge_Bank_References SET
tblMailMerge_Bank_References.Bank_Ref_Final_Request = Date()
WHERE (((tblMailMerge_Bank_References.Bank_Ref_Final_Request) Is Null)
AND ((tblMailMerge_Bank_References.Bank_Ref_Second_Request) Is Not
Null) AND ((tblMailMerge_Bank_References.Bank_Ref_First_Request) Is
Not Null));

I have tried other methods as well with no luck. Can anyone tell me
what I am doing wrong?

Thanks in advance for all your help

Tom

 

[Ken Snell \(MVP\)]

Which object and which event for that object are you using to try to run
these update queries?

 

[moosecck]

Which object and which event for that object are you using to try to run
these update queries?

--

        Ken Snell
<MS ACCESS MVP>












- Show quoted text -

I came up with an answer to this using VBA in a module.
i created a recordset out of the entries that were pulled from
tblCreditApplicationInput into
tblMailMerge_Bank_References and used IF/THEN statements to loop thru
the records looking
for First_Request, Second_Request and Final_Request and checking for
NULL or "".

Here is the code:

Sub BankRefDate()

Dim RS1 As Recordset
Dim qrystr1, qrystr2, qrystr3, qrystr4 As String

qrystr1 = " SELECT AutoNum, Bank_Ref_First_Request ,
Bank_Ref_Second_Request, Bank_Ref_Final_Request " _
& " FROM tblMailMerge_Bank_References "

Set RS1 = CurrentDb.OpenRecordset(qrystr1)
RS1.MoveFirst

Do While Not RS1.EOF

If RS1![Bank_Ref_First_Request] = "" Or IsNull(RS1!
[Bank_Ref_First_Request]) = True Then

qrystr2 = " UPDATE tblMailMerge_Bank_References " _
& " SET Bank_Ref_First_Request = """ & Date & """ "
_
& " WHERE AutoNum = " & RS1![Autonum]
MsgBox qrystr2
DoCmd.RunSQL (qrystr2)

Else
If RS1![Bank_Ref_Second_Request] = "" Or IsNull(RS1!
[Bank_Ref_Second_Request]) = True Then

qrystr3 = " UPDATE tblMailMerge_Bank_References "
_
& " SET Bank_Ref_Second_Request = """ &
Date & """ " _
& " WHERE AutoNum = " & RS1![Autonum]
MsgBox qrystr3
DoCmd.RunSQL (qrystr3)

Else
If RS1![Bank_Ref_Final_Request] = ""
Or IsNull(RS1![Bank_Ref_Final_Request]) = True Then

qrystr4 = " UPDATE
tblMailMerge_Bank_References " _
& " SET
Bank_Ref_Final_Request = """ & Date & """ " _
& " WHERE AutoNum = " & RS1!
[Autonum]
MsgBox qrystr4
DoCmd.RunSQL (qrystr4)

End If
End If
End If

RS1.MoveNext

Loop
MsgBox "Done"
End Sub

It works great!!

 

[Ken Snell \(MVP\)]

Glad you found your solution. May I point out a common VBA programming error
in your code, though?

This line:
Dim qrystr1, qrystr2, qrystr3, qrystr4 As String

DOES NOT "dim" all four variables as String data types. Only qrystr4 is
being "dim'd" as String; the other three are being "dim'd" as Variant. If
you want all four to be String, the correct code step is this:

Dim qrystr1 As String, qrystr2 As String, qrystr3 As String, qrystr4 As
String

--

Ken Snell
<MS ACCESS MVP>

- Show quoted text -

I came up with an answer to this using VBA in a module.
i created a recordset out of the entries that were pulled from
tblCreditApplicationInput into
tblMailMerge_Bank_References and used IF/THEN statements to loop thru
the records looking
for First_Request, Second_Request and Final_Request and checking for
NULL or "".

Here is the code:

Sub BankRefDate()

Dim RS1 As Recordset
Dim qrystr1, qrystr2, qrystr3, qrystr4 As String

qrystr1 = " SELECT AutoNum, Bank_Ref_First_Request ,
Bank_Ref_Second_Request, Bank_Ref_Final_Request " _
& " FROM tblMailMerge_Bank_References "

Set RS1 = CurrentDb.OpenRecordset(qrystr1)
RS1.MoveFirst

Do While Not RS1.EOF

If RS1![Bank_Ref_First_Request] = "" Or IsNull(RS1!
[Bank_Ref_First_Request]) = True Then

qrystr2 = " UPDATE tblMailMerge_Bank_References " _
& " SET Bank_Ref_First_Request = """ & Date & """ "
_
& " WHERE AutoNum = " & RS1![Autonum]
MsgBox qrystr2
DoCmd.RunSQL (qrystr2)

Else
If RS1![Bank_Ref_Second_Request] = "" Or IsNull(RS1!
[Bank_Ref_Second_Request]) = True Then

qrystr3 = " UPDATE tblMailMerge_Bank_References "
_
& " SET Bank_Ref_Second_Request = """ &
Date & """ " _
& " WHERE AutoNum = " & RS1![Autonum]
MsgBox qrystr3
DoCmd.RunSQL (qrystr3)

Else
If RS1![Bank_Ref_Final_Request] = ""
Or IsNull(RS1![Bank_Ref_Final_Request]) = True Then

qrystr4 = " UPDATE
tblMailMerge_Bank_References " _
& " SET
Bank_Ref_Final_Request = """ & Date & """ " _
& " WHERE AutoNum = " & RS1!
[Autonum]
MsgBox qrystr4
DoCmd.RunSQL (qrystr4)

End If
End If
End If

RS1.MoveNext

Loop
MsgBox "Done"
End Sub

It works great!!