asked on

String chain and SQL

In a group of records I have two fields
"Productor" and "Field" defined as STRING
As the user enter it, data is as such

Productor Lote
-------------------
PPK 5452
PPK 2012
PPK 8523

I need a group button in the form (data is displayed in continuous form) to change for each record :

PPK is deleted and the third digit of the Lote is put in Productor. The fourth and first digit are now first and second and the second digit is the third so data will become :

Productor Lote
--------------------

5 254
1 220
2 385

I will like it to be SQL statement, so I can put the boton on any form.

Thamk you

dannywareham

What do you want it to update?
Do you want it to change the table value? Just show the value on the form?

GRayL

Update table (Productor, Lote) select mid(Lote,3,1), Right(Lote,1) & Left(Lote,2) From table;

maguerez

ASKER

I want to change the data in the table

dannywareham

GRayL has teh answer for you then

An UPDATE query. :-)

maguerez

ASKER

I have a syntax error :
DoCmd.RunSQL "UPDATE CCCit (Productor,NºLote) SELECT mid(NºLote,3,1), Right(NºLote,1) & Left(NºLote,2) From CCCit Where [nopartida]=[formularios].[Controlcalidadmaster].[nopartida]"

argghh !

maguerez

ASKER

Can anybody help on the syntax problem ?

GRayL

DoCmd.RunSQL "UPDATE CCCit (Productor,NºLote) SELECT mid(NºLote,3,1), Right(NºLote,1) & Left(NºLote,2) From CCCit Where [nopartida]='" & [formularios].[Controlcalidadmaster].[nopartida] & "';"

If [nopartida] is a number remove the single quotes. This Query assumes [nopartida] is a string.

maguerez

ASKER

I have still a syntax error "3144" in your expression and in the one I have slightly modified.

DoCmd.RunSQL "UPDATE CCCit (Productor,NºLote) SET [Productor]= mid([NºLote],3,1),[NºLote]= Right([NºLote],1) & Left([NºLote],2) From CCCit Where [nopartida]= '" & Me.nopartida & "'"

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial