Breaking up composite text field (un-concatenation)? 2

[JackDempsey]

I'm dealing with a composite text field that stores a required fiscal week in the format yyyyww. For example, 199952 is year 1999, week 52.

Can I un-concatenate this string for evaluation in Design view or directly Access SQL? Thanks!

 

[BMeek]

It is possible to split the field using the left or right trim formula. For example if you use the following in a query it will separate the values.

Trim(Right([FieldName],2)) this will trim the right two characters from the string.

Trim(Left([FieldName],4)) this will trim the first four characters off the front of the field.

In query grid where the field name is you can use it like this:

Year:Trim(Left([FieldName],4))

Week:Trim(Right([FieldName],2))
Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[RickSpr]

The Left() and Right() functions actually do the "unconcatenation". Wrapping their results in the Trim() function removes leading and trailing spaces from each substring. Since you have a year and week number, your probably don't have spaces and don't need to use Trim().

In design view, you can set a text box's Control Source to =Left([fieldname],4) and =Right([fieldname],2). Keep in mind, though, that they won't be updatable if you do this, because the control source is calculated. If you need these to be updatable, they should really be separate fields in the underlying table, but if that's not possible, you can use the Form_BeforeUpdate event procedure to re-concatenate the values of these fields and store them in the recordset field:
Private Sub Form_BeforeUpdate()
Me![fieldname] = Me![txtYear] & Me![txtWeek]
End Sub Rick Sprague