String Manipulation code

[kmclane]

OK, I need to take the last two digits from a string, strip them off and put them on the front. Sounds simple. I am drawing a blank, I've looked in the help files and can't find any help.

Thanks for any suggestions, Ken

 

[Dugantrain]

The funner way (if you're a programmer) is to write a VBA function to do this. Here's the easier way:
Use an Update Query. Pull down the field that you want Updated and in the "Update To" field, write:

Right([Your_Field],2) & Left([Your_Field],Len([Your_Field]-2))

You will probably get an error for strings that are less than two characters long (haven't tested it myself), so you may want to wrap this in an Iif statement to test the length. If greater than two characters, do above, if not, do something else.

 

[kmclane]

That would work great if I was changing the field, but this is a variable that the user enters. I think I actually need to do this in vba, These are date ranges, so I thought if I could get the value after they submit but before it is sent to the query and rearrange it. Is this possible?
Thanks, Ken

 

[Dugantrain]

I'm really not sure what you're trying to accomplish or what you're set up is. Is this a parameter query that the user is entering data into? Or are you trying to capture something from a form and store it? Need more details.

 

[kmclane]

I have two date fields on a form, startdate and enddate. These are sent to a parameter query. Actually to three different queries, depending on another variable set on the same form. My problem is that the queries are being run against three different tables, two of which use a field to store a date in YYMMDD format, the other uses the more common MMDDYY. What I need to do is when the user chooses one of the oddball db's to take the value fron startdate and enddate MMDDYY and move the last two numbers YY to the front so when it gets passed to the query it goes as YYMMDD. I hope this clarifies better. Thanks, Ken.

 

[kmclane]

Well, I tried to incorporate this code:

If Forms![MultiQuery]![QueryChoice].Value = 2 Or 3 Then
Forms![MultiQuery]![StartDate] = Right(Forms![MultiQuery]![StartDate], 2) & Left(Forms![MultiQuery]![StartDate], Len(Forms![MultiQuery]![StartDate] - 2))
Forms![MultiQuery]![Enddate] = Right(Forms![MultiQuery]![Enddate], 2) & Left(Forms![MultiQuery]![Enddate], Len(Forms![MultiQuery]![Enddate] - 2))
End If

But I got a "type mismatch error" which doesn't help me a whole lot. Maybe I can just have a msg box pop up to instruct them to enter the date differently, I can't seem to find a way to manipulate the value. Thanks, Ken.