[AccessD] Re: Quick Syntax question

Hi Connie:

IF ISNUMBER(RenewID) THEN RenewID = TRIM(STR(RenewID))

Specific = "COUNT(TACS.SpecificCondition)
FROM  tblDAuthorityConditionSpecific AS TACS
WHERE NOT ISNULL(TACS.SpecificCondition)
  AND TACS.AuthorityID = " & RenewID & ";"

I am not sure this would help but it will catch a couple of the obvious
issues that have the potential to kill the above code.

If the above line is really suppose to be a complete sequel statement then
the following line would be more correct:

Specific = "SELECT COUNT(TACS.SpecificCondition)
FROM  tblDAuthorityConditionSpecific AS TACS
WHERE NOT ISNULL(TACS.SpecificCondition)
  AND TACS.AuthorityID = " & RenewID & ";"

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
connie.kamrowski at agric.nsw.gov.au
Sent: Sunday, July 04, 2004 5:51 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Re: Quick Syntax question



Ok guys here goes,

I took over this database and am trying to finish teh work of a contractor
who pulled out half way through, so I take no responsibility for structure.
The database records animal details and I need to create an If statement on
a sub report based on whether a specific condition is present ie. If
Authority renewal ID is associated with a specific
condition then show me the sub report, if no condition hide the sub report.
The table which stores the specific condition contains 3 fields.
AuthorityrenewalID(integer), SpecificConditionID (Autonumber) and specific
condition(memo field). the problem being there can be more than one row
associated with any one authorityrenewal and thus giving me multiple copies
of the report based on a new subreport for each instance of that number in
the table. So I need to count the rows associated with any given authority,
store that as an integer variable and run my IF statement on the value of
the variable.

The syntax I provided before is being coded behind the main report in
Access not in true SQL , it compiles Ok and I get no error messages on save
but everytime I run the report I get a type mismatch error, hitting Debug I
go straight to my Count. The types on the variables and the table fields
match but the error keeps popping up. I get the msgbox for my RenewID and
the value is correct but as soon as it hits the COUNT line crash

All suggestions greatly appreciated.

Code <------

Private Sub Report_Activate()
Dim Specific As Integer
Dim RenewID As Long

RenewID = Me.AuthorityRenewalID.Value
MsgBox RenewID
RenewID = Me.AuthorityRenewalID.Value

Specific = "COUNT([SpecificCondition]) FROM
[tblDAuthorityConditionSpecific] WHERE
[tblDAuthorityConditionSpecific].[AuthorityID] = " & RenewID & " ;"

If Specific >= 1 Then
Me.subreport4.Visible = True
Else
Me.subreport4.Visible = False
End If
MsgBox Specific

End Sub

------>

Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Department of Primary Industries
Orange

Ph: 02 6391 3250
Fax:02 6391 3290

This message is intended for the addressee named and may contain
confidential information. If you are not the intended recipient or received
it in error, please delete the message and notify sender. Views expressed
are those of the individual sender and are not necessarily the views of
their organisation.


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com