VBA Help

  • Thread starter Thread starter Tony Schlak
  • Start date Start date
T

Tony Schlak

Here is a snippet of code:



Dim dtCheckDate As Date 'Changed to Public to allow use in report
Dim rsCheckDate ' As Recordset
Dim strSponsor As String
Dim strTitle As String

strSQL = "SELECT tblUIS_FY_EMP_EXP_Convert.CHECK_DATE,
tblUIS_FY_EMP_EXP_Convert.Sponsor_Acronym,
tblUIS_FY_EMP_EXP_Convert.Proposal_Title1 "
strSQL = strSQL & "FROM tblUIS_FY_EMP_EXP_Convert "
strSQL = strSQL & "WHERE
((tblUIS_FY_EMP_EXP_Convert.DEPT_FRS)='02001') AND
((tblUIS_FY_EMP_EXP_Convert.ACCOUNT)=" & strAccountNo & ") AND "
strSQL = strSQL &
"((tblUIS_FY_EMP_EXP_Convert.POSTING_DATE)=#" & strPostingDate & "#)"

'MsgBox strSQL
Set rsCheckDate = dbCurrent.OpenRecordset(strSQL)
dtCheckDate = CDate(rsCheckDate.Fields("CHECK_DATE"))

If rsCheckDate.EOF = False Then
strSponsor = rsCheckDate.Fields("Sponsor_Acronym")
Else
strSponsor = "N/A"
End If

If rsCheckDate.EOF = False Then
strTitle = rsCheckDate.Fields("Proposal_Title1")
Else
strTitle = "N/A"
End If



What I am trying to have happen is if there is null value in Sponsor_Acronym
to insert N/A and the same goes for the Proposal_Title1 field.



Thanks,

Tony
 
Replace the following code:

Set rsCheckDate = dbCurrent.OpenRecordset(strSQL)
dtCheckDate = CDate(rsCheckDate.Fields("CHECK_DATE"))

If rsCheckDate.EOF = False Then
strSponsor = rsCheckDate.Fields("Sponsor_Acronym")
Else
strSponsor = "N/A"
End If

If rsCheckDate.EOF = False Then
strTitle = rsCheckDate.Fields("Proposal_Title1")
Else
strTitle = "N/A"
End If

with

Set rsCheckDate = dbCurrent.OpenRecordset(strSQL)

If rsCheckDate.EOF = False Then
dtCheckDate = CDate(rsCheckDate.Fields("CHECK_DATE"))
strSponsor = Nz(rsCheckDate.Fields("Sponsor_Acronym"), "N/A")
strTitle = Nz(rsCheckDate.Fields("Proposal_Title1"), "N/A")
Else
dtCheckDate = 0
strSponsor = "N/A"
strTitle = "N/A"
End If
 
Tony said:
Here is a snippet of code:



Dim dtCheckDate As Date 'Changed to Public to allow use in report
Dim rsCheckDate ' As Recordset
Dim strSponsor As String
Dim strTitle As String

strSQL = "SELECT tblUIS_FY_EMP_EXP_Convert.CHECK_DATE,
tblUIS_FY_EMP_EXP_Convert.Sponsor_Acronym,
tblUIS_FY_EMP_EXP_Convert.Proposal_Title1 "
strSQL = strSQL & "FROM tblUIS_FY_EMP_EXP_Convert "
strSQL = strSQL & "WHERE
((tblUIS_FY_EMP_EXP_Convert.DEPT_FRS)='02001') AND
((tblUIS_FY_EMP_EXP_Convert.ACCOUNT)=" & strAccountNo & ") AND "
strSQL = strSQL &
"((tblUIS_FY_EMP_EXP_Convert.POSTING_DATE)=#" & strPostingDate & "#)"

'MsgBox strSQL
Set rsCheckDate = dbCurrent.OpenRecordset(strSQL)
dtCheckDate = CDate(rsCheckDate.Fields("CHECK_DATE"))

If rsCheckDate.EOF = False Then
strSponsor = rsCheckDate.Fields("Sponsor_Acronym")
Else
strSponsor = "N/A"
End If

If rsCheckDate.EOF = False Then
strTitle = rsCheckDate.Fields("Proposal_Title1")
Else
strTitle = "N/A"
End If



What I am trying to have happen is if there is null value in Sponsor_Acronym
to insert N/A and the same goes for the Proposal_Title1 field.
Click to expand...


Why all this messing around? Can't you just use:

Nz([Sponsor_Acronym], "N/A")

either in the query or in the text box where it's displayed?
 
Back
Top