Access 2003 - Search where row source is selected from table/query

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

Guest

The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
 
Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Hi
The table is named TZS-GIC - there are five fields in the table:
TZS GIC Region Location County

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.

Also, when the user first initiates a search - a parameter box pops up
looking for the TZS-GIC.Location. If the TZS is entered in that box the
record is still not found. My concern is that the error does not occur in
Access 2000 version. Thanks for your interest and assistance.
Thanks
Eileen

strive4peace said:
Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
Click to expand...
Click to expand...
 
Hi eileeen,

why do you have 2 fields in the same table with the same information:

TZS = GIC

also, you did not give an example of the data, only the fieldnames...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi
The table is named TZS-GIC - there are five fields in the table:
TZS GIC Region Location County

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.

Also, when the user first initiates a search - a parameter box pops up
looking for the TZS-GIC.Location. If the TZS is entered in that box the
record is still not found. My concern is that the error does not occur in
Access 2000 version. Thanks for your interest and assistance.
Thanks
Eileen

strive4peace said:
Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
Click to expand...
Click to expand...
Click to expand...
 
Actually the field GIC is used to consolidate the TZS codes - I wrote:

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.
TZS GIC Region Location County
A111 A100 W ***** *****
A112 A100 W ***** ***** (* omitted on purpose)

The point I am really making is that the search function works in Access
2000 but does not in Access 2003.

Thanks.


strive4peace said:
Hi eileeen,

why do you have 2 fields in the same table with the same information:

TZS = GIC

also, you did not give an example of the data, only the fieldnames...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi
The table is named TZS-GIC - there are five fields in the table:
TZS GIC Region Location County

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.

Also, when the user first initiates a search - a parameter box pops up
looking for the TZS-GIC.Location. If the TZS is entered in that box the
record is still not found. My concern is that the error does not occur in
Access 2000 version. Thanks for your interest and assistance.
Thanks
Eileen

strive4peace said:
Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Eileen wrote:
The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
Click to expand...
Click to expand...
Click to expand...
 
Hi Eileen,

"the search function works in Access 2000 but does not in Access 2003."

Access 2000 is more forgiving for some types of problems -- I was asking
questions to find out what the problem is for 2003 ...

"The bound column is 1 and contains TZS"

is the field defined as a lookup in your table design?

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm




Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Actually the field GIC is used to consolidate the TZS codes - I wrote:

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.
TZS GIC Region Location County
A111 A100 W ***** *****
A112 A100 W ***** ***** (* omitted on purpose)

The point I am really making is that the search function works in Access
2000 but does not in Access 2003.

Thanks.


strive4peace said:
Hi eileeen,

why do you have 2 fields in the same table with the same information:

TZS = GIC

also, you did not give an example of the data, only the fieldnames...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi
The table is named TZS-GIC - there are five fields in the table:
TZS GIC Region Location County

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.

Also, when the user first initiates a search - a parameter box pops up
looking for the TZS-GIC.Location. If the TZS is entered in that box the
record is still not found. My concern is that the error does not occur in
Access 2000 version. Thanks for your interest and assistance.
Thanks
Eileen

:

Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Eileen wrote:
The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
Click to expand...
Click to expand...
Click to expand...
 
Hi
It's pretty strange but I suppose the problem must be due to Access 2000
being more forgiving :)
is the field defined as a lookup in your table design?
Click to expand...
No, it is not. It is just a text box. I have tried to modify the code
without success so I may have to change the whole design of it at this point.
The same "select" code works in all other databases so I will have to take a
closer look to determine where the flaw is. Thanks for your assistance.

Eileen



strive4peace said:
Hi Eileen,

"the search function works in Access 2000 but does not in Access 2003."

Access 2000 is more forgiving for some types of problems -- I was asking
questions to find out what the problem is for 2003 ...

"The bound column is 1 and contains TZS"

is the field defined as a lookup in your table design?

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm




Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Actually the field GIC is used to consolidate the TZS codes - I wrote:

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.
TZS GIC Region Location County
A111 A100 W ***** *****
A112 A100 W ***** ***** (* omitted on purpose)

The point I am really making is that the search function works in Access
2000 but does not in Access 2003.

Thanks.


strive4peace said:
Hi eileeen,

why do you have 2 fields in the same table with the same information:

TZS = GIC

also, you did not give an example of the data, only the fieldnames...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Eileen wrote:
Hi
The table is named TZS-GIC - there are five fields in the table:
TZS GIC Region Location County

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.

Also, when the user first initiates a search - a parameter box pops up
looking for the TZS-GIC.Location. If the TZS is entered in that box the
record is still not found. My concern is that the error does not occur in
Access 2000 version. Thanks for your interest and assistance.
Thanks
Eileen

:

Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Eileen wrote:
The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
Click to expand...
Click to expand...
Click to expand...
 
Hi Eileen,

Between the 2 versions, though, I must say that I like 2003 better --
you have to fix all the errors, but I find it to be more stable.

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi
It's pretty strange but I suppose the problem must be due to Access 2000
being more forgiving :)
is the field defined as a lookup in your table design?
Click to expand...
No, it is not. It is just a text box. I have tried to modify the code
without success so I may have to change the whole design of it at this point.
The same "select" code works in all other databases so I will have to take a
closer look to determine where the flaw is. Thanks for your assistance.

Eileen



strive4peace said:
Hi Eileen,

"the search function works in Access 2000 but does not in Access 2003."

Access 2000 is more forgiving for some types of problems -- I was asking
questions to find out what the problem is for 2003 ...

"The bound column is 1 and contains TZS"

is the field defined as a lookup in your table design?

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm




Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Actually the field GIC is used to consolidate the TZS codes - I wrote:

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.
TZS GIC Region Location County
A111 A100 W ***** *****
A112 A100 W ***** ***** (* omitted on purpose)

The point I am really making is that the search function works in Access
2000 but does not in Access 2003.

Thanks.


:

Hi eileeen,

why do you have 2 fields in the same table with the same information:

TZS = GIC

also, you did not give an example of the data, only the fieldnames...


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Eileen wrote:
Hi
The table is named TZS-GIC - there are five fields in the table:
TZS GIC Region Location County

There are numerous TZS codes and the GIC is used to consolidate them.
Such as when the TZS may be A111, A112, A113 - the GIC is A100 for each
record.

Also, when the user first initiates a search - a parameter box pops up
looking for the TZS-GIC.Location. If the TZS is entered in that box the
record is still not found. My concern is that the error does not occur in
Access 2000 version. Thanks for your interest and assistance.
Thanks
Eileen

:

Hi Eileen,

in your SQL Statement

SELECT
[TZS-GIC].[TZS],[TZS-GIC].Location
FROM
[TZS-GIC]
WHERE
TZS = GIC
ORDER BY
[TZS-GIC].Location;

Where is GIC coming from?

Can you give an example of the data in a record of this table?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Eileen wrote:
The Access 2000 version contains a field where the row source is selected
data from a location code table. The bound column is 1 and contains TZS.
This field is searchable and will produce results based on the TZS code
entered using the Find function. The Access 2003 version will not produce
results on the bound column data searched but will only produce results if
the location (description) is entered.
Here is the code:
SELECT [TZS-GIC].[TZS],[TZS-GIC].Location FROM [TZS-GIC] WHERE TZS = GIC
ORDER BY [TZS-GIC].Location;
Not all of our users have been upgraded to 2003 yet so I cannot convert the
database nor did I think that would be necessary.
Is there a work around for this?
Thanks
Eileen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top