Newbie Form Help

[bchase]

Hi I have made my first database to track cars as they move through our system. We undertake simple tasks on vehicles including transport and valeting. The data is sample data only but hopefully it's clear what I am trying to do.



However a few places where I've got stuck which I'd love some help with!

1. I have been experimenting with the Master Form and want to include in it the mileage field from the Collection Point table and also the mileage Exit Port table. That way when I enter the Auction Centre it will display the mileage on the form. This works fine when you create it but when you save, close and reopen the form it won't show any records at all (although i have deleted most records in the attached version).

2. I want to set the database up so that by setting the auction centre, and the transport batch up it works the transport price and transport cost out as stored in the Collection Point table. I know I've done it wrong but would love some help to structure this properly.

Thanks

[aytee111]

1. The reason you show no records in your form is that there are no records to see. The joins in your record source query demand that data exists in all three tables. The data you have currently in your database is missing data in the Exit Port table so the join from the Master table produces no entries. Either you have to make Exit Point a mandatory entry so that you cannot save a record unless a value has been entered or else change the join type on your query to be optional.

2. Bring in the costs you need in your record source and put the fields on your form. You can make them invisible if you want to. Then in the AfterUpdate event of Auction Centre you can use those fields to make calculations.

I don't know what you think you have done wrong. I didn't go into your database that closely. Are you worried about the table design or what?

[bchase]

Many thanks, I think I'm getting there. I have now linked it all up and have managed to create a simple invoice query that adds up all the charges against a vehicles. HOWEVER if any of the changes are blank the query ignores them and hence the total calculation is blank.

Can anyone advise how to get Access to treat blanks as zeros?