IsValid Flag Using DCount Not Working

[Daryl]

I've been trying to show an IsValid flag on the main form using the following code (and several variations)and can't seem to get it to work. Any ideas what I'm doing wrong or leaving out?

Dim strValidFormCount
strValidFormCount = "SELECT tbl_FormValidity.Form_nm, tbl_FormValidity.Form_vdt, " & _
"FROM tbl_FormValidity" & _
"WHERE (((tbl_FormValidity.Form_nm) = Me.Form_nm) And ((tbl_FormValidity.Form_vdt) = Me.Form_vdt) " & _
"And ((tbl_FormValidity.EffDate) Is Not Null) And ((tbl_FormValidity.ExpDate) Is Null))" & _
"GROUP BY tbl_FormValidity.Form_nm, tbl_FormValidity.Form_vdt;"
Dim intFormCount As Integer
intFormCount = DCount("[Form_nm] & [Form_vdt]", "tbl_FormValidity")
If intFormCount > 0 Then
Me.IsValid.Value = -1
Else
Me.IsValid.Value = 0
End If

 

[Pat Hartman]

The query string is incorrect and I can't see what you are doing with it in any event. When you build SQL that uses form fields or variables on the fly, you need to concatinate them properly so that VBA can parse them before passing the SQL string to Jet.

"WHERE [Form_nm] = " & Me.Form_nm & " And [Form_vdt] = " & Me.Form_vdt & " And [EffDate] Is Not Null And [ExpDate] Is Null " & _

If Form_nm or Form_vdt are text fields, they need to be surrounded by single quotes as follows:

"WHERE [Form_nm] = '" & Me.Form_nm & "' And [Form_vdt] = '" & Me.Form_vdt & "' And [EffDate] Is Not Null And [ExpDate] Is Null " & _

I removed the parentheses because they were unnecessary and I removed the table name qualification because it was also unnecessary.

 

[Daryl]

The database I'm working on relates to insurance forms. Every year or so a form (Form_nm) is revised and given a new version date (Form_vdt). Insurance regulators in various jurisdictions approve or disapprove the use of the specific form (Form_nm & Form_vdt). The EffDate field identifies when the form has been approved for use in the jurisdiction. The ExpDate identifies the last day the speicific form is valid.

Before a decision to print copies of FormA version1 (which has been replaced in some jurisdictions by FormA version2) can be made, I need to identify if it is still valid (why print a form that's not being used any more). The following query gives me a count of how many jurisdictions are still using the specific form.

SELECT tbl_FormValidity.Form_nm, tbl_FormValidity.Form_vdt, Count(([Form_nm] And [Form_vdt])) AS NumberValid
FROM tbl_FormValidity
WHERE (((tbl_FormValidity.Form_nm)=[Forms]![frm_GAIFormsInformation]![Form_nm]) AND ((tbl_FormValidity.Form_vdt)=[Forms]![frm_GAIFormsInformation]![Form_vdt]) AND ((tbl_FormValidity.EffDate) Is Not Null) AND ((tbl_FormValidity.ExpDate) Is Null))
GROUP BY tbl_FormValidity.Form_nm, tbl_FormValidity.Form_vdt;

If the NumberValid field is > 0 then it's a valid form else it's not a valid form, but I can't seem to show that in a control on my form. I've received the following errors no matter how I try setting up the code.

Can't find the field 'NumberValid' referred in your expression.
Variable not defined.

Hope this helps explain the situation a little better and you can help me move on.