Need help with Access SQL Query

[Charles E Finkenbiner]

Hi All,

I have 2 tables, Countries and States.

Countries is setup like this:
2DigCode = Primary Key
3DigCode
Number
Description

States is setup like this:
Code = Primary Key
Description
2DigCountryCode

I am trying to use a lookup in field '2DigCountryCode' to be sure that a
valid 2 digit country code is entered or selected. I want the ComboBox
to show country descriptions but I want the 2DigCode inserted into
2DigCountryCode. I came up with this SQL query for the row source:

SELECT Description FROM Countries ORDER BY Countries.Description;

The bound column is set to number 1, because Countries.2DigCode is
column 1. But, when I select a country the description is entered
instead of the 2 digit country code. I have tried many things but I
have been unable to get it to do what I want.

Can someone please point out the error of my ways?


Thanks for any help,

Charles

 

[John Spencer (MVP)]

Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is correct.

 

[Charles E Finkenbiner]

Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?


Thanks for your help,

Charles

 

[John Spencer (MVP)]

I thought you wanted only the description to show in the combobox.

If you want to show both, then clear the field width so that Access will
automatically show both columns in the drop-down mode. but only the 2DigCode
when the control is not in dropdown mode. If you only want the 2-digit country
code, then change your query to only select that, set number of columns to 1.

As far as the problem with the brackets, I should have caught that. Fieldnames
that don't start with a letter or that contain other than letters or numbers
after the first character need [] around them.

Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?

Thanks for your help,

Charles

Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is correct.

 

[Charles E Finkenbiner]

Hi John,

Yes, I do want the country description only to show in the combobox.
But I also want to be able to enter the 2 digit country code in the
field, if I do not use the combobox. I have gotten the combobox to
display both fields but I still have the problem of the description, not
the 2 digit code, to be entered into the field when one is clicked on.
My reasoning is that there is no sense duplicating a 50 character field
when a 2 character field will do fine. I should be able to use another
lookup during reports, or whatever, to show the full country description
if I need to.

I will digest your suggestions and try again. I will post back later
with my results.

Maybe I am looking at this the wrong way. Right now, with the previous
SELECT statement I posted, it appears to insert the full country
description, but I do not get an error dialog about the field size being
too small, as I did before. Does this mean that Access is storing only
the 2 digit code but is showing me the full description? If this is
true then why can't I manually enter a 2 digit code without getting the
'not in list' error? As you can tell I know next to nothing and am
learning with this project. I have done relational databases design
since the DOS days but am new to Access.


Thanks for your help,

Charles

I thought you wanted only the description to show in the combobox.

If you want to show both, then clear the field width so that Access will
automatically show both columns in the drop-down mode. but only the 2DigCode
when the control is not in dropdown mode. If you only want the 2-digit country
code, then change your query to only select that, set number of columns to 1.

As far as the problem with the brackets, I should have caught that. Fieldnames
that don't start with a letter or that contain other than letters or numbers
after the first character need [] around them.

Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?

Thanks for your help,

Charles

Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is correct.

Charles E Finkenbiner wrote:


Hi All,

I have 2 tables, Countries and States.

Countries is setup like this:
2DigCode = Primary Key
3DigCode
Number
Description

States is setup like this:
Code = Primary Key
Description
2DigCountryCode

I am trying to use a lookup in field '2DigCountryCode' to be sure that a
valid 2 digit country code is entered or selected. I want the ComboBox
to show country descriptions but I want the 2DigCode inserted into
2DigCountryCode. I came up with this SQL query for the row source:

SELECT Description FROM Countries ORDER BY Countries.Description;

The bound column is set to number 1, because Countries.2DigCode is
column 1. But, when I select a country the description is entered
instead of the 2 digit country code. I have tried many things but I
have been unable to get it to do what I want.

Can someone please point out the error of my ways?

Thanks for any help,

Charles

 

[Charles E Finkenbiner]

Hi,

Ok, I have gotten it to work, more or less, the way I want it to. This
is how I have defined the lookup tab:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [2DigCode], [Description] FROM Countries ORDER
BY [Description];
Bound Column: 1
Column Count: 2
Column Heads: Yes
Column Widths: 0.4";3.6"
List Rows: 11
List Width: 4"
Limit To List: Yes

I can live with both the 2DigCode and Description showing in the
combobox. I really don't need to see the 2 digit code in the list but
it works.

Now, the only thing I wish I could fix is: If I enter the letter C and
then open the combobox the list starts at the first country code that
starts with C. In this example I am looking for Cambodia but I have to
hunt for it in the combobox because the 2 digit code for Cambodia is KH.
I would like to enter the C and have the list start at the first
country description that starts with the letter C. But, when I find
what I am looking for I still want the 2 digit country code entered.

Can I do this? Or am I out of luck? It would be perfect if I could
only display the country description in the combobox but still have it
enter the 2 digit country code once I click on a description. Am I
asking too much of Access or SQL?


Thanks for your help,

Charles

Hi John,

Yes, I do want the country description only to show in the combobox. But
I also want to be able to enter the 2 digit country code in the field,
if I do not use the combobox. I have gotten the combobox to display
both fields but I still have the problem of the description, not the 2
digit code, to be entered into the field when one is clicked on. My
reasoning is that there is no sense duplicating a 50 character field
when a 2 character field will do fine. I should be able to use another
lookup during reports, or whatever, to show the full country description
if I need to.

I will digest your suggestions and try again. I will post back later
with my results.

Maybe I am looking at this the wrong way. Right now, with the previous
SELECT statement I posted, it appears to insert the full country
description, but I do not get an error dialog about the field size being
too small, as I did before. Does this mean that Access is storing only
the 2 digit code but is showing me the full description? If this is
true then why can't I manually enter a 2 digit code without getting the
'not in list' error? As you can tell I know next to nothing and am
learning with this project. I have done relational databases design
since the DOS days but am new to Access.


Thanks for your help,

Charles

I thought you wanted only the description to show in the combobox.
If you want to show both, then clear the field width so that Access will
automatically show both columns in the drop-down mode. but only the
2DigCode
when the control is not in dropdown mode. If you only want the
2-digit country
code, then change your query to only select that, set number of
columns to 1.

As far as the problem with the brackets, I should have caught that.
Fieldnames
that don't start with a letter or that contain other than letters or
numbers
after the first character need [] around them.

Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?

Thanks for your help,

Charles

On 9/4/2005 3:08 PM, John Spencer (MVP) wrote:

Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is
correct.

Charles E Finkenbiner wrote:


Hi All,

I have 2 tables, Countries and States.

Countries is setup like this:
2DigCode = Primary Key
3DigCode
Number
Description

States is setup like this:
Code = Primary Key
Description
2DigCountryCode

I am trying to use a lookup in field '2DigCountryCode' to be sure
that a
valid 2 digit country code is entered or selected. I want the
ComboBox
to show country descriptions but I want the 2DigCode inserted into
2DigCountryCode. I came up with this SQL query for the row source:

SELECT Description FROM Countries ORDER BY Countries.Description;

The bound column is set to number 1, because Countries.2DigCode is
column 1. But, when I select a country the description is entered
instead of the 2 digit country code. I have tried many things but I
have been unable to get it to do what I want.

Can someone please point out the error of my ways?

Thanks for any help,

Charles

 

[John Spencer (MVP)]

Well, it sounds as if you want to be able to enter either the 2 digit code or
the country name. If that is the case you could make your combobox based on a
UNION query like


SELECT [2DigCode], [Description], "1" as MyOrder
FROM Countries
UNION
SELECT [2DigCode], [2DigCode] & " " & [Description], "2"
FROM Countries
ORDER BY MyOrder, [Description]

Set column widths to 0;4;0
Set Column count to 3


This should give you a list that displays like the following and you can type
either the country code or the country name to select the correct item.
Austraila
Cambodia
New Zealand
United States
AU Austraila
KH Cambodia
NZ New Zealand
US United States

If you want the list to have the values intermixed, then drop the myOrder from
the order by clause.

Hi,

Ok, I have gotten it to work, more or less, the way I want it to. This
is how I have defined the lookup tab:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [2DigCode], [Description] FROM Countries ORDER
BY [Description];
Bound Column: 1
Column Count: 2
Column Heads: Yes
Column Widths: 0.4";3.6"
List Rows: 11
List Width: 4"
Limit To List: Yes

I can live with both the 2DigCode and Description showing in the
combobox. I really don't need to see the 2 digit code in the list but
it works.

Now, the only thing I wish I could fix is: If I enter the letter C and
then open the combobox the list starts at the first country code that
starts with C. In this example I am looking for Cambodia but I have to
hunt for it in the combobox because the 2 digit code for Cambodia is KH.
I would like to enter the C and have the list start at the first
country description that starts with the letter C. But, when I find
what I am looking for I still want the 2 digit country code entered.

Can I do this? Or am I out of luck? It would be perfect if I could
only display the country description in the combobox but still have it
enter the 2 digit country code once I click on a description. Am I
asking too much of Access or SQL?

Thanks for your help,

Charles

Hi John,

Yes, I do want the country description only to show in the combobox. But
I also want to be able to enter the 2 digit country code in the field,
if I do not use the combobox. I have gotten the combobox to display
both fields but I still have the problem of the description, not the 2
digit code, to be entered into the field when one is clicked on. My
reasoning is that there is no sense duplicating a 50 character field
when a 2 character field will do fine. I should be able to use another
lookup during reports, or whatever, to show the full country description
if I need to.

I will digest your suggestions and try again. I will post back later
with my results.

Maybe I am looking at this the wrong way. Right now, with the previous
SELECT statement I posted, it appears to insert the full country
description, but I do not get an error dialog about the field size being
too small, as I did before. Does this mean that Access is storing only
the 2 digit code but is showing me the full description? If this is
true then why can't I manually enter a 2 digit code without getting the
'not in list' error? As you can tell I know next to nothing and am
learning with this project. I have done relational databases design
since the DOS days but am new to Access.


Thanks for your help,

Charles

I thought you wanted only the description to show in the combobox.
If you want to show both, then clear the field width so that Access will
automatically show both columns in the drop-down mode. but only the
2DigCode
when the control is not in dropdown mode. If you only want the
2-digit country
code, then change your query to only select that, set number of
columns to 1.

As far as the problem with the brackets, I should have caught that.
Fieldnames
that don't start with a letter or that contain other than letters or
numbers
after the first character need [] around them.
Charles E Finkenbiner wrote:

Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?

Thanks for your help,

Charles

On 9/4/2005 3:08 PM, John Spencer (MVP) wrote:

Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is
correct.

Charles E Finkenbiner wrote:


Hi All,

I have 2 tables, Countries and States.

Countries is setup like this:
2DigCode = Primary Key
3DigCode
Number
Description

States is setup like this:
Code = Primary Key
Description
2DigCountryCode

I am trying to use a lookup in field '2DigCountryCode' to be sure
that a
valid 2 digit country code is entered or selected. I want the
ComboBox
to show country descriptions but I want the 2DigCode inserted into
2DigCountryCode. I came up with this SQL query for the row source:

SELECT Description FROM Countries ORDER BY Countries.Description;

The bound column is set to number 1, because Countries.2DigCode is
column 1. But, when I select a country the description is entered
instead of the 2 digit country code. I have tried many things but I
have been unable to get it to do what I want.

Can someone please point out the error of my ways?

Thanks for any help,

Charles

 

[John Spencer (MVP)]

Whoops! You could end up with some strange behavior on the display. Since two
rows have the same bound value, Access will choose one of them to display
(perhaps the first in the sort order??). If you do implement my suggestion, I
would be interested in hearing what the behavior is when you have two bound
values that are equal. Which row gets displayed? Is it consistent?

Well, it sounds as if you want to be able to enter either the 2 digit code or
the country name. If that is the case you could make your combobox based on a
UNION query like

SELECT [2DigCode], [Description], "1" as MyOrder
FROM Countries
UNION
SELECT [2DigCode], [2DigCode] & " " & [Description], "2"
FROM Countries
ORDER BY MyOrder, [Description]

Set column widths to 0;4;0
Set Column count to 3

This should give you a list that displays like the following and you can type
either the country code or the country name to select the correct item.
Austraila
Cambodia
New Zealand
United States
AU Austraila
KH Cambodia
NZ New Zealand
US United States

If you want the list to have the values intermixed, then drop the myOrder from
the order by clause.

Hi,

Ok, I have gotten it to work, more or less, the way I want it to. This
is how I have defined the lookup tab:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [2DigCode], [Description] FROM Countries ORDER
BY [Description];
Bound Column: 1
Column Count: 2
Column Heads: Yes
Column Widths: 0.4";3.6"
List Rows: 11
List Width: 4"
Limit To List: Yes

I can live with both the 2DigCode and Description showing in the
combobox. I really don't need to see the 2 digit code in the list but
it works.

Now, the only thing I wish I could fix is: If I enter the letter C and
then open the combobox the list starts at the first country code that
starts with C. In this example I am looking for Cambodia but I have to
hunt for it in the combobox because the 2 digit code for Cambodia is KH.
I would like to enter the C and have the list start at the first
country description that starts with the letter C. But, when I find
what I am looking for I still want the 2 digit country code entered.

Can I do this? Or am I out of luck? It would be perfect if I could
only display the country description in the combobox but still have it
enter the 2 digit country code once I click on a description. Am I
asking too much of Access or SQL?

Thanks for your help,

Charles

Hi John,

Yes, I do want the country description only to show in the combobox. But
I also want to be able to enter the 2 digit country code in the field,
if I do not use the combobox. I have gotten the combobox to display
both fields but I still have the problem of the description, not the 2
digit code, to be entered into the field when one is clicked on. My
reasoning is that there is no sense duplicating a 50 character field
when a 2 character field will do fine. I should be able to use another
lookup during reports, or whatever, to show the full country description
if I need to.

I will digest your suggestions and try again. I will post back later
with my results.

Maybe I am looking at this the wrong way. Right now, with the previous
SELECT statement I posted, it appears to insert the full country
description, but I do not get an error dialog about the field size being
too small, as I did before. Does this mean that Access is storing only
the 2 digit code but is showing me the full description? If this is
true then why can't I manually enter a 2 digit code without getting the
'not in list' error? As you can tell I know next to nothing and am
learning with this project. I have done relational databases design
since the DOS days but am new to Access.


Thanks for your help,

Charles

On 9/4/2005 8:29 PM, John Spencer (MVP) wrote:

I thought you wanted only the description to show in the combobox.
If you want to show both, then clear the field width so that Access will
automatically show both columns in the drop-down mode. but only the
2DigCode
when the control is not in dropdown mode. If you only want the
2-digit country
code, then change your query to only select that, set number of
columns to 1.

As far as the problem with the brackets, I should have caught that.
Fieldnames
that don't start with a letter or that contain other than letters or
numbers
after the first character need [] around them.
Charles E Finkenbiner wrote:

Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?

Thanks for your help,

Charles

On 9/4/2005 3:08 PM, John Spencer (MVP) wrote:

Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is
correct.

Charles E Finkenbiner wrote:


Hi All,

I have 2 tables, Countries and States.

Countries is setup like this:
2DigCode = Primary Key
3DigCode
Number
Description

States is setup like this:
Code = Primary Key
Description
2DigCountryCode

I am trying to use a lookup in field '2DigCountryCode' to be sure
that a
valid 2 digit country code is entered or selected. I want the
ComboBox
to show country descriptions but I want the 2DigCode inserted into
2DigCountryCode. I came up with this SQL query for the row source:

SELECT Description FROM Countries ORDER BY Countries.Description;

The bound column is set to number 1, because Countries.2DigCode is
column 1. But, when I select a country the description is entered
instead of the 2 digit country code. I have tried many things but I
have been unable to get it to do what I want.

Can someone please point out the error of my ways?

Thanks for any help,

Charles

 

[Charles E Finkenbiner]

Hi John,

Ok, I will try it out later and post back. Since I have run into the
the need to use forms, in another post I made, I will most likely just
forget this approach and use a form for this also.

Before I do this I will try your suggestion and post back.


Thanks for the help,

Charles

Whoops! You could end up with some strange behavior on the display. Since two
rows have the same bound value, Access will choose one of them to display
(perhaps the first in the sort order??). If you do implement my suggestion, I
would be interested in hearing what the behavior is when you have two bound
values that are equal. Which row gets displayed? Is it consistent?

Well, it sounds as if you want to be able to enter either the 2 digit code or
the country name. If that is the case you could make your combobox based on a
UNION query like

SELECT [2DigCode], [Description], "1" as MyOrder
FROM Countries
UNION
SELECT [2DigCode], [2DigCode] & " " & [Description], "2"
FROM Countries
ORDER BY MyOrder, [Description]

Set column widths to 0;4;0
Set Column count to 3

This should give you a list that displays like the following and you can type
either the country code or the country name to select the correct item.
Austraila
Cambodia
New Zealand
United States
AU Austraila
KH Cambodia
NZ New Zealand
US United States

If you want the list to have the values intermixed, then drop the myOrder from
the order by clause.

Hi,

Ok, I have gotten it to work, more or less, the way I want it to. This
is how I have defined the lookup tab:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [2DigCode], [Description] FROM Countries ORDER
BY [Description];
Bound Column: 1
Column Count: 2
Column Heads: Yes
Column Widths: 0.4";3.6"
List Rows: 11
List Width: 4"
Limit To List: Yes

I can live with both the 2DigCode and Description showing in the
combobox. I really don't need to see the 2 digit code in the list but
it works.

Now, the only thing I wish I could fix is: If I enter the letter C and
then open the combobox the list starts at the first country code that
starts with C. In this example I am looking for Cambodia but I have to
hunt for it in the combobox because the 2 digit code for Cambodia is KH.
I would like to enter the C and have the list start at the first
country description that starts with the letter C. But, when I find
what I am looking for I still want the 2 digit country code entered.

Can I do this? Or am I out of luck? It would be perfect if I could
only display the country description in the combobox but still have it
enter the 2 digit country code once I click on a description. Am I
asking too much of Access or SQL?

Thanks for your help,

Charles

On 9/4/2005 9:00 PM, Charles E Finkenbiner wrote:

Hi John,

Yes, I do want the country description only to show in the combobox. But
I also want to be able to enter the 2 digit country code in the field,
if I do not use the combobox. I have gotten the combobox to display
both fields but I still have the problem of the description, not the 2
digit code, to be entered into the field when one is clicked on. My
reasoning is that there is no sense duplicating a 50 character field
when a 2 character field will do fine. I should be able to use another
lookup during reports, or whatever, to show the full country description
if I need to.

I will digest your suggestions and try again. I will post back later
with my results.

Maybe I am looking at this the wrong way. Right now, with the previous
SELECT statement I posted, it appears to insert the full country
description, but I do not get an error dialog about the field size being
too small, as I did before. Does this mean that Access is storing only
the 2 digit code but is showing me the full description? If this is
true then why can't I manually enter a 2 digit code without getting the
'not in list' error? As you can tell I know next to nothing and am
learning with this project. I have done relational databases design
since the DOS days but am new to Access.


Thanks for your help,

Charles

On 9/4/2005 8:29 PM, John Spencer (MVP) wrote:


I thought you wanted only the description to show in the combobox.
If you want to show both, then clear the field width so that Access will
automatically show both columns in the drop-down mode. but only the
2DigCode
when the control is not in dropdown mode. If you only want the
2-digit country
code, then change your query to only select that, set number of
columns to 1.

As far as the problem with the brackets, I should have caught that.
Fieldnames
that don't start with a letter or that contain other than letters or
numbers
after the first character need [] around them.
Charles E Finkenbiner wrote:


Hi John,

You pointed me in the right direction but I do have a question. I
received an error message about: Syntax error (missing operator) in
query expression '2DigCode'. In order to fix this error I had to put
braces [] around the field names, no problem, at least I figured that
out. I changed number of columns to 2 and set column widths to 0" as
you suggested.

Now when I open the state table the combobox display is exactly like I
want. When I click on a country description I do not get a field size
too small error anymore (2DigCountryCode is set to field size 2). But,
the full country description is displayed, not the 2 digit code. Also,
if I manually enter the 2 digit country code I get an error telling me
that my entry is not in the list.

I want to be able to manually enter the 2 digit country code, if I know
it or select from the list. And I only want the 2 digit country code
displayed, not the full country description.

Am I misunderstanding something about Access?

Thanks for your help,

Charles

On 9/4/2005 3:08 PM, John Spencer (MVP) wrote:


Modify the query to show the country; cod

SELECT 2DigCode, Description from Countries ORDER BY Description

Leave the bound column as 1
Change number of columns to 2
Set Column width to 0 to hide the first column

That should do it, assuming that my memory for property names is
correct.

Charles E Finkenbiner wrote:



Hi All,

I have 2 tables, Countries and States.

Countries is setup like this:
2DigCode = Primary Key
3DigCode
Number
Description

States is setup like this:
Code = Primary Key
Description
2DigCountryCode

I am trying to use a lookup in field '2DigCountryCode' to be sure
that a
valid 2 digit country code is entered or selected. I want the
ComboBox
to show country descriptions but I want the 2DigCode inserted into
2DigCountryCode. I came up with this SQL query for the row source:

SELECT Description FROM Countries ORDER BY Countries.Description;

The bound column is set to number 1, because Countries.2DigCode is
column 1. But, when I select a country the description is entered
instead of the 2 digit country code. I have tried many things but I
have been unable to get it to do what I want.

Can someone please point out the error of my ways?

Thanks for any help,

Charles