DLookup

[Jesster79 via AccessMonster.com]

I am trying to us the DLookup funtion in a text box (within a report) to
return information from a query. I want the criteria to be twofold, Year and
SiteID. I keep getting an error.

Here is what I am trying:

=DLookup("[New Redds]","Annual Sites","[Year]=2008 and {SiteID]=101")

Any suggestions?

 

[Jeff Boyce]

Take a look at Access HELP for the correct syntax and examples.

I believe you need to keep [Year]= within the quotes, and the and [SiteID]=
within quotes.

By the way, are you sure it now says '{SiteID]...'? That's a typo at the
beginning of the fieldname.

By the way #2, a field named "Year" may cause confusion... I believe that's
a reserved word in Access, so what you mean may not match what Access thinks
it means.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

It would help to know what the error is...

What are the data types of the Year and SiteID fields? If either is Text,
you need quotes around the values. For instance, if SiteID is a text field,
you'd need

=DLookup("[New Redds]","Annual Sites","[Year]=2008 and [SiteID]='101'")

 

[Jesster79 via AccessMonster.com]

It would help to know what the error is...

What are the data types of the Year and SiteID fields? If either is Text,
you need quotes around the values. For instance, if SiteID is a text field,
you'd need

=DLookup("[New Redds]","Annual Sites","[Year]=2008 and [SiteID]='101'")

I am trying to us the DLookup funtion in a text box (within a report) to
return information from a query. I want the criteria to be twofold, Year

[quoted text clipped - 6 lines]

Any suggestions?

Douglas,

The error shows up in the print view as #error. The Year field is a
Date/Time and the SiteID is a Number field. I tried putting quotes around
them anyways and it still won't work.

Jesse

 

[Jeff Boyce]

"How" depends on "what" ... please post back a copy of what you used (that
didn't work).

Regards

Jeff Boyce
Microsoft Office/Access MVP

It would help to know what the error is...

What are the data types of the Year and SiteID fields? If either is Text,
you need quotes around the values. For instance, if SiteID is a text
field,
you'd need

=DLookup("[New Redds]","Annual Sites","[Year]=2008 and [SiteID]='101'")

I am trying to us the DLookup funtion in a text box (within a report) to
return information from a query. I want the criteria to be twofold,
Year

[quoted text clipped - 6 lines]

Any suggestions?

Douglas,

The error shows up in the print view as #error. The Year field is a
Date/Time and the SiteID is a Number field. I tried putting quotes around
them anyways and it still won't work.

Jesse

 

[Douglas J. Steele]

It would help to know what the error is...

What are the data types of the Year and SiteID fields? If either is Text,
you need quotes around the values. For instance, if SiteID is a text
field,
you'd need

=DLookup("[New Redds]","Annual Sites","[Year]=2008 and [SiteID]='101'")

Douglas,

The error shows up in the print view as #error. The Year field is a
Date/Time and the SiteID is a Number field. I tried putting quotes around
them anyways and it still won't work.

If the Year field is a Date/Time field, then 2008 isn't a valid value for it
(or rather, it'll give you something very different than what you expect: a
date of 30 June, 1905). Date/Time fields can only be compared to dates, and
2008 isn't a date.

You may need

=DLookup("[New Redds]","[Annual Sites]","([Year] BETWEEN #1/1/2008# AND
#12/31/2008#) and [SiteID]=101")

Incidentally, Year is a bad choice for a field name, as it's a reserved
word. For a comprehensive list of names to avoid (plus a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

 

[Jesster79 via AccessMonster.com]

[quoted text clipped - 10 lines]

Date/Time and the SiteID is a Number field. I tried putting quotes around
them anyways and it still won't work.

If the Year field is a Date/Time field, then 2008 isn't a valid value for it
(or rather, it'll give you something very different than what you expect: a
date of 30 June, 1905). Date/Time fields can only be compared to dates, and
2008 isn't a date.

You may need

=DLookup("[New Redds]","[Annual Sites]","([Year] BETWEEN #1/1/2008# AND
#12/31/2008#) and [SiteID]=101")

Incidentally, Year is a bad choice for a field name, as it's a reserved
word. For a comprehensive list of names to avoid (plus a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

Douglas,

Thanks you so much, it works great! Should I attempt to change the field
name of 'Year' to something else, or should I leave it now that it is working.
Thanks for your help!

Jesse

 

[Douglas J. Steele]

Thanks you so much, it works great! Should I attempt to change the field
name of 'Year' to something else, or should I leave it now that it is
working.
Thanks for your help!

My recommendation would be to rename the field.

There are third party tools that can help you find all usages of a name that
need to be changed. I'm afraid I'm not in my office, so I don't have access
to my usual references, but two of the tools to look for are Find & Replace
and Speed Ferret.