Default value via dlookup doing a calculation instead giving the v

[Maverick]

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.

 

[Marshall Barton]

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.

When the DefaultValue property is applied to a field in a
new record, it is evaluated as an expression. This means
that you have to set the DefaultValue property to something
that evaluates to what you want. This can be rather
confusing until you really want to use an expression that
does some kind of serious calculation so I try to explain
with some examples.

If you use code like:
Me.combo.DefaultValue = DLookup( . . . )
then the property is assigned the string
2007/2008
which will then be evaluated as an expression with the
result you are seeing.

If you do not want the DefaultValue to do that, then use:
Me.combo.DefaultValue = """" & DLookup( . . . ) & """"
This will set the property to
"2007/2008"
and the quotes will be removed when this simple expression
is evaluated.

As an alternative, you could take advantage of the
evaluation by setting the DefaultValue property to
DLookup( ... ) and not use any code at all. This way the
DLookup will be reevaluated when you initiate a new record
instead of in your code when the combo box's value is
changed. In this particular case, I would leave it in the
code, but it may not make a significant difference.

BTW, I think(?) your DLookup is missing an important space
and it does not need the table name in the first and third
arguments. You should probably just use:
DLookUp("DateYear", "tblReportYears", "Right(DateYear,4)
='" & Year(Date()) & "' And FiscalCalendar='Fiscal' ")

 

[Maverick]

Thanks. That did it. I swear I tried that code, but it must have been the
only version I didn't try. I put quotes everywhere else though. LOL

How do you give rep in this forum?

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.

When the DefaultValue property is applied to a field in a
new record, it is evaluated as an expression. This means
that you have to set the DefaultValue property to something
that evaluates to what you want. This can be rather
confusing until you really want to use an expression that
does some kind of serious calculation so I try to explain
with some examples.

If you use code like:
Me.combo.DefaultValue = DLookup( . . . )
then the property is assigned the string
2007/2008
which will then be evaluated as an expression with the
result you are seeing.

If you do not want the DefaultValue to do that, then use:
Me.combo.DefaultValue = """" & DLookup( . . . ) & """"
This will set the property to
"2007/2008"
and the quotes will be removed when this simple expression
is evaluated.

As an alternative, you could take advantage of the
evaluation by setting the DefaultValue property to
DLookup( ... ) and not use any code at all. This way the
DLookup will be reevaluated when you initiate a new record
instead of in your code when the combo box's value is
changed. In this particular case, I would leave it in the
code, but it may not make a significant difference.

BTW, I think(?) your DLookup is missing an important space
and it does not need the table name in the first and third
arguments. You should probably just use:
DLookUp("DateYear", "tblReportYears", "Right(DateYear,4)
='" & Year(Date()) & "' And FiscalCalendar='Fiscal' ")

 

[Marshall Barton]

Thanks. That did it. I swear I tried that code, but it must have been the
only version I didn't try. I put quotes everywhere else though. LOL

How do you give rep in this forum?

You're welcome.

Rep?? If you mean acknowledging a reply helped you solve
your problem, then it depends on how you got here. If you
used the MS web interface, then there is a button to click.
If you used an email/newgroup program such as Outlook
Express, then you just did ;-)