Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

#Type! using replace on Long text field

Status
Not open for further replies.

lameid

Programmer
Jan 31, 2001
4,212
US
I have a control that is returning a #Type! error on a report... The control source is below. Field1 in this case is long text.
What am I missing as FindField is also text... probably short text?

Code:
   = Replace([Field1], "[FindField]", NZ([FindField], ""))

Similar expression works in a query. I am at a loss...
 
Are you sure you want [FindField] in quotes? Are you actually looking for the exact characters "[FindField]"?
This is the documentation:
Replace ( string1, find, replacement, [start, [count, [compare]]] ) from this TechOnTheNet.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Yes that is actually what I am doing. If i could find a clever way to do that replacement from the source data by detecting the fields in square brackets that would be extremely useful. Alas I don't think there is a way to pass the report object to a user defined function to pull it off (Parent may work?)... but that doesn't solve my error issue.
 
It would probably help illustrate your issue if you provided a couple sample records with at least the significant fields.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
I feel stupid...

It was a circular reference... the name of the control was Field1...

My general practice is to always rename the control whenever I do anything to change the the control source from wizard generation... So not sure how this happened this time or why I am not getting a more reasonable error. Side note, I did see this same error earlier to day when someone failed to wrap the replacement value in a NZ function.

Two possible causes of #Type! error I have seen other than more obvious type issue...

Control name is used in the control source (failure to rename control to not be a field in the record source).
The replacement value has a null value (optionally use NZ to handle the null)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top