Default value via expression

[fire2ice]

I'm not sure if this belongs in this section or not.

I created code to set the default value of an unbound combobox based on the following expression:

DLookUp("[tblReportYears]![DateYear]","tblReportYears"," right([tblReportYears]![DateYear],4) ='" & Year(Now()) & "' and" & "[tblReportYears]![FiscalCalendar]='" & "Fiscal" & "'").

It returns a calculation instead of the actual value in the table. This expression works perfectly in a textbox which returns the value of 2007/2008. However, the result of the code is the calculation of 2007 divided by 2008.

This is a simple dlookup... why is it performing a calculation? I just want it to return the text value of 2007/2008. I've tried making it a string but get a type mismatch error. I'm sure the answer is simple, but it eludes me.

Thanks.

 

[MarkK]

Maybe you can enclose the whole thing in something like...

=cstr(<your stuff>)

You need the "=" sign in the control source, and then tell it it's a string, not a value to calculate.

 

[fire2ice]

Maybe you can enclose the whole thing in something like...

=cstr(<your stuff>)

You need the "=" sign in the control source, and then tell it it's a string, not a value to calculate.

I've tried that already. Also, I'm doing this via code and not in the default value property of the control. This is because the default value is dynamic and changes based on the value of another control.

When I put my expression in the Immediate Window it shows the value I want as 2007/2008. However, when the unbound control gets ahold of it, Access then performs a calculation on the value instead of merely accepting the result. This leads me to believe that it's not the code that is the problem, but how Access handles the unbound control. Perhaps I need to somehow supress calculations in the control (if that is possible).

 

[fire2ice]

The final solution to this issue was some well placed quotation marks.

= """" & DLookup("[DateYear]", "tblReportYears", " right([DateYear],4) ='" & Year(Now()) & "' and" & "[FiscalCalendar]='" & "Fiscal" & "'") & """"