dlookup, mixed number and date

  • Thread starter Thread starter Wim
  • Start date Start date
W

Wim

Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31 dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & " and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim
 
Does this work if you hard-code dates into the where clause? Are both the
date fields populated with valid dates?
 
Duane,

Good idea, I'll do it immediately.

Wim
-----Original Message-----
Does this work if you hard-code dates into the where clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31 dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & " and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim
Click to expand...


.
Click to expand...
 
Duane,

Hard-coded dates work fine, and yes, the date fields have
valid dates.
It is as if the reference to the field is "invisible" for
dlookup. I now see an error like : invalid date : # # (a
few spaces).

Wim
-----Original Message-----
Does this work if you hard-code dates into the where clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31 dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & " and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim
Click to expand...


.
Click to expand...
 
I would generally expect to see the field(s) from Ref_Prices inside the
quotes rather than your expression that has [InvoiceDate] inside the qutoes.
It might help to see your full SQL of the query and some table and field
names.

--
Duane Hookom
MS Access MVP


Wim said:
Duane,

Hard-coded dates work fine, and yes, the date fields have
valid dates.
It is as if the reference to the field is "invisible" for
dlookup. I now see an error like : invalid date : # # (a
few spaces).

Wim
-----Original Message-----
Does this work if you hard-code dates into the where clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31 dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & " and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim
Click to expand...


.
Click to expand...
Click to expand...
 
M1: CCur(DLookUp("[Price]";"Ref_table Prices";"IdPrice = "
& 12 & " and [BeginDateCourse] between #" & [Begindate]
& "and " & [Enddate]))

Ref_table Prices :
IdPrice Course BeginDate EndDate Price
11 Math 01/01/2004 31/01/2004 10
12 Math 01/02/2004 31/03/2004 12
13 Math 01/04/2004 30/06/2004 15

table CourseInscriptions :

IdCourse IdStudent BeginDateCourse Meals
Math 1 01/01/2004 Y
Math 2 01/03/2004 Y


The prices for courses vary in time, so it must be
possible to determine the price even after a long period.
I know I could use dlast(), but then this will only give
me correct prices for the current period, not for the past.

Wim
-----Original Message-----
I would generally expect to see the field(s) from Ref_Prices inside the
quotes rather than your expression that has [InvoiceDate] inside the qutoes.
It might help to see your full SQL of the query and some table and field
names.

--
Duane Hookom
MS Access MVP


Duane,

Hard-coded dates work fine, and yes, the date fields have
valid dates.
It is as if the reference to the field is "invisible" for
dlookup. I now see an error like : invalid date : # # (a
few spaces).

Wim
-----Original Message-----
Does this work if you hard-code dates into the where clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31 dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & " and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim



.
Click to expand...
Click to expand...


.
Click to expand...
 
As I stated previously, I think you had the wrong fields inside the ""s:
M1: CCur(DLookUp("[Price]";"[Ref_table Prices]";"IdPrice = 12 and #" &
[BeginDateCourse] & "# between [Begindate] and [Enddate]"))


--
Duane Hookom
MS Access MVP


Wim said:
M1: CCur(DLookUp("[Price]";"Ref_table Prices";"IdPrice = "
& 12 & " and [BeginDateCourse] between #" & [Begindate]
& "and " & [Enddate]))

Ref_table Prices :
IdPrice Course BeginDate EndDate Price
11 Math 01/01/2004 31/01/2004 10
12 Math 01/02/2004 31/03/2004 12
13 Math 01/04/2004 30/06/2004 15

table CourseInscriptions :

IdCourse IdStudent BeginDateCourse Meals
Math 1 01/01/2004 Y
Math 2 01/03/2004 Y


The prices for courses vary in time, so it must be
possible to determine the price even after a long period.
I know I could use dlast(), but then this will only give
me correct prices for the current period, not for the past.

Wim
-----Original Message-----
I would generally expect to see the field(s) from Ref_Prices inside the
quotes rather than your expression that has [InvoiceDate] inside the qutoes.
It might help to see your full SQL of the query and some table and field
names.

--
Duane Hookom
MS Access MVP


Duane,

Hard-coded dates work fine, and yes, the date fields have
valid dates.
It is as if the reference to the field is "invisible" for
dlookup. I now see an error like : invalid date : # # (a
few spaces).

Wim

-----Original Message-----
Does this work if you hard-code dates into the where
clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


message
Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31 dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & " and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim



.
Click to expand...


.
Click to expand...
Click to expand...
 
You mean something like :

= 12 & " and #" & [BeginDate] & "# >= [BeginDateCourse]"))

?

-----Original Message-----
As I stated previously, I think you had the wrong fields inside the ""s:
M1: CCur(DLookUp("[Price]";"[Ref_table Prices]";"IdPrice = 12 and #" &
[BeginDateCourse] & "# between [Begindate] and [Enddate]"))


--
Duane Hookom
MS Access MVP


M1: CCur(DLookUp("[Price]";"Ref_table Prices";"IdPrice = "
& 12 & " and [BeginDateCourse] between #" & [Begindate]
& "and " & [Enddate]))

Ref_table Prices :
IdPrice Course BeginDate EndDate Price
11 Math 01/01/2004 31/01/2004 10
12 Math 01/02/2004 31/03/2004 12
13 Math 01/04/2004 30/06/2004 15

table CourseInscriptions :

IdCourse IdStudent BeginDateCourse Meals
Math 1 01/01/2004 Y
Math 2 01/03/2004 Y


The prices for courses vary in time, so it must be
possible to determine the price even after a long period.
I know I could use dlast(), but then this will only give
me correct prices for the current period, not for the past.

Wim
-----Original Message-----
I would generally expect to see the field(s) from Ref_Prices inside the
quotes rather than your expression that has
Click to expand...
[InvoiceDate]
inside the qutoes.
It might help to see your full SQL of the query and
Click to expand...
some
table and field
names.

--
Duane Hookom
MS Access MVP


Duane,

Hard-coded dates work fine, and yes, the date fields have
valid dates.
It is as if the reference to the field is "invisible" for
dlookup. I now see an error like : invalid date :
Click to expand...
# #
(a
few spaces).

Wim

-----Original Message-----
Does this work if you hard-code dates into the where
clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


message
Hi all,

I want to look up a price for an item and store it in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till
Click to expand...
31
dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12
Click to expand...
& "
and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Close
= 12 & " and [BeginDate] <=#" & [BeginDateCourse] & "# EndDate >=# &
[BeginDateCourse] & "#"))

--
Duane Hookom
MS Access MVP


Wim said:
You mean something like :

= 12 & " and #" & [BeginDate] & "# >= [BeginDateCourse]"))

?

-----Original Message-----
As I stated previously, I think you had the wrong fields inside the ""s:
M1: CCur(DLookUp("[Price]";"[Ref_table Prices]";"IdPrice = 12 and #" &
[BeginDateCourse] & "# between [Begindate] and [Enddate]"))


--
Duane Hookom
MS Access MVP


M1: CCur(DLookUp("[Price]";"Ref_table Prices";"IdPrice = "
& 12 & " and [BeginDateCourse] between #" & [Begindate]
& "and " & [Enddate]))

Ref_table Prices :
IdPrice Course BeginDate EndDate Price
11 Math 01/01/2004 31/01/2004 10
12 Math 01/02/2004 31/03/2004 12
13 Math 01/04/2004 30/06/2004 15

table CourseInscriptions :

IdCourse IdStudent BeginDateCourse Meals
Math 1 01/01/2004 Y
Math 2 01/03/2004 Y


The prices for courses vary in time, so it must be
possible to determine the price even after a long period.
I know I could use dlast(), but then this will only give
me correct prices for the current period, not for the past.

Wim

-----Original Message-----
I would generally expect to see the field(s) from
Ref_Prices inside the
quotes rather than your expression that has [InvoiceDate]
inside the qutoes.
It might help to see your full SQL of the query and some
table and field
names.

--
Duane Hookom
MS Access MVP


message
Duane,

Hard-coded dates work fine, and yes, the date fields
have
valid dates.
It is as if the reference to the field is "invisible"
for
dlookup. I now see an error like : invalid date : # #
(a
few spaces).

Wim

-----Original Message-----
Does this work if you hard-code dates into the where
clause? Are both the
date fields populated with valid dates?

--
Duane Hookom
MS Access MVP


message
Hi all,

I want to look up a price for an item and store it
in an
field InvoiceDate in a table called Invoices.
This price was valid from, say, 01 jan 2003 till 31
dec
2003. The prices are in a table : Ref_Prices. This
particular item's key is, say, : 12.
So : PriceId = 12, StartDate=01/01/2003,
EndDate=12/31/2003.

I tried :

dlookup("Price"; "Ref_Prices"; "PriceId = " & 12 & "
and
InvoiceDate >= #" & [Ref_Prices]![StartDate] & "# and
InvoiceDate <= #" & [Ref_Prices]![EndDate] & "#")

But I get invalid use of null error.

Has anyone a clue of the correct syntax ?

Thanks in advance,

Wim



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top