Pulling a month from a text string

[rywello]

I want to add a new column to my query called order month that pulls the month out of a text string from another column in my table. For example in column PO# I have 12 ABCCHAMP and in the new column order_month I want it to pull December from the the numbers in front of the text. So if it is 7 ABCCHAMP then the order_month column would say July and so on. I am using the code MonthName([Asphalt]![PO#]) but I think I am missing something. Any suggestions? Thanks!

 

[plog]

You really would make your life simpler if you stored your data correctly. Discrete data should be stored discretely. That means your PO# field should be at least 2 fields--one to store the month data and one to store the rest of it. Also, you shouldn't use special characters in table/field names. That # makes writing code and queries a little more difficult.

With that said, you need to extract out the month number from your string. You would do that like so:

order_month: MonthName(Trim(Left([Asphalt]![PO#], 2)))

 

[rywello]

Thanks for your help!

 

[rywello]

Is there a way to make it do nothing if there is no number to pull the month from instead of it showing an error? Thanks

 

[arnelgp]

order_month: iif(Va([Asphalt]![PO#])=0, "", MonthName(Val([Asphalt]![PO#])))

 

[rywello]

I am still getting errors on some of the fields that should come back with the month value.

 

[arnelgp]

can you post the [po#]'s, that has error.

 

[Galaxiom]

in column PO# I have 12 ABCCHAMP and in the new column order_month I want it to pull December from the the numbers in front of the text.

It would unusual to have a Purchase Order table that did not include a field for the date. Having to parse it out of a string is a very clumsy arrangement.

 

[rywello]

I get a Func! error on 1 992522 OP and a #Num! error on 1 12507016300. It works with the original code.

 

[arnelgp]

then use the other code, i thought you have string in your po# as you posted: ABCCHAMP.