query question

[didacticone]

i have an append query that sends data to a form. i was wondering if for one of the fields if i could just send the first letter. so if the record says "test" i would like it to only display "t" in the field on the form... is that possible... thanks!

[Stewart Ross]

You can use a simple unbound textbox in the form to do this, using as its control source the Left function to extract the first character from the field concerned. I have also used the Nz function to substitute for nulls in the example below, which will avoid a run-time error that would otherwise occur if Left is applied to a field that has no current value (i.e. is null):

=Left (Nz ([your field name], ""), 1)

You could include this in the query you mention as a calculated field if you prefer.

-Stewart

[didacticone]

when i put that string in the control source field of an unbound text box, the box returns "#error" .... any idea as to what i could be doing wrong.. this is the code i used

=Left(Nz([town],""),1)

[Stewart Ross]

The #Error occurs when the name of a field or control - [Town] in this case - either does not exist, or is already in use, or if the name of a function is unrecognised.

Check that the unbound control does not have the same name - Town - as you are trying to feed into its control source expression. Check also that the field you have named - Town - exists in the query or table you are using as the recordsource of your form. If it does not you will certainly get an error when you try to refer to it!

If this is OK then check what happens if you remove the Nz function - so just leaving

=Left([Town], 1)

I have come across some occasions when Nz is not recognised as valid. If this does occur, replace the expression with:

=IIF([IsNull([Town]), "", Left([Town], 1))

-Stewart

[didacticone]

that was the problem lol... i have the text box the same name as the field... thanks so much for your help.. it works great!