Custom format of date in a report

[CelevGadol]

Hi everyone
I haven't been doing databases for very long so I'm sure my question will easily be solved. I am trying to create a report in order to invoice customers. The reference that I have been using has been the first four letters of the company's name followed by the 8 digits of the date. I haven't been using Access to generate invoices. I would now like to use Access to generate invoices, hence creating a report for the invoice which hopefully will take the manual work out of it and also eliminate any errors. In addition to this, invoices can be amended by correcting any data that has been incorrectly entered so that everything ties up properly.

My problem is that if I create a text box with the first four letters of the client's name, I can concatenate the date but only in the format dd/mm/yyyy. Is it possible to format this in the text box so that it leaves out the '/' so that I have nameddmmyyyy?

What I have done so far is something like, ="name" & date() in the text box which sort of works but has the slashes in.

Help!!!

 

[theDBguy]

Hi. Check out the Format() function.

=[name] & Format(Date(), "ddmmyyyy")

 

[CelevGadol]

Wow! That was quick! I've just tried it and it works a treat. Thanks very much.
Just so that you don't think I didn't try anything like that, I did. I tried the format function but I used it with square brackets which I think might be more VBA syntax but I'm not sure. Anyway, thanks again.

 

[theDBguy]

Wow! That was quick! I've just tried it and it works a treat. Thanks very much.
Just so that you don't think I didn't try anything like that, I did. I tried the format function but I used it with square brackets which I think might be more VBA syntax but I'm not sure. Anyway, thanks again.

Hi. You're welcome. Glad to hear it worked for you. Good luck with your project.

 

[Pat Hartman]

What if you have to send two invoices in one day?

In the olden days when everything was on paper, it was important to use this type of numbering scheme because the Invoice number itself gave you information. Today, everything is in the computer and all you need is a search feature or a combo. Much of the old value disappears.

In any event, do NOT use the user-friendly invoice number as the PK for the invoice. Use an autonumber and add a unique index for the Invoicenumber to prevent duplicates.

If you want to actually be able to sort the invoice numbers, use yyyymmdd for the date so you can easily sort ascending or descending. Using string dates as mmddyyyy gives you all the February items together which tends to be less helpful than being able to easily find the newews.

 

[CelevGadol]

Thanks for the tip Pat. The date number is just a reference number which gives me the company and date if the invoice is queried so it's just a text reference rather than database related. I invoice customers on the last day of the month and only ever once. If it came to it I guess I would have to add a suffix if multiple invoices were sent to a single customer and this might not be the best way. As you say, using an automatically generated PK would be a safer way. Also, I haven't figured out how to generate the invoices yet so it might be a while until it matters. Finally, I haven't worked out how to save an individual invoice once it has been generated. The way I am trying to do it at the moment is generating a monthly report based on a monthly query which would then be exported as a PDF and saved in the relevant folder separate from the DB.

I'm struggling with something else at the moment, which is a count problem.

Imagine that you have 3 products all at different prices and you sell multiple products to a customer. I can create the query which lists the products sold to a single customer this month and I can even group them by price. However, I can't find a way to query how many of each product I have sold, if you see what I mean. Any ideas?

 

[arnelgp]

I can't find a way to query how many of each product I have sold, if you see what I mean. Any ideas?

you also group them and sum.

 

[Pat Hartman]

If an invoice is always 100% of an order, the only thing I would put in the Invoice table is:
InvoiceID (autonumber pk)
InvoiceNum (friendly code, unique index)
InvoiceDT
PaidDT
InvoiceAmt
PaidAmt
OrderID

Then once you generate the invoice, you make a report and send a pdf of it to the client. So, add the invoice to a table first, then generate the report, not vice versa as you are doing it.

I can't find a way to query how many of each product I have sold, if you see what I mean. Any ideas?

You make different totals by including different fields in the query.
Select ProductName, Count(*) As SoldCount
From YourTable
Where SoldDT Between Forms!YourForm!txtStartDT AND Forms!YourForm!txtEndDT;

 

[CelevGadol]

Thanks arnelgp. I've messed about with summing and grouping quite a bit and couldn't figure it out . I'll have another play with it and see what happens.

 

[CelevGadol]

Thanks a lot Pat. That's good advice. I should have thought of it really. I know enough to understand that everything revolves around tables and their relationships . Sometimes I can be a bit thick .