Still Didn't Work

  • Thread starter Thread starter Courtney
  • Start date Start date
C

Courtney

When I try to run the union query, I get "Syntax error in
string in query expression '[Account Number] ="&
{accountTextBox}.Value'.

Now, I don't know if this is relevant, but I will mention
it anyway. The account number field on Contact Table is
a Combo Box that presents options from a query that was
built off of another table called demographicstable. In
essence, the master list of account numbers is found on
the table called demographicstable, the user then
associates the account number to contacts using the
contact Information table and then the account number is
associated with an order on the Request table.

I thought that if I changed the {accountTextBox} to
{accountComboBox}, it would work, but it didn't.

Please help. I'm desperate. Thanks,


Subject: Re: Combo box
From: "Gerald Stanley" <[email protected]> Sent:
3/9/2004 11:32:57 AM

The quey SQL should look something like
"SELECT [Contact First Name 1]& " " & [Contact Last Name
1]
FROM [Contact Information]
WHERE [Account Number] = " & {accountTextBox}.Value
& "UNION
SELECT [Contact First Name 2]& " " & [Contact Last Name 2]
FROM [Contact Information]
WHERE [Account Number] = " & {accountTextBox}.Value

Hope That Helps
Gerald Stanley MCSD
-----Original Message-----
I have a table that I call Contact Information with the
following fields on it:
Account Number
Contact First Name 1
Contact Last Name 1
Contact 1 Address
Contact 1 City
Contact 1 State
Contact 1 Zip
Contact 1 County
Contact First Name 2
Contact Last Name 2
Contact 2 Address
Contact 2 City
Contact 2 State
Contact 2 Zip
Contact 2 County

This table is linked to another table that contains order
information. The relationship is based on the Account
Number.

I created a form based off of the order table. In this
form, I have a field called contact name that I have
designed as a combo box. When the user types the account
number in the formand goes to select a contact name from
the combo box, I want it to display both contact name 1
and combo name 2 associated with that account number as
choices. For example, if I type account number 1234, I
want to click on the drop down box and see Ben Smith and
Jane Smith as options in the drop down. I tried creating
a query and basing the combo box off of that query, but
it just didn't work out right.

Please help.
.
Click to expand...
..
 
Courtney
Three things to check out
1. Is the column in the table called [Account Number]. If
not change the SQL to the true column name.
2. The name of the control that holds the account number
should not be enclosed in {}. I put them there to show you
what you need to change. Remember that I cannot see your
Access application and am unable to name the controls and
column names precisely.
3. Check that the correct spacing is applied. The = sign
should have a space either side.

Hope That Helps
Gerald Stanley MCSD
-----Original Message-----
When I try to run the union query, I get "Syntax error in
string in query expression '[Account Number] ="&
{accountTextBox}.Value'.

Now, I don't know if this is relevant, but I will mention
it anyway. The account number field on Contact Table is
a Combo Box that presents options from a query that was
built off of another table called demographicstable. In
essence, the master list of account numbers is found on
the table called demographicstable, the user then
associates the account number to contacts using the
contact Information table and then the account number is
associated with an order on the Request table.

I thought that if I changed the {accountTextBox} to
{accountComboBox}, it would work, but it didn't.

Please help. I'm desperate. Thanks,


Subject: Re: Combo box
From: "Gerald Stanley" <[email protected]> Sent:
3/9/2004 11:32:57 AM

The quey SQL should look something like
"SELECT [Contact First Name 1]& " " & [Contact Last Name
1]
FROM [Contact Information]
WHERE [Account Number] = " & {accountTextBox}.Value
& "UNION
SELECT [Contact First Name 2]& " " & [Contact Last Name 2]
FROM [Contact Information]
WHERE [Account Number] = " & {accountTextBox}.Value

Hope That Helps
Gerald Stanley MCSD
-----Original Message-----
I have a table that I call Contact Information with the
following fields on it:
Account Number
Contact First Name 1
Contact Last Name 1
Contact 1 Address
Contact 1 City
Contact 1 State
Contact 1 Zip
Contact 1 County
Contact First Name 2
Contact Last Name 2
Contact 2 Address
Contact 2 City
Contact 2 State
Contact 2 Zip
Contact 2 County

This table is linked to another table that contains order
information. The relationship is based on the Account
Number.

I created a form based off of the order table. In this
form, I have a field called contact name that I have
designed as a combo box. When the user types the account
number in the formand goes to select a contact name from
the combo box, I want it to display both contact name 1
and combo name 2 associated with that account number as
choices. For example, if I type account number 1234, I
want to click on the drop down box and see Ben Smith and
Jane Smith as options in the drop down. I tried creating
a query and basing the combo box off of that query, but
it just didn't work out right.

Please help.
.
Click to expand...
..


.
Click to expand...
 
When I try to run the union query, I get "Syntax error in
string in query expression '[Account Number] ="&
{accountTextBox}.Value'.
Click to expand...

In addition to Gerald's suggestions, I think you'll need an explicit
reference to [Forms] - i.e.

[Account Number] = [Forms]![NameOfYourForm]![NameOfTheControl]

The .Value property is the default so you can safely leave it off, but
a Query doesn't have any way of knowing which form to look at.
 
Back
Top