Problem using Dlookup

[Guest]

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

 

[Guest]

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in the tables
relate.

--
Duane Hookom
Microsoft Access MVP

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

:

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

Okay,

tblProcessData contains:

Processed_Country - Text
Processed_Supplier - Text
Dispatch_Method - Text
Item_WeightGMS (it was pointed out with brackets, the sql could be
confusing) - Long Integer

Processed_Country will contain the name of the destination country,
Processed_Supplier will contain the name of the company who will be mailing
the product,
Dispatch_Method contains the service this item will be sent and
Item_WeightGMS will contain the weight of the item.

qryCountryandSuppliers contains

CountryName - Text
Supplier - Text
DispatchMethodName - Text
WeightBandStart - Long Integer
WeightBandEnd - Long Integer

How values relate:

Processed_Country = CountryName - Text
Processed_Supplier = Supplier - Text
Dispatch_Method = DispatchMethodName - Text
Item_WeightGMS will fall in between WeightBandStart and WeightBandEnd

By the way, thank you for your on going interest in this Duane, I really
appreciate it.

John

You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in the tables
relate.

--
Duane Hookom
Microsoft Access MVP

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP


:

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

:

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

To get the Supplier, you would use an expression like:
DLookUp("Supplier","qryCountryandSuppliers",[Item_Weightgms] & " Between
WeightBandStart and WeightBandEnd AND [CountryName] ="""
& [Processed_Country] & """ AND DispatchMethodName=""" & [Dispatch_Method] &
"""")

--
Duane Hookom
Microsoft Access MVP

Okay,

tblProcessData contains:

Processed_Country - Text
Processed_Supplier - Text
Dispatch_Method - Text
Item_WeightGMS (it was pointed out with brackets, the sql could be
confusing) - Long Integer

Processed_Country will contain the name of the destination country,
Processed_Supplier will contain the name of the company who will be mailing
the product,
Dispatch_Method contains the service this item will be sent and
Item_WeightGMS will contain the weight of the item.

qryCountryandSuppliers contains

CountryName - Text
Supplier - Text
DispatchMethodName - Text
WeightBandStart - Long Integer
WeightBandEnd - Long Integer

How values relate:

Processed_Country = CountryName - Text
Processed_Supplier = Supplier - Text
Dispatch_Method = DispatchMethodName - Text
Item_WeightGMS will fall in between WeightBandStart and WeightBandEnd

By the way, thank you for your on going interest in this Duane, I really
appreciate it.

John

You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in the tables
relate.

--
Duane Hookom
Microsoft Access MVP

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.

:

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP


:

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

:

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[Guest]

Hey Duane,

Thanks for sorting this out for me; I'll take a look at this work next
Tuesday.

Much appreciated,

John

To get the Supplier, you would use an expression like:
DLookUp("Supplier","qryCountryandSuppliers",[Item_Weightgms] & " Between
WeightBandStart and WeightBandEnd AND [CountryName] ="""
& [Processed_Country] & """ AND DispatchMethodName=""" & [Dispatch_Method] &
"""")

--
Duane Hookom
Microsoft Access MVP

Okay,

tblProcessData contains:

Processed_Country - Text
Processed_Supplier - Text
Dispatch_Method - Text
Item_WeightGMS (it was pointed out with brackets, the sql could be
confusing) - Long Integer

Processed_Country will contain the name of the destination country,
Processed_Supplier will contain the name of the company who will be mailing
the product,
Dispatch_Method contains the service this item will be sent and
Item_WeightGMS will contain the weight of the item.

qryCountryandSuppliers contains

CountryName - Text
Supplier - Text
DispatchMethodName - Text
WeightBandStart - Long Integer
WeightBandEnd - Long Integer

How values relate:

Processed_Country = CountryName - Text
Processed_Supplier = Supplier - Text
Dispatch_Method = DispatchMethodName - Text
Item_WeightGMS will fall in between WeightBandStart and WeightBandEnd

By the way, thank you for your on going interest in this Duane, I really
appreciate it.

John

You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in the tables
relate.

--
Duane Hookom
Microsoft Access MVP


:

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.

:

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP


:

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

:

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?

 

[wphx]

One way you might be able to debug the dlookup statement is to develop first
in a query - you can interactively test and try out different criteria. When
you have the correct result/record appearing in the query, you could look at
its SQL statement and see the conditions you have generated (in the where
statement in the SQL).

It might make more sense when you are visualising it in the query grid.

Hey Duane,

Thanks for sorting this out for me; I'll take a look at this work next
Tuesday.

Much appreciated,

John

To get the Supplier, you would use an expression like:
DLookUp("Supplier","qryCountryandSuppliers",[Item_Weightgms] & " Between
WeightBandStart and WeightBandEnd AND [CountryName] ="""
& [Processed_Country] & """ AND DispatchMethodName=""" &
[Dispatch_Method] &
"""")

--
Duane Hookom
Microsoft Access MVP

Okay,

tblProcessData contains:

Processed_Country - Text
Processed_Supplier - Text
Dispatch_Method - Text
Item_WeightGMS (it was pointed out with brackets, the sql could be
confusing) - Long Integer

Processed_Country will contain the name of the destination country,
Processed_Supplier will contain the name of the company who will be
mailing
the product,
Dispatch_Method contains the service this item will be sent and
Item_WeightGMS will contain the weight of the item.

qryCountryandSuppliers contains

CountryName - Text
Supplier - Text
DispatchMethodName - Text
WeightBandStart - Long Integer
WeightBandEnd - Long Integer

How values relate:

Processed_Country = CountryName - Text
Processed_Supplier = Supplier - Text
Dispatch_Method = DispatchMethodName - Text
Item_WeightGMS will fall in between WeightBandStart and WeightBandEnd

By the way, thank you for your on going interest in this Duane, I
really
appreciate it.

John

:

You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in
the tables
relate.

--
Duane Hookom
Microsoft Access MVP


:

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which
we would
send a particular product to. Depending on where they are (along
with weight
and service) will dictate which supplier will be sending this item.

:

I don't think you have described enough about the "customer data"
to allow us
to tell you how to create a dlookup of the other table/query.
Apparently you
want the DLookup() to return the company. Your earlier attempts
tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP


:

The way the supplier is selected is dependant on country,
weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece
goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams,
it could go
via company #3. These companies could change again if it goes
'economy'
speed

I have a table that contains weight band 'from' and 'to' values
which change
dependant on who is the cheapest to that particular
destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 -
priority -
Company #1
Greece - 101 - 800 -
riority - Company
#2
Greece - 801 - 2000 -
rity -Company #3

When I run the customer data which contains all the address
destinations, it
will reference this table in order to pick the relevant
company.

I hope this makes sense; if there's anything else i can clarify
let me know!

John

:

You might want to describe how you want to select a Supplier.
What is this
selection based on and where do the selection criteria come
from? We know
about tblProcessData.Item_Weight(gms) and
qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I
apologise for sounding
like an idiot - but how would I add additional criteria in?
I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the
weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND
[CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am
experiencing the same
problem. Is there a way of doing a 'criteria' to
ensure that I am checking
the correct country and dispatch method? Am I doing
this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data
etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and
weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at
the moment, and need your
help!

I have a table that contains different mailing
destinations, and based on
the weight of the item and dispatch method, it will
go a different supplier.

So, if, for instance the destination is USA, and
the item weight 400 grams
and wants to be going Priority service, I'll be
able to check that this item
will go via "Fast Post". However if it weighs 100
grams, it will go "Light
Post".

With that in mind, I have a select query
(qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want
to use it to update
another table (tblProcessData) with the correct
supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<="
&
[tblProcessData].[Item_Weight (gms)] & " and
weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same
supplier; where am I going
wrong?

 

[Guest]

Hello Duane, the dLookUp works a treat; thank you!

One thing that is still confusing me though, is in the criteria part of the
function. I'm getting confused as to why you have used blocks of three, and
what they refer to.

Could you clarify this for me please?

To get the Supplier, you would use an expression like:
DLookUp("Supplier","qryCountryandSuppliers",[Item_Weightgms] & " Between
WeightBandStart and WeightBandEnd AND [CountryName] ="""
& [Processed_Country] & """ AND DispatchMethodName=""" & [Dispatch_Method] &
"""")

--
Duane Hookom
Microsoft Access MVP

Okay,

tblProcessData contains:

Processed_Country - Text
Processed_Supplier - Text
Dispatch_Method - Text
Item_WeightGMS (it was pointed out with brackets, the sql could be
confusing) - Long Integer

Processed_Country will contain the name of the destination country,
Processed_Supplier will contain the name of the company who will be mailing
the product,
Dispatch_Method contains the service this item will be sent and
Item_WeightGMS will contain the weight of the item.

qryCountryandSuppliers contains

CountryName - Text
Supplier - Text
DispatchMethodName - Text
WeightBandStart - Long Integer
WeightBandEnd - Long Integer

How values relate:

Processed_Country = CountryName - Text
Processed_Supplier = Supplier - Text
Dispatch_Method = DispatchMethodName - Text
Item_WeightGMS will fall in between WeightBandStart and WeightBandEnd

By the way, thank you for your on going interest in this Duane, I really
appreciate it.

John

You need to provide the significant field names and data types of
qryCountryAndSuppliers and tblProcessData as well as how values in the tables
relate.

--
Duane Hookom
Microsoft Access MVP


:

Sorry yes, the 'supplier' and 'company' are one and the same.

Our customers would supply us data containing addresses for which we would
send a particular product to. Depending on where they are (along with weight
and service) will dictate which supplier will be sending this item.

:

I don't think you have described enough about the "customer data" to allow us
to tell you how to create a dlookup of the other table/query. Apparently you
want the DLookup() to return the company. Your earlier attempts tried to
return a field named "Supplier".

--
Duane Hookom
Microsoft Access MVP


:

The way the supplier is selected is dependant on country, weight and dispatch
method.

For example if a magazine weighing 200 grams going to Greece goes 'Priority'
speed, it could go via company #1, but if it weighed 890 grams, it could go
via company #3. These companies could change again if it goes 'economy'
speed

I have a table that contains weight band 'from' and 'to' values which change
dependant on who is the cheapest to that particular destination.

Country - Weight Start - Weight End Dispatch Method - Company
Greece - 0 - 100 - priority -
Company #1
Greece - 101 - 800 - priority - Company
#2
Greece - 801 - 2000 - priority -Company #3

When I run the customer data which contains all the address destinations, it
will reference this table in order to pick the relevant company.

I hope this makes sense; if there's anything else i can clarify let me know!

John

:

You might want to describe how you want to select a Supplier. What is this
selection based on and where do the selection criteria come from? We know
about tblProcessData.Item_Weight(gms) and qryCOuntryandSuppliers but little
else.
--
Duane Hookom
Microsoft Access MVP


:

That looks great Duane; thank you! However - and I apologise for sounding
like an idiot - but how would I add additional criteria in? I'm getting
tangled up in all the quote marks at the end of the query!

:

If you need to select a supplier based on more than the weight, you will need
to add additional criteria in the DLookup().

DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend AND [CountryName ="""
& [Country] & """")

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for replying; I've just tried that and I am experiencing the same
problem. Is there a way of doing a 'criteria' to ensure that I am checking
the correct country and dispatch method? Am I doing this the 'best' way or
is there a better alternative?

:

It's a bit difficult to tell without seeing your data etc. You could try:
DLookUp("Supplier","qryCountryandSuppliers",[tblProcessData].[Item_Weight
(gms)] & " Between WeightBandStart and weightbandend")

--
Duane Hookom
Microsoft Access MVP


:

Hi there,

I'm having trouble using the DLookup function at the moment, and need your
help!

I have a table that contains different mailing destinations, and based on
the weight of the item and dispatch method, it will go a different supplier.

So, if, for instance the destination is USA, and the item weight 400 grams
and wants to be going Priority service, I'll be able to check that this item
will go via "Fast Post". However if it weighs 100 grams, it will go "Light
Post".

With that in mind, I have a select query (qryCountryandSuppliers) built from
different other tables which contains:

CountryName
Supplier
DispatchMethod
WeightBandStart
WeightBandEnd

the function I have built looks like this as I want to use it to update
another table (tblProcessData) with the correct supplier according to the
weight:

DLookUp("Supplier","qryCountryandSuppliers","WeightBandStart<=" &
[tblProcessData].[Item_Weight (gms)] & " and weightbandend>=" &
[tblProcessData].[Item_Weight (gms)])

All it seems to be doing is referencing the same supplier; where am I going
wrong?