Read string and enter value in next field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
 
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.
 
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


Dennis said:
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

albycindy said:
I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
 
No, the "'" is correct. What you're doing is putting quotes around the
results of the Mid([LongCode], 6, 2)

What you should be getting is something like:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = 'B1'")

However, you're saying that your supplier name is in a field named
SupplierName, so that should be the first argument:

=DLookup("[SupplierName]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


Dennis said:
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

albycindy said:
I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


Dennis said:
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

albycindy said:
I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
Sorry, that should read...

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Left([Postcode],2,2) & "'")

Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


Dennis said:
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
Excellent! That works perfectly, thankyou.

I am trying to do something very similar with another field. Get Access to
"read" the first two letters of the postcode and put the closest warehouse
into the next field. So i have put this in, however, no result is occurring
(no error either):

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Mid([Postcode],2,2) & "'")

Any glaring errors????


Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


Dennis said:
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
Well...actually, now that I test the Left thing it is returning an error.....

Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


Dennis said:
Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
Left only takes 1 parameter so it should be Left([PostCode],2)
What do you mean by closest warehouse ?
The DLookup will return the first one it finds where the postcode matches
the first 2 characters.

albycindy said:
Excellent! That works perfectly, thankyou.

I am trying to do something very similar with another field. Get Access to
"read" the first two letters of the postcode and put the closest warehouse
into the next field. So i have put this in, however, no result is occurring
(no error either):

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Mid([Postcode],2,2) & "'")

Any glaring errors????


Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


:

Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
There are three warehouses in the country and depending on the first two
letters of the postcode, one of the warehouses is responsible for the
collection of the item.

I'll try that...give me a sec

Dennis said:
Left only takes 1 parameter so it should be Left([PostCode],2)
What do you mean by closest warehouse ?
The DLookup will return the first one it finds where the postcode matches
the first 2 characters.

albycindy said:
Excellent! That works perfectly, thankyou.

I am trying to do something very similar with another field. Get Access to
"read" the first two letters of the postcode and put the closest warehouse
into the next field. So i have put this in, however, no result is occurring
(no error either):

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Mid([Postcode],2,2) & "'")

Any glaring errors????


Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

:

I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


:

Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
This is what I have, and it's not returning anything:

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Left([Postcode],2) & "'")

albycindy said:
Well...actually, now that I test the Left thing it is returning an error.....

Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

albycindy said:
I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


:

Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
If you want to return a warehouse based on the postcode then you have it the
wrong way around. In English, get me the warehouse name field from my
warehouses table, where the post code prefix field in my table matches the
first 2 characters from a field on my form.

=DLookUp("[Warehouse]","tblWarehouse","[PostCodeChar] = '" &
Left([Postcode],2) & "'")


albycindy said:
This is what I have, and it's not returning anything:

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Left([Postcode],2) & "'")

albycindy said:
Well...actually, now that I test the Left thing it is returning an error.....

Dennis said:
Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

:

I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


:

Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

Cindy
Click to expand...
Click to expand...
Click to expand...
 
Thankyou - thankyou - thankyou!!!!

I love this forum....virtual chocolates to you all!

Dennis said:
If you want to return a warehouse based on the postcode then you have it the
wrong way around. In English, get me the warehouse name field from my
warehouses table, where the post code prefix field in my table matches the
first 2 characters from a field on my form.

=DLookUp("[Warehouse]","tblWarehouse","[PostCodeChar] = '" &
Left([Postcode],2) & "'")


albycindy said:
This is what I have, and it's not returning anything:

=DLookUp("[PostcodeChar]","tblWarehouse","[Warehouse] = '" &
Left([Postcode],2) & "'")

albycindy said:
Well...actually, now that I test the Left thing it is returning an error.....

:

Is there a space in your supplier ID field name ?
if so it should read

=DLookup("[SupplierName]","tblSuppliers","[Supplier ID] = '" &
Mid([LongCode],6,2) & "'")

:

I've done that, I'm not getting any errors (so thats good) but I'm not
getting a result either.

I've got tblSuppliers with SupplierID and SupplierName

Supplier ID SupplierName
B1 Bisilque
H1 Harewood
(and about eight others)


So, I've written this in the Control Source:

=DLookup("[Supplier]","tblSuppliers","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

I've a feeling there should be something in the "", am i right?


:

Is the product long code always in the format 4 chars, dash, 2 chars, dash, 4
chars ?
If yes, then set the control source property of the supplier field to

=DLookup("[Supplier]","Suppliers Table","[SupplierID] = '" &
Mid([LongCode],6,2) & "'")

Substitute your actual field names and table in the above statement.

:

I need Access to read the value in a field and then based on the middle
letter, return a value in the next field.

The first field is a Product Long Code and the second field is the Supplier.

A Long Code can look like: EA01-B1-4460

I want Access to read the B1 and return the word Bisilque in the Supplier
field.

I have mucked around with the Long Code AfterUpdate Event but not really
having much luck. Any ideas?

all appreciated, cheers.

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