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

jean <(E-Mail Removed)> wrote in
news:05f639a3-90fe-418c-a8ff-(E-Mail Removed)
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.

Hi I receive an error message

here is what I wrote as formula

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

Thanks for helping

On Jan 31, 6:51*pm, Bob Quintal <rquin...@sPAmpatico.ca> wrote:
> jean <jeanulric...@gmail.com> wrote innews:05f639a3-90fe-418c-a8ff-(E-Mail Removed)
> 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

On Wed, 1 Feb 2012 06:31:34 -0800 (PST), (E-Mail Removed) wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Feb 1, 1:25*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Wed, 1 Feb 2012 06:31:34 -0800 (PST), jeanulric...@gmail.com wrote:
> >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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../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

On Wed, 1 Feb 2012 12:54:50 -0800 (PST), Jean Ulrich <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On Wed, 1 Feb 2012 12:54:50 -0800 (PST), Jean Ulrich
> <(E-Mail Removed)> wrote:
>
>>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.

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