Comrad_Durandal Comrad_Durandal - 3 years ago 128
SQL Question

DCount vs. SQL SELECT COUNT(*)?

I am trying to get a count of all items sent to a supplier based on the purchase order they are assigned to. But I can't seem to get the control to show a number of items based on the purchase order instance - it keeps throwing either a #name? or #error! message in the text box when the form is activated.

Create statement for tblPODetail

USE [instkeeper_test]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblPODetail](
[intPurchaseOrderInstance] [int] IDENTITY(1,1) NOT NULL,
[intPONumber] [int] NOT NULL,
[datDateEntered] [datetime] NOT NULL,
[bolPOOpen] [bit] NOT NULL,
[intRMANumber] [nvarchar](255) NULL,
[strSupplierCode] [nvarchar](50) NOT NULL,
[strSupplierLastAddress] [nvarchar](50) NULL,
[strSupplierLastCity] [nvarchar](50) NULL,
[strSupplierLastState] [nvarchar](50) NULL,
[strSupplierLastPostalCode] [nvarchar](50) NULL,
[strSupplierLastCountry] [nvarchar](50) NULL,
[strSupplierLastPhone] [nvarchar](50) NULL,
[strSupplierLastFax] [nvarchar](50) NULL,
[datDateSent] [datetime] NULL,
[datDateReceived] [datetime] NULL,
[bolAdvGageTrakNoTouch] [bit] NOT NULL,
[bolAdvHardCopies] [bit] NOT NULL,
[bolFreightShip] [bit] NOT NULL,
[bolReturnableUsed] [bit] NOT NULL,
[bolInHouse] [bit] NOT NULL,
[strUPSTrackNumOut] [nvarchar](255) NULL,
[strFedExTrackNumOut] [nvarchar](255) NULL,
[strFreightTrackNumOut] [nvarchar](255) NULL,
[strUPSTrackNumIn] [nvarchar](255) NULL,
[strFedExTrackNumIn] [nvarchar](255) NULL,
[strFreightTrackNumIn] [nvarchar](255) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblPODetail] WITH CHECK ADD CONSTRAINT [SSMA_CC$tblPODetail$strSupplierCode$disallow_zero_length] CHECK ((len([strSupplierCode])>(0)))
GO

ALTER TABLE [dbo].[tblPODetail] CHECK CONSTRAINT [SSMA_CC$tblPODetail$strSupplierCode$disallow_zero_length]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__intPO__6C190EBB] DEFAULT ((0)) FOR [intPONumber]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__datDa__6D0D32F4] DEFAULT (getdate()) FOR [datDateEntered]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__bolPO__6E01572D] DEFAULT ((1)) FOR [bolPOOpen]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__bolAd__6EF57B66] DEFAULT ((1)) FOR [bolAdvGageTrakNoTouch]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__bolAd__6FE99F9F] DEFAULT ((1)) FOR [bolAdvHardCopies]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__bolFr__70DDC3D8] DEFAULT ((0)) FOR [bolFreightShip]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__bolRe__71D1E811] DEFAULT ((0)) FOR [bolReturnableUsed]
GO

ALTER TABLE [dbo].[tblPODetail] ADD CONSTRAINT [DF__tblPODeta__bolIn__72C60C4A] DEFAULT ((0)) FOR [bolInHouse]
GO


Create statement for tblPOGaugeDetail

USE [instkeeper_test]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblPOGaugeDetail](
[intItemPOID] [int] IDENTITY(1,1) NOT NULL,
[intGagePOID] [int] NOT NULL,
[strGageDetailID] [nvarchar](50) NULL,
[datGageSent] [datetime] NULL,
[datGageReceived] [datetime] NULL,
[bolGageCalibrate] [bit] NOT NULL,
[bolGageRepair] [bit] NOT NULL,
[bolGageEvaluate] [bit] NOT NULL,
[bolGageAccredited] [bit] NOT NULL,
[bolGageReturned] [bit] NOT NULL,
[bolGageException] [bit] NOT NULL,
[bolGageExceptResolved] [bit] NOT NULL,
[bolGageLeavePriceBlank] [bit] NOT NULL,
[intGageCost] [real] NULL,
[intTurnaroundDaysOut] [int] NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tblPOGaugeDetail$PrimaryKey] PRIMARY KEY CLUSTERED
(
[intItemPOID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblPOGaugeDetail] WITH CHECK ADD CONSTRAINT [tblPOGaugeDetail$tblPODetailtblPOGaugeDetail] FOREIGN KEY([intGagePOID])
REFERENCES [dbo].[tblPODetail] ([intPurchaseOrderInstance])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] CHECK CONSTRAINT [tblPOGaugeDetail$tblPODetailtblPOGaugeDetail]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [intGagePOID]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((1)) FOR [bolGageCalibrate]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageRepair]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageEvaluate]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageAccredited]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageReturned]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageException]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageExceptResolved]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [bolGageLeavePriceBlank]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [intGageCost]
GO

ALTER TABLE [dbo].[tblPOGaugeDetail] ADD DEFAULT ((0)) FOR [intTurnaroundDaysOut]
GO


Query statement for purchase order details that I want the counts to appear in

SELECT dbo_tblPODetail.intPurchaseOrderInstance, dbo_tblPODetail.strSupplierCode, dbo_tblPODetail.bolPOOpen, dbo_tblPODetail.intPONumber, dbo_tblPODetail.datDateSent
FROM dbo_tblPODetail
WHERE (((dbo_tblPODetail.bolPOOpen)=True))
ORDER BY dbo_tblPODetail.datDateSent;


I've been a bit frustrated as I've read a bit on both DCount() and SELECT COUNT(*), but can't seem to get -either- of them to work. Any help would be greatly appreciated! Thanks in advance!

-- Edited 06/18/2009 @ 1542 hours --

This is an example of the SELECT COUNT I tried to use in code when the subform's On Current event triggered:

SELECT COUNT(*) AS ItemsSent
FROM [dbo_tblPOGaugeDetail]
WHERE [dbo_tblPOGageDetail]![intGagePOID] = [dbo_tblPODetail]![intPurchaseOrderInstance]


This is an example of the =DCOUNT statement as attached to the control in the subform.

=DCOUNT("*", "[dbo_tblPOGaugeDetail]", "[intGagePOID] = [Forms]![frmSupplierInfoMain]![subfrmOpenPOBySupplierID]![intPurchaseOrderInstance]")


Neither of these worked - sadly.

Answer Source

Does your query work without the count(*)?

Use an actual field name for the first arguement of DCount Get the control name out of the where string.

For DCOUNT("[intItemPOID],"[dbo_tblPOGaugeDetail]","[intGagePOID]=" & Forms]![frmSupplierInfoMain]![subfrmOpenPOBySupplierID]![intPurchaseOrderInstance])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download