How do I get this month's sales in a field?

[Ali Edwards]

Please help - I'm pulling my hair out!

When I get a sale I want to check which month it is and add the amount to a field. I have a field for MaySales, JuneSales etc so I'll use similar code for each month, changing the month each time.

I'm currently doing this on the 'on exit' event of the 'Ticket Spend' field although when I get it working it should probably go on the 'After Insert' event of the form (?).



My code is:

If Month(Date) = 5 Then
If [Forms]![MainForm]![subfrmSales].Form!MaySales = 0 Then
[Forms]![MainForm]![subfrmSales].Form!MaySales = Me.TicketSpend
Else
[Forms]![MainForm]![subfrmSales].Form!MaySales = [Forms]![MainForm]![subfrmSales].Form!MaySales.OldValue + Me.TicketSpend

End If
End If

It's not working!! It keeps adding a mystery amount each time!

Really grateful for any help!
Thanks

 

[RuralGuy]

Is the MaySales control in your SubForm bound to a field?

 

[Ali Edwards]

Hi there!
The MaySales field is in the subform and it's control source is a table.

 

[RuralGuy]

I have to be honest and say I have some concerns about the design of your db. Having a field named MaySales sets off alarms here on the old homestead. So there is a field that represents each month of the year? That's called repeating fields. It is also called storing a calculated value - another no-no in db theory. Maybe if you described your tables more it would help.

 

[raskew]

A separate field for each month is a 'spreadsheet' approach and indicates a non-normalized structure.

Using a more normalized structure, e.g.
SaleID - PK
CustomerID - FK
SaleDte - datetime
SaleAmt - Currency
SaleDescription - Text

...for all entries. A by-month breakout can then be achieved using a crosstab query.

Bob

 

[Ali Edwards]

First I ought to explain that I'm really bad at this - so you're very welcome to call me an idiot...and I think I might have misled you.

I don't need to store the MaySales value in a table - it was just something I tried in the process of trying to make it work. The business concerned is an annual event and trading only takes place between March and September, so only 7 months (fields) are required. I thought that as I was checking the current date (of the new record) against what month it is, say May ..(If Month(Date) = 5 Then).. I would HAVE to store the answer in a table because I don't know how else to store it.

The main form holds all the usual names, addresses and sales details etc and it has a form footer where certain calculated fields exist, like sum of sales, sum of attendees etc. All is fine with the form but this is where I wanted to put the monthly sales fields (in the footer). It didn't work so I created a subform based on a table with the fields MaySales, JuneSales up to SeptemberSales and put the subform in the footer of the main form.

All I want to do when I add a new sale (record) is determine what month it is and add the value of the sale to a field for that month, so that at the end of each month I can easily see what we've taken for the month. I thouhgt it would be easy and I've spent all day on it!

Anyway, many thanks for your help.

 

[RuralGuy]

It really does not matter that the operation runs for 7 months a year. Each "sale" should be a record in your Sales table and then it is very easy to use a query to get total sales for any month as long as you have recorded the date of the sale.

 

[Ali Edwards]

Ok well I've added a date field now that populates on insert. Bit late I know..

 

[RuralGuy]

That will make it a bit difficult to get the sales by date prior to today.

 

[Ali Edwards]

Yes it will! I already have the figures up till May in a spreadsheet - I can manually enter the May sales to date and then at least I'm up and running for the rest of this year and ready to go properly next year... I'm stupid for not having a salesdate field really.

Thanks for your help on this - very grateful. I think I can remember how to work it from a query now so I should be OK.
All the best.

 

[RuralGuy]

Great! Lots of success with the rest of the project and we're here if you need us for something.