Access MS Access Concatenate a date and text field

[Jerry Tack]

I want to combine a text field and a date field into one field to use as a reference number. IE 'Mil - 05/11'.

I have got a field with the standard short date format 29/05/2011 (that I have to keep) and I have created a second field with the same date but formatted to a "mm/yy". When I concatenate the Operater Ref field "Mil" to the Date Ref field "05/11" I still get the full date format IE Mil - 29/05/2011.

 

[MikeStuck]

Jerry-

If you are creating the second field in your table, go to design view and change the Data Type to Text then try your concatenation. I built a mini-table and queried it and this worked for me... hope this works for you!

 

[Jerry Tack]

Hi Mike

Many thanks for your feedback

Unfortunately this is not going to work because the Ref Date is a calculated field that reads the data from the Date of Job field. The Ref Date field is the one that I've changed to mm/yy but it still sees the underlying information from the Date of Job field and displays that. I have partially solved the problem by entering an expression in the calculated data type of the concatenated field called the Ref No

This expression is [Ref Oper] & "-" & Month([Ref Date]) & Year([Ref Date])

and this produces a Ref No e.g. Mil - 52011 which is part way there and may have to do, but I would prefer to get rid of the '20' and just leave the Mil - 511.

Jerry

 

[MikeStuck]

Hmm... what about trying adding a new column Ref No Final: =left(Ref No,7)& right(Ref No, 2)

 

[Jerry Tack]

Hi MIke

This works brilliantly until you get to Oct, Nov and Dec then it only pulls the first digit from the month '1' instead of 10, 11 or 12. To get round this I need to tell the Ref Number field which contains the Ref Operator and Ref Date data to see and display the leading zero in the Ref Date field. I.E. the Ref Date field contains 0611 (June 2011), which is the full date but I have formatted the column as 'mm/yy', but when the Ref Number pulls the data through it displays 611. If I could get the leading zero then I could increase the string size from the left and it would be sorted.

So so near!!

 

[MikeStuck]

OK, let's try this. Make a new column called Ref Date1 and change the format as follows: Ref Date1: Format([Ref Date],"mm,yy").

Now make a new column Ref No Last: [RefOper] &" - "& Left([Ref Date1],2) & Right([Ref Date1],2).

It worked on my test query... let's see if this does the trick!

Mike

 

[Jerry Tack]

Hi Mike

Unfortunately the problem lies in the Ref Date being a calculated field as it has to get the information from another field (Date of Job) which it then reformats to the mmyy.
I created the fields as suggested and rather than obtain the data from the Ref Date field I left it blank and keyed the info in and your plan worked a treat. I now have to get the Ref Date1 field to read the data in the Ref Date field without setting the field up as a calculated field. I have tried several other options but I get the error message telling me that the Ref Date field isn't recognised. How do you get a Text field to get the data from another field?

Jerry

 

[MikeStuck]

Jerry-

What if we try to reformat Date of Job instead of Ref Date?

Ref Date1: Format([Date of Job],"mm,yy").

I tried that with the short date format and it pulled OK here. We'll get this yet!

 

[Jerry Tack]

Hi Mike

Unfortunately I have to keep the full date format for the invoices I send out.

Jerry

 

[MikeStuck]

If you make a new column Ref Date1: Format([Date of Job],"mm,yy") you should still be able to keep the original column as well- so you'd have Date of Job, then next to it in its own spot RefDate1. The RefDate1 will not impact the original Date of Job- it should just display what the data from Date of Job will look like with the format declared. Worth a try?

 

[Jerry Tack]

What data type should I use for this column?

 

[MikeStuck]

Jerry-

I was building all my expressions in the GUI in query design view, and do not need to declare data type- I can even uncheck the box on the "Show" row for it and not have it appear. I am still able to write expressions against it, even if it does not appear in the final output anywhere, so that is nice to keep things tidy. Have I been missing something?

 

[Jerry Tack]

Mike

I'm designing the database table in Table Design view and this expects you to enter a data type with all the required parameters. I could always copy the table and remove most of the data and email you the table but this forum doesn't allow large files or database files, so I would have to do it directly.

 

[MikeStuck]

Jerry-

I was operating under the assumption you were doing a make-table query- my fault for assuming! What I have done to get things here working was to create an excel doc with date fields and text fields (an estimate of the raw data you'd be getting) and then building a make-table query that let me use the expressions I posted. The make-table query automatically assigns a data type, and the ones I created from my left() and right() expressions were sent over as Text when I included them in the table. When I did not add them to the table output but just used them to concatenate with the other text fields to give me the Mil - 0511 the result was a text data type, as I expected. If my guess as to how you are getting/building the data is not correct please give me a brief outline and I will see what I can come up with. Today may be dicey, but soon! Mike

 

[Jerry Tack]

Hi Mike

At present I receive a batch of jobs from several travel operators and I put the relevant information in my Access database. This includes details of the trip from home to airport and back etc along with the actual date and time of the journey; hence the reason I have to keep the date in it's full format as this goes on the invoice. I was them asked to supply reference numbers for each invoice I submitted and I decided to use the first 3 letters of the company and the month/year e.g. Mil - 0611, Ins - 0411.
All the data is entered into the main database and I then run queries and reports to identify the invoice amounts, how much is outstanding and which driver I owe money too and then at the end of each month I then run invoices which I print to PDF and send to the main office who then split them and send them to the relevant travel operators. It may be easier if I send the full database to you and you can see what I'm trying to do, as although I know a bit about Access from my former life as an IT bod it has been a while and my knowledge is scant and self taught. If you want me to do that then let me have your email address and I'll do the honours.

The next step I have planned is to get the post codes linked so that when I enter and full post code it will look at the tables and pull through the correct mileage rate but that's another day.

The final piece of the jigsaw will be to link some mapping software (MS AutoRoute maybe) and when the post codes are entered it calculates the mileage from post code to post code. Not that I'm trying to run before I walk!!!!

Thanks anyway

Jerry

 

[MikeStuck]

Hi Jerry

Hope all is well with you. Let's step back and see if we can come at this from a different angle. If the raw data comes in an Excel doc we can build a Make-Table query to create your table, then use an Append query to keep adding things. That will keep the formatting consistent and you will be able to change up the data to display as you wish before you send it to the table using the expressions from earlier. If the folks giving you the data can be persuaded to use an Excel spreadsheet with all the columns you need, then we may be set. Even if they just email you data you could have an Excel doc where you put the info and have that linked to Access, then run your Append query to add data each time you update the Excel doc.

This would be my recommendation, as it would allow the most automation and standardization, and it can all be done from the graphic user interface. You could even add "dummy" data to the spreadsheet the very first time to make sure ALL your fields are in the table you make and are formatted as you need. You can then delete the lines from the table (not the whole table!) and then append to it as needed. Best of luck to you!

Mike

 

[Jerry Tack]

Hi Mike

The information is entered by me from an email I receive from the supplier. I have already got a load of data in the table so no need to add dummy data. I don't want to add to my work by adding an Excel process when I can simply enter it into the database and then process my invoices as and when. It pretty much works at the moment other than this invoice number business which if I have to I will live with though I would still like to do the job properly.

I will, at a later stage when I know how, add a couple of extras such as the post code option that looks at the post code and vehicle type entered and then display the mile rate based on the combination of the two. I have a different rate for different parts of the country and for vehicle type.

You would get a better idea by looking at the actual database tables

Jerry