DLookup

[tgavin]

I have a situation where one of my database clients changed their cost per
copy pricing from a set amount based on the series of machine to gradiated
pricing per copy per series. I put a DLookup in the query but it slowed
things down to an unbearable rate...it was already slow due to several
subforms and massive calculations.

I created a curCPC field to hold the cost per copy for the particular
proposal and want to populate it from an after update of the number of
copies, but my code keeps returning an type mismatch error. All my data types
are either currancy or long integer and nothing is null, so I know the types
are right so I figure I have something wrong with the code. I am not sure if
the code is totally right anyway because it needs to find the intCopiesMonth
in between the intBlackMin and the intBlackMax but I wasn't sure how to write
that much and thought I would see what was returned with this...except all
it's returning is an error message of type mismatch.

This is what I wrote:
Dim curCPC As Currency

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", "[intBlackMin] =" &
Me.[intCopiesMonth] And "[intSeriesID] = " & Me.Series)

Any help would be greatly appreciated.

Thanks
Terri

 

[Daniel Pineault]

For starters you have improper syntax. If you look at the help file example

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
& Forms!Shippers!ShipperID)

You'll notice that the 2nd variable (the domain) should not be in brackets.
Also, you are missing a '&" to properly concatenate your criteria string. So
try somthing more like.

curCPC = DLookup("[curBlackCopy]", "tblCopyPrice", "[intBlackMin] =" &
Me.[intCopiesMonth] & " And [intSeriesID] = " & Me.Series)

 

[Daniel Pineault]

Also depending on your data types, you might need to enclose your criteria in
's.

curCPC = DLookup("[curBlackCopy]", "tblCopyPrice", "[intBlackMin] ='" &
Me.[intCopiesMonth] & "' And [intSeriesID] = '" & Me.Series & "'")


--
Hope this helps,

Daniel Pineault

For starters you have improper syntax. If you look at the help file example

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
& Forms!Shippers!ShipperID)

You'll notice that the 2nd variable (the domain) should not be in brackets.
Also, you are missing a '&" to properly concatenate your criteria string. So
try somthing more like.

curCPC = DLookup("[curBlackCopy]", "tblCopyPrice", "[intBlackMin] =" &
Me.[intCopiesMonth] & " And [intSeriesID] = " & Me.Series)
--
Hope this helps,

Daniel Pineault

I have a situation where one of my database clients changed their cost per
copy pricing from a set amount based on the series of machine to gradiated
pricing per copy per series. I put a DLookup in the query but it slowed
things down to an unbearable rate...it was already slow due to several
subforms and massive calculations.

I created a curCPC field to hold the cost per copy for the particular
proposal and want to populate it from an after update of the number of
copies, but my code keeps returning an type mismatch error. All my data types
are either currancy or long integer and nothing is null, so I know the types
are right so I figure I have something wrong with the code. I am not sure if
the code is totally right anyway because it needs to find the intCopiesMonth
in between the intBlackMin and the intBlackMax but I wasn't sure how to write
that much and thought I would see what was returned with this...except all
it's returning is an error message of type mismatch.

This is what I wrote:
Dim curCPC As Currency

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", "[intBlackMin] =" &
Me.[intCopiesMonth] And "[intSeriesID] = " & Me.Series)

Any help would be greatly appreciated.

Thanks
Terri

 

[tgavin]

Daniel, thanks. It solved the error message but I am not getting any answer.
Any ideas why it may not work?

For starters you have improper syntax. If you look at the help file example

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
& Forms!Shippers!ShipperID)

You'll notice that the 2nd variable (the domain) should not be in brackets.
Also, you are missing a '&" to properly concatenate your criteria string. So
try somthing more like.

curCPC = DLookup("[curBlackCopy]", "tblCopyPrice", "[intBlackMin] =" &
Me.[intCopiesMonth] & " And [intSeriesID] = " & Me.Series)
--
Hope this helps,

Daniel Pineault

I have a situation where one of my database clients changed their cost per
copy pricing from a set amount based on the series of machine to gradiated
pricing per copy per series. I put a DLookup in the query but it slowed
things down to an unbearable rate...it was already slow due to several
subforms and massive calculations.

I created a curCPC field to hold the cost per copy for the particular
proposal and want to populate it from an after update of the number of
copies, but my code keeps returning an type mismatch error. All my data types
are either currancy or long integer and nothing is null, so I know the types
are right so I figure I have something wrong with the code. I am not sure if
the code is totally right anyway because it needs to find the intCopiesMonth
in between the intBlackMin and the intBlackMax but I wasn't sure how to write
that much and thought I would see what was returned with this...except all
it's returning is an error message of type mismatch.

This is what I wrote:
Dim curCPC As Currency

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", "[intBlackMin] =" &
Me.[intCopiesMonth] And "[intSeriesID] = " & Me.Series)

Any help would be greatly appreciated.

Thanks
Terri

 

[Douglas J. Steele]

You'll notice that the 2nd variable (the domain) should not be in
brackets.

There's no problem with the domain name being in square brackets (or with
the field name not being in square brackets). The square brackets are
optional (in both cases) if the names do not include spaces, and are
mandatory (in both cases) if the names do include spaces.

 

[tgavin]

The second idea returned an error message so I will stick with the first but
any ideas why it didn't give me an answer?

Also depending on your data types, you might need to enclose your criteria in
's.

curCPC = DLookup("[curBlackCopy]", "tblCopyPrice", "[intBlackMin] ='" &
Me.[intCopiesMonth] & "' And [intSeriesID] = '" & Me.Series & "'")


--
Hope this helps,

Daniel Pineault

For starters you have improper syntax. If you look at the help file example

Dim varX As Variant
varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
& Forms!Shippers!ShipperID)

You'll notice that the 2nd variable (the domain) should not be in brackets.
Also, you are missing a '&" to properly concatenate your criteria string. So
try somthing more like.

curCPC = DLookup("[curBlackCopy]", "tblCopyPrice", "[intBlackMin] =" &
Me.[intCopiesMonth] & " And [intSeriesID] = " & Me.Series)
--
Hope this helps,

Daniel Pineault

I have a situation where one of my database clients changed their cost per
copy pricing from a set amount based on the series of machine to gradiated
pricing per copy per series. I put a DLookup in the query but it slowed
things down to an unbearable rate...it was already slow due to several
subforms and massive calculations.

I created a curCPC field to hold the cost per copy for the particular
proposal and want to populate it from an after update of the number of
copies, but my code keeps returning an type mismatch error. All my data types
are either currancy or long integer and nothing is null, so I know the types
are right so I figure I have something wrong with the code. I am not sure if
the code is totally right anyway because it needs to find the intCopiesMonth
in between the intBlackMin and the intBlackMax but I wasn't sure how to write
that much and thought I would see what was returned with this...except all
it's returning is an error message of type mismatch.

This is what I wrote:
Dim curCPC As Currency

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", "[intBlackMin] =" &
Me.[intCopiesMonth] And "[intSeriesID] = " & Me.Series)

Any help would be greatly appreciated.

Thanks
Terri

 

[tgavin]

Doug, Daniel's first email got rid of my error message but even though there
is not error, there is no answer. Any ideals?

Thanks.
Terri

 

[Douglas J. Steele]

You said you need "to find the intCopiesMonth in between the intBlackMin and
the intBlackMax", and your lookup isn't doing that. Try:

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", _
"[intBlackMin] <=" & Me.[intCopiesMonth] & _
" And [intBlackMax] >=" & Me.[intCopiesMonth] & _
" And [intSeriesID] = " & Me.Series)

 

[tgavin]

Doug, thanks. It should work but nothing happens, no error message but also
the curCPC stays at 0. I have checked all my fields to make sure they are
right and don't know where else to look. I even created another form that is
simpler to test with and nothing.

Any ideas where or what I should look at>

Thanks, Terri

You said you need "to find the intCopiesMonth in between the intBlackMin and
the intBlackMax", and your lookup isn't doing that. Try:

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", _
"[intBlackMin] <=" & Me.[intCopiesMonth] & _
" And [intBlackMax] >=" & Me.[intCopiesMonth] & _
" And [intSeriesID] = " & Me.Series)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, Daniel's first email got rid of my error message but even though
there
is not error, there is no answer. Any ideals?

Thanks.
Terri

 

[Douglas J. Steele]

Pretty difficult to say, since you haven't given any details of
tblCopyPrice.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug, thanks. It should work but nothing happens, no error message but
also
the curCPC stays at 0. I have checked all my fields to make sure they are
right and don't know where else to look. I even created another form that
is
simpler to test with and nothing.

Any ideas where or what I should look at>

Thanks, Terri

You said you need "to find the intCopiesMonth in between the intBlackMin
and
the intBlackMax", and your lookup isn't doing that. Try:

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", _
"[intBlackMin] <=" & Me.[intCopiesMonth] & _
" And [intBlackMax] >=" & Me.[intCopiesMonth] & _
" And [intSeriesID] = " & Me.Series)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, Daniel's first email got rid of my error message but even though
there
is not error, there is no answer. Any ideals?

Thanks.
Terri

:

message
You'll notice that the 2nd variable (the domain) should not be in
brackets.

There's no problem with the domain name being in square brackets (or
with
the field name not being in square brackets). The square brackets are
optional (in both cases) if the names do not include spaces, and are
mandatory (in both cases) if the names do include spaces.

 

[tgavin]

This is the layout for tblCopyPrice
CopyID AutoNumber
intSeriesID Number Long Integer
intBlackMin Number Long Integer
intBlackMax Number Long Integer
intColorMin Number Long Integer
intColorMax Number Long Integer
curBlackCopy Currency
curColorCopy Currency

The fields that correspond in the table where I am trying to get the cost to
post to are:
intID Number Long Integer
intCopiesMonth Number Long Integer
curCPC Currency
intColorCopiesMonth Number Long Integer
curColorCPC Currency

intID links to tblEquipment which has SeriesID

I hope this helps. Thanks for your help.

 

[Douglas J. Steele]

Okay,

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", _
"[intBlackMin] <=" & Me.[intCopiesMonth] & _
" And [intBlackMax] >=" & Me.[intCopiesMonth] & _
" And [intSeriesID] = " & Me.Series)

should work, provided the correct values are in the controls intCopiesMonth
and Series on your form.

Realistically, though, there shouldn't be any need to use DLookup. Just
write a query that joins the tables. You'll have to work directly with the
SQL, though, as Access doesn't let you create non equijoins through the
graphical query builder.



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This is the layout for tblCopyPrice
CopyID AutoNumber
intSeriesID Number Long Integer
intBlackMin Number Long Integer
intBlackMax Number Long Integer
intColorMin Number Long Integer
intColorMax Number Long Integer
curBlackCopy Currency
curColorCopy Currency

The fields that correspond in the table where I am trying to get the cost
to
post to are:
intID Number Long Integer
intCopiesMonth Number Long Integer
curCPC Currency
intColorCopiesMonth Number Long Integer
curColorCPC Currency

intID links to tblEquipment which has SeriesID

I hope this helps. Thanks for your help.

I have a situation where one of my database clients changed their cost
per
copy pricing from a set amount based on the series of machine to
gradiated
pricing per copy per series. I put a DLookup in the query but it slowed
things down to an unbearable rate...it was already slow due to several
subforms and massive calculations.

I created a curCPC field to hold the cost per copy for the particular
proposal and want to populate it from an after update of the number of
copies, but my code keeps returning an type mismatch error. All my data
types
are either currancy or long integer and nothing is null, so I know the
types
are right so I figure I have something wrong with the code. I am not sure
if
the code is totally right anyway because it needs to find the
intCopiesMonth
in between the intBlackMin and the intBlackMax but I wasn't sure how to
write
that much and thought I would see what was returned with this...except
all
it's returning is an error message of type mismatch.

This is what I wrote:
Dim curCPC As Currency

curCPC = DLookup("[curBlackCopy]", "[tblCopyPrice]", "[intBlackMin] =" &
Me.[intCopiesMonth] And "[intSeriesID] = " & Me.Series)

Any help would be greatly appreciated.

Thanks
Terri