Dlookup in a query

[Jay Cook]

Hi

I have a dlookup statement inside a query and I am not getting the result i
need, I think I need to put in # symbols but can't figure out the correct
syntax, any help would be appricated

FISCYR: DLookUp("YR","tblFiscalPeriod","Operations.Date" Between "Startdate"
And "EndDate")

Thanks

 

[John Vinson]

Hi

I have a dlookup statement inside a query and I am not getting the result i
need, I think I need to put in # symbols but can't figure out the correct
syntax, any help would be appricated

FISCYR: DLookUp("YR","tblFiscalPeriod","Operations.Date" Between "Startdate"
And "EndDate")

Thanks

First off, don't use the fieldname Date if you can avoid it. It's a
reserved word (for the builtin Date() function which returns the
system clock date). If you must use it, always enclose it in square
brackets. Even worse, fieldnames should *never* contain periods, since
a period is the delimiter between tablenames and fieldnames in
queries; Operations.Date means "the field named Date in the table
named Operations". Square brackets *might* avoid problems but renaming
the field (say to Operations_Date) would be much better!

Thirdly, why use a DLookUp inside a query, rather than just using
criteria in the query?

Those concerns aside: you need to have the third parameter end up as a
text string which is a valid SQL WHERE clause, without the word Where.
Guessing that tblFiscalPeriod has fields named StartDate and EndDate,
and you want to return records where the field [Date] in the outer
query (based on the table Operations I presume), you should be able to
use

DLookUp("YR", "tblFiscalPeriod", "[StartDate] <= #" & [Date] & "# AND
[EndDate] >= #" & [Date] & "#")

You may be able to avoid the tblFiscalPeriod altogether if your fiscal
year begins in (say) July - DatePart("yyyy", DateAdd("m", 7, [Date]))
will return 2004 for July 1 2003 through June 30 2004 and so on.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Correct syntax for DLookup():

DLookup("<column>","<data source>","<criteria>")

You can't have another table reference in the criteria expression. You
must use a column that is in the data source.

Don't use the word "Date" as a column name 'cuz it is a reserved word.
If you insist on using "Date" enclose it in square brackets: [Date].

If Startdate and EndDate are parameters declare them as Date data types
then they can be used w/o the # delimiters.

DLookup("YR","tblFiscalPeriod","[Date] Between [Startdate]
And [EndDate]")

If that doesn't work try this:

DLookup("YR","tblFiscalPeriod","[Date] Between " & [Startdate] & " And "
& [EndDate])

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQbYz0oechKqOuFEgEQJKrACcC1ij67pjlC+EI/OS1seWXeaWV9cAn2eL
eJ+GQlQiNJFghOnN7C9dPI6e
=24po
-----END PGP SIGNATURE-----

 

[Jay Cook]

Thanks Guys, this worked perfect, I wish I could find a good article that
explains the sytax a little better for me.

you were correct on the field name date, it did come from the operations
table - I should give some more detail next time - but you guys got

again Thanks

Hi

I have a dlookup statement inside a query and I am not getting the result i
need, I think I need to put in # symbols but can't figure out the correct
syntax, any help would be appricated

FISCYR: DLookUp("YR","tblFiscalPeriod","Operations.Date" Between "Startdate"
And "EndDate")

Thanks

First off, don't use the fieldname Date if you can avoid it. It's a
reserved word (for the builtin Date() function which returns the
system clock date). If you must use it, always enclose it in square
brackets. Even worse, fieldnames should *never* contain periods, since
a period is the delimiter between tablenames and fieldnames in
queries; Operations.Date means "the field named Date in the table
named Operations". Square brackets *might* avoid problems but renaming
the field (say to Operations_Date) would be much better!

Thirdly, why use a DLookUp inside a query, rather than just using
criteria in the query?

Those concerns aside: you need to have the third parameter end up as a
text string which is a valid SQL WHERE clause, without the word Where.
Guessing that tblFiscalPeriod has fields named StartDate and EndDate,
and you want to return records where the field [Date] in the outer
query (based on the table Operations I presume), you should be able to
use

DLookUp("YR", "tblFiscalPeriod", "[StartDate] <= #" & [Date] & "# AND
[EndDate] >= #" & [Date] & "#")

You may be able to avoid the tblFiscalPeriod altogether if your fiscal
year begins in (say) July - DatePart("yyyy", DateAdd("m", 7, [Date]))
will return 2004 for July 1 2003 through June 30 2004 and so on.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks Guys, this worked perfect, I wish I could find a good article that
explains the sytax a little better for me.

The syntax makes sense, especially if you are acquainted with the SQL
view of a query. The third argument to any domain function is simply a
text string which is a valid SQL WHERE clause, without the word WHERE.
In your example you want (apparently) to have the field [DATE] from
the outer query to fall between the table fields [STARTDATE] and
[ENDDATE]. A Where clause to do this would be

WHERE #12/8/2004# BETWEEN [STARTDATE] AND [ENDDATE]

or you could use the greater than/less than expression in my previous
post. In the DLookUp call you just need to piece this string together
from its components. In this case, [DATE] is a variable, and the rest
is constant, so you could have written

[DATE] & " BETWEEN [STARTDATE] AND [ENDDATE]"

to concatenate the variable date to the non-varying portion of the
clause.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps