Module /script not working anymore! HELP

[shewolf]

Way back in 2004 this wonderful group helped me create a module that split a long folder title field into two smaller chunks that i could export into my label software. Unfortunately now it's not working anymore.

Public Function reduceit(strIn As String) As String
'this breaks up long titles into shorter portions to fit on label design
'without this the design breaks in middle of words
If Len(strIn) < 35 Then
reduceit = strIn
Else
Dim export_string As String
Dim n As Long
n = 35
Do While Mid(strIn, n, 1) <> " "
n = n - 1
Loop
export_string = Mid(strIn, 1, n)
reduceit = export_string
End If
End Function



Public Function secondline(strIn As String) As String
Dim reducit As String
'This breaks up the title field to create the second line of text

If Len(strIn) > 35 Then
Dim n As Long
n = 36
Do While Mid(strIn, n, 1) <> " "
n = n - 1
Loop
reducit = Mid(strIn, 1, n)
Dim A As String
A = Len(reducit)
Dim B As String
B = A + 1
export_string = Mid(strIn, B)
secondline = export_string
Else
End If
End Function

My query field headings look like

Project Title line one: reduceit([DSC]![FolderTitle])

It's been working wonderfully for all this time. Now instead of the broken up strings of text that I can import into my label software my field results only show as #Error when I run it as Select Query. When I run it as a make-table query I get a "type conversion error".

I've verified that the field lengths and types all match so I am totally stumped as to why this is no longer working.

Any and all help will be GREATLY appreciated.

Thank you,

C.

 

[RuralGuy]

Have you checked to see if you have any reference issues?

 

[Moniker]

There's nothing that would need a reference in that code that I can see. What exactly is DSC!FolderTitle? Something has broken down there. I know it's a reference to the folder title you potentially want to split -- that's not what I'm asking. Instead, what's an example of it? Something tells me either the name of that field or that form changed, which is producing the error.

A simple way to tell is to put a NULL test right at the top of the ReduceIt function, like this:

Public Function reduceit(strIn As String) As String
'this breaks up long titles into shorter portions to fit on label design
'without this the design breaks in middle of words

[COLOR="Blue"]If Nz(strIn,"") = "" Then
    MsgBox "Invalid folder name -- cannot split."
    Exit Function
End If
[/COLOR]
If Len(strIn) < 35 Then
reduceit = strIn
Else
.
.
.

If you get that MsgBox message ("Invalid..."), you've found the problem.

 

[shewolf]

Thanks for the suggestions, Moniker. Unfortunately, that didn't work so evidently that's not the problem. I still got the same "type conversion error"


"Project Title line one: reduceit([DSC]![FolderTitle])" is a calculated query field value with the column heading of "Project Title Line One". It is set up to take the result of the field "Project Title" and run it through the reduceit script to break it into the two lines for the label software, which only allows 30 characters per line.

 

[shewolf]

Weird...I't working again...

Tried the References suggestion. None said they were missing so tried the thing about marking and unmarking one and that seemed to work.

Very weird.

Thanks everyone for all your help.

Once again this forum ROCKS!

C.

 

[RuralGuy]

That's why I reference that article. It is surprising what sort of problems can be resolved by refreshing the references. Glad to hear you are back up and running and glad I could help.