[AccessD] Re: Quick Syntax question

Additional to my last:

Heres the solution, modify to suit your fields, this needs a reference
to DAO.

Dim sqltext, specific As Integer
Dim db As dao.Database, rs As dao.Recordset
Set db = CurrentDb()

sqltext = "SELECT count([id]) as countMe FROM Table1;"
Set rs = db.OpenRecordset(sqltext)
specific = rs.Fields("countMe")
rs.Close
Set db = Nothing


Andrew Curtis

-----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: Monday, 5 July 2004 8:51 AM
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



CONFIDENTIAL COMMUNICATION
If you receive this confidential communication by mistake, please contact the sender immediately by return electronic mail.
Worsley Alumina Pty Ltd ABN 58 008 905 155 is the manager of
the Worsley Joint Venture - Bauxite/Alumina Operation. Liability and responsibility of the Joint Venturers is several in accordance with the following schedule of participating interests: Billiton Aluminium (RAA) Pty Ltd 56 percent, Billiton Aluminium (Worsley) Pty Ltd 30 percent, Japan Alumina Associates (Australia) Pty Ltd 10 percent, Sojitz Alumina Pty Ltd 4 percent.