troubl using a null field in strwhere

[Sandy]

I need help with the following statement where I refer to the
CertificationLANID field. I need the strwhere string to recognize records
that match the Coach ID and where the [CertificationLANID] is Null.
The underlying table has the CertificationLANID field set to Not allow
zero-length strings.

Your help is much appreciated!

Private Sub cmdMetricVerification_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim IntCount As Integer
Dim Cert As Variant

'On Error GoTo Err_cmdMetricVerification_Click

Cert = Null

ID = [Forms]![frmAssocII]![COACH ID].Value 'equals the current associate
from Assoc Form II
strwhere = "[COACH ID]=" & Chr(34) & ID & Chr(34) & _
" AND [CertificationLANID]=" & Chr(34) & Cert & Chr(34)

'where the Coach ID in the table matches Coach ID from frmAssocII"


IntCount = DCount("*", "[tblB2BStatisticsNew]", strwhere) 'Counts number of
records where IDs match and where the certification is null
If IntCount <> 0 Then
MsgBox "This associate's statistics have not been certified." & Chr(13) &
"Please contact the CDT for Assistance"
Exit Sub

End If

 

[Dirk Goldgar]

I need help with the following statement where I refer to the
CertificationLANID field. I need the strwhere string to recognize
records that match the Coach ID and where the [CertificationLANID] is
Null.
The underlying table has the CertificationLANID field set to Not allow
zero-length strings.

Your help is much appreciated!

Private Sub cmdMetricVerification_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim IntCount As Integer
Dim Cert As Variant

'On Error GoTo Err_cmdMetricVerification_Click

Cert = Null

ID = [Forms]![frmAssocII]![COACH ID].Value 'equals the current
associate from Assoc Form II
strwhere = "[COACH ID]=" & Chr(34) & ID & Chr(34) & _
" AND [CertificationLANID]=" & Chr(34) & Cert & Chr(34)

'where the Coach ID in the table matches Coach ID from frmAssocII"


IntCount = DCount("*", "[tblB2BStatisticsNew]", strwhere) 'Counts
number of records where IDs match and where the certification is null
If IntCount <> 0 Then
MsgBox "This associate's statistics have not been certified." &
Chr(13) & "Please contact the CDT for Assistance"
Exit Sub

End If

If I understand you right, this is what you want:

strwhere = _
"[COACH ID]=" & Chr(34) & ID & Chr(34) & _
" AND [CertificationLANID] Is Null"

 

[Sandy]

ahhh.. too many """ I had. Thank you Dirk!

I need help with the following statement where I refer to the
CertificationLANID field. I need the strwhere string to recognize
records that match the Coach ID and where the [CertificationLANID] is
Null.
The underlying table has the CertificationLANID field set to Not allow
zero-length strings.

Your help is much appreciated!

Private Sub cmdMetricVerification_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim IntCount As Integer
Dim Cert As Variant

'On Error GoTo Err_cmdMetricVerification_Click

Cert = Null

ID = [Forms]![frmAssocII]![COACH ID].Value 'equals the current
associate from Assoc Form II
strwhere = "[COACH ID]=" & Chr(34) & ID & Chr(34) & _
" AND [CertificationLANID]=" & Chr(34) & Cert & Chr(34)

'where the Coach ID in the table matches Coach ID from frmAssocII"


IntCount = DCount("*", "[tblB2BStatisticsNew]", strwhere) 'Counts
number of records where IDs match and where the certification is null
If IntCount <> 0 Then
MsgBox "This associate's statistics have not been certified." &
Chr(13) & "Please contact the CDT for Assistance"
Exit Sub

End If

If I understand you right, this is what you want:

strwhere = _
"[COACH ID]=" & Chr(34) & ID & Chr(34) & _
" AND [CertificationLANID] Is Null"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)