DataBase - Totals not working

In tblRecipes I have a Field as follows:
TotalCount: Nz(DCount("*","tblRecipeCategories","RecipeID = " &
[RecipeID]))

This works fined until I "Filter" the records with the Procedure
below, when TotalCount gives me "#Error".
How do I make TotalCount work with the following Procedure?
Thanks, Bernie

Private Sub cboShowCategory_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCTROW tblRecipes.* FROM tblRecipes " & _
& ";"     'this code works if the value is a number (like CategoryID)
Me.RecordSource = strSQL
End Sub

  Daryl S replied to iamnu

Iamnu -

The Nz function is missing all of the parameters.  Try this:

TotalCount: Nz(DCount("*","tblRecipeCategories","RecipeID = " &
[RecipeID]),0)

--
Daryl S

categories DataBase Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 1 Pens$50.00 5 1 Pencils$30.00 6 1 Rulers 155.00 Is it possible to do so? How? Thank you! Access Queries Discussions Expenses.CategoryID (1) Expenses.ExpenseID (1) Office (1) Categories.CategoryId (1) CategoryID (1) ExpenseID (1) Report (1) ShowCategory (1) A query returns the same number of columns BLANK / Null for a calculated column for category name SELECT IIF([ExpenseID] = DMin("ExpenseID", "Expenses", "CategoryID = " & Expenses.[CategoryID], [CategoryName], Null) as ShowCategory , ExpenseID, Expenses.CategoryID, ExpenseName, AmountSpent FROM Expenses INNER JOIN Categories ON Expenses.CategoryID = Categories.CategoryId ORDER BY Expenses

do is if the mentioned check box is active (true) collect only equipment with an CategoryId of 7 else I want all of the equipment EXCEPT 7. I have entered <> 7 to pass the correct statement to the query? Thanks, Jason Access Queries Discussions PreviewReports (1) CategoryId (1) Statemtn (1) Quesiton (1) Friday (1) IMO (1) iif statements can only return values lines for the OR part) example Select * From yourTable WHERE ([forms]![PreviewReports].[chkpumpstations] = 0 AND CategoryID <> 7) OR ([forms]![PreviewReports].[chkpumpstations]<> 0 AND [CategoryID] = 7); You could try an expression like the following: IIF([forms].[PreviewReports].[chkpumpstations] = 0, CategoryID = 7, CategoryID <> 7) In the query design view you would have that expression in a field "cell as follows Field: [forms].[PreviewReports].[chkpumpstations] Criteria (line 1): TRUE Criteria (line 2) : False Field: CategoryID Criteria (line 1): = 7 Criteria (line 2) : <> 7 John Spencer Access MVP 2002-2005, 2007 Baltimore County You would need to include the entire expression: IIf([forms].[PreviewReports].[chkpumpstations] = 0, CategoryId But, IMO, that is kind of clumsy. I thing I would use: WHERE (Forms.PreviewReports

Projektoren(vulgo: Beamer)bereich vergleichen m?chte. Dazu habe ich folgende Tabellenstruktur aufgebaut: tblCategory - -- -- -- -- -- CategoryName CategoryID (PK) tblUnsereProjektoren - -- -- -- -- -- -- -- -- -- ProjectorID (PK) ProductCode ProductDescription fkCategoryID tblCompetitors - -- -- -- -- -- -- - CompetitorID (PK) CompetitorName tblCompetitorAndProducts - -- -- -- -- -- -- -- -- -- -- -- - CompProductID (PK) CompProductDescr Discussions INNER JOIN (1) LEFT JOIN (1) ListBox (1) Inner (1) LEFT (1) DB (1) CategoryID (1) Peter Doering (1) Hallo! Volker Neurath schrieb: [. . .] Verwendest du eventuell in der Datenquelle des tblEp2Comp.IndirectPrice, tblEp2Comp.FinalPrice, tblEp2Comp.ErfDatum, tblCompProducts.fkVendorID, tblCompProducts.Specification, tblEP_Projectors.ProductCode, tblEP_Projectors.Description, tblEP_Projectors.CategoryID FROM tblVendors LEFT JOIN ((tblCategories LEFT JOIN tblEP_Projectors ON tblCategories.category = tblEP_Projectors.CategoryID) LEFT JOIN (tblCompProducts LEFT JOIN tblEp2Comp ON tblCompProducts.CompproductID = tblEp2Comp.fkCompetitorID) ON tblEP_Projectors.Ep_ID = tblEp2Comp tblours2Compet.IndirectPrice, tblours2Compet.FinalPrice, tblours2Compet.ErfDatum, tblCompProducts.fkVendorID, tblCompProducts.Specification, tblUnsereProjektoren.ProductCode, tblUnsereProjektoren.Description, tblUnsereProjektoren.CategoryID FROM tblVendors LEFT JOIN ((tblCategories LEFT JOIN tblUnsereProjektoren ON tblCategories.category = tblUnsereProjektoren.CategoryID) LEFT JOIN (tblCompProducts LEFT JOIN tblours2Compet ON tblCompProducts.CompproductID = tblours2Compet.fkCompetitorID) ON tblUnsereProjektoren.Ep_ID = tblours2Compet tblours2Compet.IndirectPrice, tblours2Compet.FinalPrice, tblours2Compet.ErfDatum, tblCompProducts.fkVendorID, tblCompProducts.Specification, tblUnsereProjektoren.ProductCode, tblUnsereProjektoren.Description, tblUnsereProjektoren.CategoryID FROM tblVendors RIGHT JOIN ((tblCategories RIGHT JOIN tblUnsereProjektoren ON tblCategories.Category = tblUnsereProjektoren.CategoryID) RIGHT JOIN (tblCompProducts RIGHT JOIN tblours2Compet ON tblCompProducts.CompProductID = tblours2Compet.fkCompetitorID) ON tblUnsereProjektoren.Ep_ID = tblours2Compet

LastName FirstName HomeroomTeacher (ohter info relating to the student) Cases CaseNo_ID <PK> StudentID <FK> Categories CategoryID <PK> Category (e.g. School, Community, Individual, Peers, Family) Issues IssuesID <PK> Issues (text, e.g. lack of role models) CategoryID <FK> StudentIssues CaseNoID IssuesID Actions ActionsID <PK> Actions (text, eg. New Adult Role Models CategoryID <FK> StudentActions CaseNoID ActionsID Does this look right to anyone? Is it possible to merge TblStudentHomeroom StudentHomeroomID StudentID HomeroomTeacherID Regarding issues and actions . . . . . . . . First, your category table looks good: TblCategory CategoryID <PK> Category (e.g. School, Community, Individual, Peers, Family) Your Issue table looks good: TblIssue IssueID <PK> CategoryID <FK> Issue (text, e.g. lack of role models) Your action table looks good: TblAction ActionsID <PK> CategoryID <FK> Action (text, eg. New Adult Role Models) Now you need a table that identifies

folgt: tblCategories - -- -- -- -- -- -- ID (PK) Category (Bezeichnung, z.B. Mobil, Installation, Jome cinema) tblOurProjectors - -- -- -- -- -- -- -- OurProjectorID (PK) CategoryID (FK) ProductCode ProductDescription tblVendors - -- -- -- -- -- VendorID (PK) VendorName tblVendorsAndProducts - -- -- -- -- -- -- -- -- -- -- ProductID (PK) fkVendorID (FK) ProductCode ProductDescription tblOurs2Others vom Himmel Zitronen regnet - - lerne, Limonade zu machen. Ich habe jetzt die folgende Tabellenkonstellation: tblCategories - -- -- -- -- -- -- CategoryID Category (Projektor-Kategorie, also z.B. Mobil, Installation, Home Cinema) tblVendors ( = = Hersteller) - -- -- -- -- - VendorID VendorName tblVendorsAndProducts - -- -- -- -- -- -- -- -- -- -- ProductID fkVendorID CategoryID ProductCode ProductDescription tblOurs2Competitors - -- -- -- -- -- -- -- -- -- - O2CID (PK, Autowert) fkOursID (unsere VendorID aus tblVendorsandProducts) fkCompetitorID ProjectNumber DirectPrice (distri lerne, Limonade zu machen. Nachtrag: Folgende Verkn?pfungen habe ich Access im Beziehungsfenster mitgeteilt: tblVendorsAndProducts - -- -- -- -- -- -- -- -- -- -- CategoryID (n)- -- -- -- -> (1) CategoryID tblCategories fkVendorID (n)- -- -- -- -> (1) VendorID tblVendors ausserdem tblOurs2Competitors - -- -- -- -- -- -- -- -- -- fkCompetitorID (n)- -- --> (1) ProductID tblVendorsAndProducts fkOursID (n Hallo Volker! Leider bist Du beratungsresistent dafür, daß Du eine verkäuferunabhängige Produktbeschreibung anlegen Produkt - -- -- -- - ProductID CategoryID ProductCode ProductDescription und das Produkt anschließend mit dem Verkäufer (für Fremdverkäufer * *und* * Deine Firma) verknüpfen