Can I build a table using wildcards?

[sylbaryn]

Hi. I'm a bit of a newby so hope you can help advise me on the best approach to take here.



I'm trying to link a pivot table in excel to a query in access. I can link up but I would like to be able to split my data in the pivot by 'month'. The thing is, I don't have a month field in the database and I can't use a date to extract the month because I'm looking for a 'work' month which means the date entry could actually be from the next/previous calendar month. What I do have is a reference that combines the month number and an ID so for instance 01-1234 would be from January and anything with "02-" at the start would be from February.

My first thought was to create a table in access to link up "01-" with "Jan" and "02-" with Feb etc. However, I will have a few thousand entries starting with "01-". If I could use a wildcard asterisk that would do the trick. So "01-*" = Jan. But I don't think I can do this? Or can I?

My second thought was to extract the first two characters from the combined reference

My third though was to do something with a calculated cell within the excel pivot table.

To be honest I'm not sure how to approach any of these option and in the back of my mind I'm thinking there must be an easier approach. Hence why I'm here

Any guidance would be most appreciated.

[Gicu]

In your Access query add a calculated field:
WorkMonth: MonthName(Cint(Left([ID],2))) will give you January for ID="01-1234". Then use that in your pivot.

Cheers,

[sylbaryn]

That is surely witchcraft! Works a treat. Thank you very much

[Gicu]

You're very welcome!

Cheers,