How do i calculate the difference between two dates and display the result in a field

[krellda]

The Vat rate is not a constant, storing the vat rate means you don't have to change codes accordingly
i.e. Total: [somefield]*[vatrate]

good point. i'll change it in my system then, i did think about this now that you mention it because its changing from january 1st here. and i just thought i don't mind changing the number, but what you said makes more sense. thanks.

any help on my other issue of filtering the query results to just the form i have open?

Thanks
Dan

 

[Rich]

I assume the payment details are in a separate table from the client and that the client ID is stored in that table along with a PK for the payments?

 

[krellda]

I assume the payment details are in a separate table from the client and that the client ID is stored in that table along with a PK for the payments?

i haven't actually set a primary key in either but other than that yes you are right.
The ONLY reason i haven't setup primary keys is cause i'm currently testing everything and am likely to re-build the system after this, what do you think would be a good choice for the primary key on each, it might be my rusty knowledge of using access in college but isn't the primary key an autonumber, or is that just if you don't select one of the fields you already have in the table?

 

[Rich]

The primary key in the client table becomes the fk in the payments table, I would also have a PK in the payments table etc.

 

[krellda]

The primary key in the client table becomes the fk in the payments table, I would also have a PK in the payments table etc.

fk? sorry i'm not good on the abbriviations. i'll set the primary keys up then, by doing that do i add something to the query telling it to only open the records that are linked via the PK's? if so how?

 

[Rich]

The Primary key in one table becomes the Foreign key in related tables

 

[krellda]

The Primary key in one table becomes the Foreign key in related tables

Right so that links the tables together and solves the problem?

 

[Rich]

With the client id as the fk in the payments table you can easily restrict the viewing of records for just that client, with the pk from the payments table you can just view the records pertaining to whatever payment you want and so on