Update with "like"

[krzysiekk]

Hi I have problem with double click event to update table

DoCmd.SetWarnings False
DoCmd.RunSQL "Update scheme_tatm set status = 'R' where tran_reference = List192 and status = 'A'"
Me.List192.Requery

Working fine, but I need setup update: where = tran_reference like List192

but I don't know how built like with List192* content

Please advise

 

[bob fitz]

Hi
You may like to try this:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update scheme_tatm set status = 'R' where ((tran_reference0) Like 'List192*') and status = 'A'"
Me.List192.Requery

 

[krzysiekk]

not working - looking value List192 but List192 is List box where I selection value

 

[bob fitz]

Hi

What is the "Multi Select" property of the list box?

What kind of data is in the bound column of List192 ?

 

[krzysiekk]

is not multi select list

Bound column is order number (text)

 

[bob fitz]

Hi
Can you try this:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update scheme_tatm set status = 'R' where tran_reference ='" & List192 & "' ANDstatus = 'A'"
Me.List192.Requery

 

[krzysiekk]

DoCmd.SetWarnings False
DoCmd.RunSQL "Update scheme_tatm set status = 'R' where tran_reference ='" & List192 & "' ANDstatus = 'A'"
Me.List192.Requery

Working fine but still keeping same rules with my first script

DoCmd.SetWarnings False
 DoCmd.RunSQL "Update scheme_tatm set status = 'R' where tran_reference = List192 and status = 'A'"
 Me.List192.Requery

Working when order number is same in table and select list but I need update some others when order number in table is 08001001 but in select list 08001 thats way I need built LIKE in update.

 

[bob fitz]

Hi

Ok. This seems to be a bit like a man with no eyes leading a blind man but I think we're almost there!
Please try this:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update scheme_tatm set status = 'R' where tran_reference Like '" & Me.List192 & "*' and status = 'A'"
Me.List192.Requery

 

[krzysiekk]

hmmm, when I updating same order number list and table working fine but when is different mean should be working like then still not working I checked with * and with %.

 

[bob fitz]

Hi

Can you try this:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update scheme_tatm set status = 'R' where ((tran_reference Like '" & Me.List192 & "*') and (status = 'A'"
))Me.List192.Requery

 

[krzysiekk]

still nothing

 

[bob fitz]

Sorry, but I have nothing else to offer at the moment.
Let's hope somebody with more knowledge than I have (and there are plenty) will be able to help.

 

[Brianwarnock]

tran_reference Like List192 & '*'

Brian

 

[krzysiekk]

thanks

working fine