Unknown function 'Dlookup'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lord.zoltar@gmail.com - 26 Feb 2007 16:42 GMT

Hello,
I'm trying to set a default value for a column in a table to be based
on values in another table. So far, I've discovered that DLookUp is
the way to go for this sort of thing. The text of my default value
looks like this:
=DLookUp("[value]","rates","[name] = 'Gold'")
and when I try to save, I get an error saying "Unknown Function:
'DLookup'".
and I can't seem to figure out why. Anyone have an idea?
Using Access 2003/WinXP Pro

Thanks.

Reply to this Message

lord.zoltar@gmail.com - 26 Feb 2007 16:46 GMT

On Feb 26, 11:42 am, lord.zol...@gmail.com wrote:

> Hello,
> I'm trying to set a default value for a column in a table to be based
[quoted text clipped - 8 lines]
>
> Thanks.

Oh, forgot to add:
when I run the above code through the Immediate Window as:
?DLookUp("[value]","rates","[name] = 'Gold'")
it works fine. But it gives the unknown function error when I try to
make it a default value in the table designer.

Reply to this Message

lord.zoltar@gmail.com - 26 Feb 2007 16:58 GMT

On Feb 26, 11:42 am, lord.zol...@gmail.com wrote:

> Hello,
> I'm trying to set a default value for a column in a table to be based
[quoted text clipped - 8 lines]
>
> Thanks.

Ok I got a work-around:

I put the above code into the default value for the fields on the Form
rather than on the table. It works but it's not great.
still, does anyone know why that code didn't work on the table?

Reply to this Message

George Nicholson - 26 Feb 2007 17:52 GMT

You can't use functions as default values for fields in table design. The
exception to that are a few simple system calls (which require no arguments)
like Date() and Now().

HTH,

> On Feb 26, 11:42 am, lord.zol...@gmail.com wrote:
>> Hello,
[quoted text clipped - 15 lines]
> rather than on the table. It works but it's not great.
> still, does anyone know why that code didn't work on the table?

Reply to this Message

aaron.kempf@gmail.com - 26 Feb 2007 20:23 GMT

I can use functions for defaults using Access Data Projects

I do it ALL THE FRIGGIN TIME

On Feb 26, 9:52 am, "George Nicholson" <GeorgeNJ...@Junkmsn.com>
wrote:

> You can't use functions as default values for fields in table design. The
> exception to that are a few simple system calls (which require no arguments)
[quoted text clipped - 25 lines]
> > rather than on the table. It works but it's not great.
> > still, does anyone know why that code didn't work on the table?

Reply to this Message

fredg - 26 Feb 2007 17:21 GMT

> Hello,
> I'm trying to set a default value for a column in a table to be based
[quoted text clipped - 8 lines]
>
> Thanks.

1) Change the Value and Name field names to something else.
Name and Value are both reserved Access/VBA/Jet words and should not
be used as field names.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

2) After you change the field names to something else, use the
appropriate control's defaultvalue property on the FORM.
Don't use the field's defaultvalue property in the table.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

lord.zoltar@gmail.com - 26 Feb 2007 17:39 GMT

> 1) Change the Value and Name field names to something else.
> Name and Value are both reserved Access/VBA/Jet words and should not
[quoted text clipped - 8 lines]
>
> For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html

Thanks for the warning! I'll have a look.

Reply to this Message