CVar Function

[Guest]

I have an append query that contains an expression as a field that will not
update without the CVar function being used. The expression is as follows:
MUserName: CVar([Forms]![frmfield]![UserName]). This expression will return
the username without the CVar function, but the append query will not update
without the function. Can anyone explain why?

Thanks.

 

[Allen Browne]

The need for CVar() suggests that JET is not understanding the data type you
expected. Converting it to a Variant has the side effect of making the value
flexible enough that JET can plug it into the target field.

An alternative solution might be to declare the parameter so Access
understands the data type. In query design view, choose Parameters on the
Query menu (or the Design tab of the ribbon in Access 2007, Show/Hide
chunk.) Then enter the name and type in the dialog. If the value is going
into a field of type Number field, you probably want:
[Forms]![frmfield]![UserName] Long Integer

If it is going into a Text field, and UserName is an unbound text box, make
sure there's nothing in the Format property of the text box. (If it is a
Number field, set Format to something numeric, e.g. General Number.)

If it is a Text field, declaring the parameter can have undesirable side
effects when the text box is null:
http://allenbrowne.com/bug-13.html