Solved Help with using a Date Field to generate an invoice number

[Sue_VTA]

Hi Everyone - (new user / new to Access!)
I have an Invoice Entry Form to add new invoices and have a txtInvoiceNumber txt box that is formatted as "yymmhns" which gives me e.g 2211412 as an invoice number. Which is what I want...
However - also on my form I have a command box that will save the invoice as a PDF to a certain file path. I have tried putting in some code to include the invoice number as part of the file name when it saves but I think because the txtInvoiceNumber field is a date field it is trying to bring the data through as yy/mm/dd etc and the slashes are a special character that can't be used in file names. So when I try to save it with my button it is failing.

Is there a way to either convert the Invoice Number value to text either by having a separate txt box on my form to hold this data or to format the txtInvoiceNumber box as it generates the number.

Any help with easy code to copy and paste with instructions would be fab.
please note - I am not a programmer, so be gentle!!

Many thanks
Sue

 

[June7]

Use Format() function.

What field is txtInvoiceNumber bound to and what is its data type? How is this field populated - DefaultValue of Now()?

 

[Sue_VTA]

Use Format() function.

What field is txtInvoiceNumber bound to and what is its data type? How is this field populated - DefaultValue of Now()?

Hi - So the InvoiceNumber is bound to a table tblInvoice I have attached a screenshot of the settings against it in my table

 

[arnelgp]

as suggested, use format:

dim filename as string
filename = "sometext" & Format$([txtInvoiceNumber], "yymmhns") & ".pdf"

 

[June7]

So that is a Date/Time type field? Format property is just a display setting - does not change the saved value. A full date is saved to field. Use format function to change the date value to a string with desired structure.

 

[Sue_VTA]

as suggested, use format:

dim filename as string
filename = "sometext" & Format$([txtInvoiceNumber], "yymmhns") & ".pdf

 

[Sue_VTA]

This worked perfectly - thank you so much for your help - I have been struggling with this for days.!