dlookup, mixed number and 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
 
D

Duane Hookom

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

Wim

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...
 
W

Wim

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...
 
D

Duane Hookom

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...
 
W

Wim

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...
 
D

Duane Hookom

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...
 
W

Wim

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...
 
D

Duane Hookom

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

null date 2
Complex date query 1
Selecting value from second list when date is within date range on first list 5
I´m getting nutts here! 1
Largest Date! 9
stop 0xC5 0
Fradulent Impersonator using my email addresses to post to news groups 6
Fradulent Impersonator using my email addresses to post to news groups 5

Top