Dlookup

[jean]

Hi

I have 2 tables
First one is name TblPeriods containing 3 fields "PeriodNo" (Number
format from 1 to 12) "PeriodStart" (Date format dd/mm/yy) and
"PeriodEnd" (Date format dd/mm/yy)

1 01/05/12 31/05/12
2 01/06/12 30/06/12
3 01/07/12 31/07/12

and so on. PeriodStart is the first day of the mont and PerioEnd is
the last

The PeriodNo is always 1 to 12 but 1 can represent January or May or
any other month

Second table named "Oracle" contains only 2 fields "Description" (Text
format) and "ItemDate" (Date format dd/mm/yy)

I am trying create a formula with dlookup

I want to put table "Oracle" in a query (2 fields) and create a third
one that will be a lookup of the table period

Lets say for one record the ItemDate is 15/05/12 the calculate field
shoud return 1
If the ItemDate is 01/07/12 calculate field should return 3
1 for 31/05/12
2 for 18/06/12
and so on

In text the formula shoud say
Take the date of the ItemDate and return the PeriodNo of the table
TblPeriod that match

Something like =Dlookup("PeriodNo","TblPeriods", "ItemDate >=
PeriodSart and <= PeriodEnd)

I have try many different style (with " and [ ],without them) but no
success

Thanks for helping

 

[Bob Quintal]

m:

Hi

I have 2 tables
First one is name TblPeriods containing 3 fields "PeriodNo"
(Number format from 1 to 12) "PeriodStart" (Date format dd/mm/yy)
and "PeriodEnd" (Date format dd/mm/yy)

1 01/05/12 31/05/12
2 01/06/12 30/06/12
3 01/07/12 31/07/12

and so on. PeriodStart is the first day of the mont and PerioEnd
is the last

The PeriodNo is always 1 to 12 but 1 can represent January or May
or any other month

Second table named "Oracle" contains only 2 fields "Description"
(Text format) and "ItemDate" (Date format dd/mm/yy)

I am trying create a formula with dlookup

I want to put table "Oracle" in a query (2 fields) and create a
third one that will be a lookup of the table period

Lets say for one record the ItemDate is 15/05/12 the calculate
field shoud return 1
If the ItemDate is 01/07/12 calculate field should return 3
1 for 31/05/12
2 for 18/06/12
and so on

In text the formula shoud say
Take the date of the ItemDate and return the PeriodNo of the table
TblPeriod that match

Something like =Dlookup("PeriodNo","TblPeriods", "ItemDate >=
PeriodSart and <= PeriodEnd)

I have try many different style (with " and [ ],without them) but
no success

Thanks for helping

Try:
[Itemdate] & " between [PeriodStart] and [PeriodEnd]"
as the criteria,

Note that if you want to test a field twice, you have to mention it
twice:
ItemDate >= > PeriodStart and Itemdate <= PeriodEnd

 

[jeanulrich00]

Hi I receive an error message

here is what I wrote as formula

DLookUp([PeriodNo],[TblPeriods],[Itemdate] & " between [PeriodStart] and [PeriodEnd]")

Thanks for helping

 

[Jean Ulrich]

m:

Hi

I have 2 tables
First one is name TblPeriods containing 3 fields "PeriodNo"
(Number format from 1 to 12)  "PeriodStart" (Date format dd/mm/yy)
and "PeriodEnd"  (Date format dd/mm/yy)

1  01/05/12     31/05/12
2  01/06/12     30/06/12
3  01/07/12     31/07/12

and so on.  PeriodStart is the first day of the mont and PerioEnd
is the last

The PeriodNo is always 1 to 12  but 1 can represent January or May
or any other month

Second table named "Oracle" contains only 2 fields "Description"
(Text format) and "ItemDate" (Date format  dd/mm/yy)

I am trying create a formula with dlookup

I want to put table "Oracle" in a query (2 fields) and create a
third one that will be a lookup of the table period

Lets say for one record the ItemDate is 15/05/12  the calculate
field shoud return 1
If the ItemDate is 01/07/12  calculate field should return 3
1 for 31/05/12
2 for 18/06/12
and so on

In text the formula shoud say
Take the date of the ItemDate and return the PeriodNo of the table
TblPeriod that match

Something like =Dlookup("PeriodNo","TblPeriods", "ItemDate >=
PeriodSart and <= PeriodEnd)

I have try many different style (with " and [ ],without them) but
no success

Thanks for helping

Try:
[Itemdate] & " between [PeriodStart] and [PeriodEnd]"
as the criteria,

Note that if you want to test a field twice, you have to mention it
twice:
ItemDate >= > PeriodStart and Itemdate <= PeriodEnd

--
Bob Q.
PA is y I've altered my address.- Hide quoted text -

- Show quoted text -

Hi I have try the following formula

DLookUp([PeriodNo],[TblPeriods],[Itemdate] & " between [PeriodStart]
and [PeriodEnd]")

And it is not working (error)

What I am doing wrong ?

thanks for helping

 

[John W. Vinson]

Hi I receive an error message

here is what I wrote as formula

DLookUp([PeriodNo],[TblPeriods],[Itemdate] & " between [PeriodStart] and [PeriodEnd]")

Thanks for helping

Take another look at the Help for DLookUp. The function takes three arguments;
ALL THREE of them must be text strings (either text literals in quotes, or
variables which evaluate to a text string. The first argument must be a text
string containing the name of a field; the second a text string containing the
name of a table or query; the third a text string containing an (optional) SQL
WHERE clause without the word WHERE.

Try

DLookUp("[PeriodNo]", "[tblPeriods]", "[ItemDate] BETWEEN #" & [PeriodStart] &
"# AND #" & [PeriodEnd] & "#")

The & concatenation operator stitches bits of string together into one result
string; I'm including the # date delimiter.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Jean Ulrich]

Hi I receive an error message

here is what I wrote as formula

DLookUp([PeriodNo],[TblPeriods],[Itemdate] & " between [PeriodStart] and[PeriodEnd]")

Thanks for helping

Take another look at the Help for DLookUp. The function takes three arguments;
ALL THREE of them must be text strings (either text literals in quotes, or
variables which evaluate to a text string. The first argument must be a text
string containing the name of a field; the second a text string containing the
name of a table or query; the third a text string containing an (optional) SQL
WHERE clause without the word WHERE.

Try

DLookUp("[PeriodNo]", "[tblPeriods]", "[ItemDate] BETWEEN #" & [PeriodStart] &
"# AND #" & [PeriodEnd] & "#")

The & concatenation operator stitches bits of string together into one result
string; I'm including the # date delimiter.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John

I have try your formula and it is not working

I have a hard time when it is question of Dates in criteria

Can it be the date format I use ( dd/mm/yy ) that cause the problem ?

The formula you gave don't produce error, but when I open the query,
the field is empty which is wrong !

Thanks

 

[John W. Vinson]

I have a hard time when it is question of Dates in criteria

Can it be the date format I use ( dd/mm/yy ) that cause the problem ?

It is indeed. The programmers who wrote Access were Americans and used the
American mm/dd/yyyy convention; date literals within # marks must use either
this format or the unambiguous ISO yyyy-mm-dd format. Try

DLookUp("[PeriodNo]", "[tblPeriods]", "[ItemDate] BETWEEN #" &
Format([PeriodStart],'yyyy\-mm\-dd') &
"# AND #" & Format([PeriodEnd],'yyyy\-mm\-dd') & "#")
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Quintal]

I have a hard time when it is question of Dates in criteria

Can it be the date format I use ( dd/mm/yy ) that cause the
problem ?

It is indeed. The programmers who wrote Access were Americans and
used the American mm/dd/yyyy convention; date literals within #
marks must use either this format or the unambiguous ISO
yyyy-mm-dd format. Try

DLookUp("[PeriodNo]", "[tblPeriods]", "[ItemDate] BETWEEN #" &
Format([PeriodStart],'yyyy\-mm\-dd') &
"# AND #" & Format([PeriodEnd],'yyyy\-mm\-dd') & "#")

Actually, the problem is that [itemdate] is the field from the local
table that needs to be between the # delimiters and the other two
field [periodstart and periodend should be as I wrote them
Try

"#" & [Itemdate] & "# between [PeriodStart] and [PeriodEnd]"


You should not need the format functions if the dates are stored in a
table.