Textbox expression

[DZ]

The underlying record source of a form has a VehicleDescription field that
has the Automobile make model and year all together. Here are examples of the
Field values

CHEYROLET VAN 2007-GAS
FORD VAN 2004-GAS
FORD VAN 2003-CNG
LINCOLN-2002
Description
2003 FORD ADA
2005 CHEVY ADA
2005 FORD ADA
2003 FORD
2004 FORD

I have two text boxes on the form.

For each record
In one text box I want to display only the year (Example 2003)
In the other text box I want to display all the text except the year
(Example:FORD VAN -CNG)

the vehicle years go as far back as 1975 so an expression that uses
something like 19## and 20## where # represent any numeric digit would
return accurate results. I just don't know what kind of expression to use to
return what I want.

Thanks for any ideas

 

[Steve Sanford]

OK, I guess it goes without saying thatwould be easy if the fields were
atomic; ie the year, make and model in seperate fields??


first, add this code to the form:

'****code beg***************
Public Function YearDesc(theDesc As String, YorD As String) As String
Dim PosStart As Integer
Dim tmp As String

tmp = ""

'get the start pos of the year
PosStart = InStr(Nz(theDesc, ""), "19")
If PosStart = 0 Then
PosStart = InStr(Nz(theDesc, ""), "20")
End If

Select Case YorD
Case "Y" 'Get Year
If PosStart <> 0 Then
tmp = Mid(Nz(theDesc, ""), PosStart, 4)
End If
Case "D" ' Get description
If PosStart <> 0 Then
tmp = Trim(Left(Nz(theDesc, ""), PosStart - 1))
tmp = tmp & Trim(Mid(Nz(theDesc, ""), PosStart + 4))
Else
tmp = Trim(Nz(theDesc, ""))
End If
End Select

'return value
YearDesc = tmp

End Function
'****code end***************



If the name of the field that has "2004 FORD" is not "Description", change
it to your field name in the following.

Then, in the form for the text box for the year, set the control source to:

=YearDesc([Description],"Y")

and in the control source for the text box for the rest of the text, enter:

=YearDesc([Description],"D")


HTH

 

[DZ]

Thank you. The functions works great. But the text box displays #Error if the
field is null. I would like it to display nothing (either "" or Null) if the
field is null

I slightly altered your function by adding a check for isNumeric to make
sure it returns a year and not something like 20XT and I renamed some of the
variables.

Can you please help me avoid the #Error in the text box if the field is null.

Public Function ExtractYearOrDesc(strTextToProcess As String,
YearOrDecription As String) As String
Dim PosStart As Integer
Dim tmp As String

tmp = ""

'get the start pos of the year
PosStart = InStr(nz(strTextToProcess, ""), "19")
If PosStart = 0 Then
PosStart = InStr(nz(strTextToProcess, ""), "20")
End If

Select Case YearOrDecription
Case "Y" 'Get Year

If PosStart <> 0 Then
tmp = Mid(nz(strTextToProcess, ""), PosStart, 4)
tmp = IIf(IsNumeric(nz(tmp, "")), nz(tmp, ""), "")

End If

Case "D" ' Get description

If PosStart <> 0 Then
tmp = Trim(Left(nz(strTextToProcess, ""), PosStart - 1))
tmp = tmp & Trim(Mid(nz(strTextToProcess, ""), PosStart + 4))

Else
tmp = Trim(nz(strTextToProcess, ""))
End If

End Select



ExtractYearOrDesc = tmp

End Function

 

[Steve Sanford]

DZ,

Change the control source for text box for the year to:

=IIF(IsNull([strTextToProcess],"", ExtractYearOrDesc([strTextToProcess],"Y"))


and in the control source for the text box for the rest of the text to:

=IIF(IsNull([strTextToProcess],"", ExtractYearOrDesc([strTextToProcess],"D"))


HTH