Problem accessing property or method of OLE object / Sort Order

  • Thread starter Thread starter Nick Stansbury
  • Start date Start date
N

Nick Stansbury

Hi,
When binding a form to a sql server table I am having a problem
specifying an order by clause on some fields. Works absolutely fine in query
analyser but falls down in access. Any ideas? SQL is posted below

Thanks

Nick

--
SELECT
TEV170payments.*
FROM
TEV160charges
INNER JOIN TEV170payments ON TEV170payments.ChargeID =
TEV160charges.ChargeID
WHERE
((TEV160charges.PayType < 5))
ORDER BY
TEV160charges.ChargeID (fails on chargeId & payType but is ok on attendId).


(CREATE TABLE [TEV170payments] (
[PayID] [int] IDENTITY (1, 1) NOT NULL ,
[AttendID] [int] NULL ,
[ChargeID] [int] NULL ,
[Currency] [nvarchar] (5) NULL ,
[Amount] [float] NULL ,
[Comments] [nvarchar] (255) NULL ,
[EntryDate] [datetime] NULL CONSTRAINT [DF__Temporary__Entry__498EEC8D]
DEFAULT (getdate()),
[EntryBy] [nvarchar] (100) NULL ,
[Update] [datetime] NULL CONSTRAINT [DF__Temporary__Updat__4A8310C6]
DEFAULT (getdate()),
[By] [nvarchar] (50) NULL ,
[Pick] [bit] NULL CONSTRAINT [DF__TemporaryU__Pick__4B7734FF] DEFAULT (0),
[upsize_ts] [timestamp] NULL ,
CONSTRAINT [aaaaaTEV170payments_PK] PRIMARY KEY NONCLUSTERED
(
[PayID]
) ON [PRIMARY] ,
CONSTRAINT [TEV170payments_FK00] FOREIGN KEY
(
[AttendID]
) REFERENCES [TEV130eventAttendance] (
[AttendID]
),
CONSTRAINT [TEV170payments_FK02] FOREIGN KEY
(
[ChargeID]
) REFERENCES [TEV160charges] (
[ChargeID]
)
) ON [PRIMARY]
)


CREATE TABLE [TEV160charges] (
[ChargeID] [int] IDENTITY (1, 1) NOT NULL ,
[Sort] [smallint] NULL ,
[PersCode] [nvarchar] (7) NULL ,
[ChargeGroup] [smallint] NULL ,
[PayType] [smallint] NULL CONSTRAINT [DF__Temporary__PayTy__40058253]
DEFAULT (0),
[ChargeItem] [nvarchar] (255) NULL ,
[Currency] [nvarchar] (20) NULL ,
[Amount] [float] NULL ,
[Deadline] [datetime] NULL ,
[Update] [datetime] NULL ,
[EntryBy] [nvarchar] (10) NULL ,
[Pick] [bit] NULL CONSTRAINT [DF__TemporaryU__Pick__41EDCAC5] DEFAULT (0),
[upsize_ts] [timestamp] NULL ,
CONSTRAINT [aaaaaTEV160charges_PK] PRIMARY KEY NONCLUSTERED
(
[ChargeID]
) ON [PRIMARY] ,
CONSTRAINT [TEV160charges_FK00] FOREIGN KEY
(
[PersCode]
) REFERENCES [TPE200peopleTypes] (
[PersCode]
),
CONSTRAINT [TEV160charges_FK01] FOREIGN KEY
(
[ChargeGroup]
) REFERENCES [TEV250chargeGroups] (
[ChargeGroup]
),
CONSTRAINT [TEV160charges_FK02] FOREIGN KEY
(
[PayType]
) REFERENCES [TEV260paymentTypes] (
[PayType]
)
) ON [PRIMARY]
GO





_
 
Back
Top