Control Source Blues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am converting an Access database to an Access Project (adp file). There is
an unbound form that has lots of controls with contol source like this one:

=-(IIf([AsAt] Is Null Or [Begin] Is
Null,Null,nz(DSum("[Amount]","Cashier","[DateOfPayment]>=Forms!TB!Begin And
[DateOfPayment]<=Forms!TB!AsAt And [CashOrCheque]=1 And
[PostedBy]<>Null"),0)+nz(DSum("[RechargeAmount]","AccountRecharging","[RechargeDate]>=Forms!TB!Begin
and [RechargeDate]<=Forms!TB!AsAt And [CashOrCheque]=1 And
[PostedBy]<>Null"),0)-nz(DSum("[PaymentAmount]","Payment","[PaymentDate]>=Forms!TB!Begin
And [PaymentDate]<=Forms!Tb!AsAt And [CashOrCheque]=1 And
[PostedBy]<>Null"),0)-nz(DSum("[Debit]","GLCLodgementDetail","[TDate]>=Forms!TB!Begin And [TDate]<=Forms!TB!AsAt And [CashOrCheque]=1"),0)))

The attraction for this is that each contol can be edited on the "surface"
without going into the form code. Can a similar thing be done in the Access
Project, i.e. make an SQL statement the control source of a control?

2. What would be the correct syntax for selecting records by the date data
fields in an Access Project since whereconditions like *...
[TDate]<=Forms!TB!AsAt ...* (TDate and AsAt are date fields in the underlying
table and form respectively) did not do any filtering at all?

I really hope someone can help me out as I am new to ADO and SQL Server
programming.
 
Yes same idea as mdb's almost all that kind of thing will port over very
easily, be careful of the difference in syntax between talking to mdb or SQL
e.g. wildcard in access is * and in SQL it is %, one of the things I do with
this kind of project is most of my control sources are stored procedures
this enables me to change how things work on the forms without modifying the
ADP, as the modification is done on the SQL server, not good for everything
but quite a good idea to get your business intelligence into the back-end
rather than the front-end if you can, quicker and more robust.

in your example about correct syntax, if you have a control on your form
called AsAt if your stored procedure had a parameter called AsAt it would
automatically take the value from the form without you having to set it.
 
Back
Top