SQL Insert Into Where Syntax

[DeoM]

Help, Please tell me whats wrong with my SQL syntax; am getting a data mismatch error.
POCreation is a blank table where I want to append data from
POparts for only a certain PO number
POnumber is Long Integer
strPOnumber is a variable where i input the POnumber i want to append.


DoCmd.RunSQL "INSERT INTO POCreation SELECT * FROM [POparts] WHERE [POnumber] = '" & strPOnumber & "'"

 

[RoyVidar]

Single quotes are used on text fields, for numerics you don't need them:

[tt]DoCmd.RunSQL "INSERT INTO POCreation SELECT * FROM [POparts] WHERE [POnumber] = " & strPOnumber[/tt]

Roy-Vidar

 

[TheAceMan1]

How are ya DeoM . . . . .

You need to specifiy the fields and [purple]on a one to one basis[/purple]. If the field order is different between the tables its easy to get a mismatch error.

Something like:

[blue]DoCmd.RunSQL "INSERT INTO POCreation [purple][b](field1,field2 ...)[/b][/purple] SELECT [purple][b]field1,field2 ...[/b][/purple] FROM [POparts] WHERE [POnumber] = '" & strPOnumber & "'"[/blue]

See Ya! . . . . . .

 

[DeoM]

Thanks AceMan,
They are identical tables.

 

[TheAceMan1]

DeoM . . . . .

[blue]POnumber is Long Integer[/blue]

Did you try [blue]RoyVidar's[/blue] suggestion?

See Ya! . . . . . .

 

[redapples]

Is it just me?

SQL Where Clause has the following parts

WHERE ( )

Inside the above you need this syntax

((Field) = value)

So yours would be

"WHERE (((PONumber) = " & strPoNumber & "))"

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[earthandfire]

redapples, the qbe gets carried away with brackets and more often than not they can be stripped out.

 

[redapples]

Just checked - I stand corrected, it is just me!

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk