Access 2010 SQL Problem

[pinkpanther6666]

Hi All,

Wondering if anyone could help me on this Access 2010 SQL problem I have.

My tables are as follows:-

Table name .......... Main
Field Name 1 .......... Name
Field Name 2 .......... Gender


Table Name ............ Player
Field Name 1 ............ PlayerName
field Name 2 ............ Gender

In the player table for some records the value is set to 1

What the sql code below is suppose to do is to get all the playername where the gender =1 and then sets the player gender to 1 in the main table

But its not working

UPDATE Main SET Gender = 1
WHERE Name IN
(SELECT PlayerName FROM Player WHERE Gender = 1)




Can Anyone help please

Many thanks in advance


Steve

 

[alansidman]

Unable to recreate your issue. The Query updates for me. However, the Field name "Name" is a reserved word. Suggest you change the field name which is what I did and the Update Query works for me.

 

[pinkpanther6666]

Alansidman,

Many thanks for your reply

i have changed the name to ABC and still get the error of "type mismatch in expression"

my sql code is

UPDATE Main SET Gender = 1
WHERE ABC
IN (SELECT PlayerName FROM Player WHERE Gender = 1);

Any other thoughts



Steve

 

[pinkpanther6666]

Alansidman

Just had a thought ........... the Gender Field in the Main table is a combo box ......... would that make a difference


Cheers

Steve

 

[pinkpanther6666]

alansidman

Could you please send me that database your created please to stevenlovell66@virginmedia.com


Many Thanks


Steve

 

[alansidman]

You should avoid lookup fields in tables. They should appear only in forms. Read this:

The Access Web - The Evils of Lookup Fields in Tables

I did not save the db after testing. Change the lookup to a number field (long or integer). Put your lookup in your bound form.

 

[pinkpanther6666]

AlanSidman


Many Thanks

Ill Have a go at that

Thanks again


Steve