CODEWITHSUNDEEP
sqlms-access
user1787114
user1787114

Reputation: 205

take numeric values from text

i have values such as 1st 2nd 3rd 4th how would use an sql statement to just take the numbers and put them in a table so that i just have 1,2,3,4

right now i am trying this code below

DoCmd.RunSQL "UPDATE DEDValidation LEFT JOIN ReqInput ON DEDValidation.[Plan Code] = ReqInput.[Plan Code] SET DEDValidation.[Expected Plan Effective Day] =ReqInput.[Start Day] WHERE DEDValidation.[DED Row Found] = 'Yes'"
DoCmd.RunSQL "Update DEDValidation Set DEDValidation.[Expected Plan Effective Day] = REPLACE(DEDValidation.[Expected Plan Effective Day], '*st', '') WHERE DEDValidation.[Expected Plan Effective Day] LIKE '*st'"

the first line imports the code and the second is supposed to replace the letters 'st' with a blank, but its not working

Upvotes: 0

Views: 30

Answers (1)

ericpap
ericpap

Reputation: 2937

Try this. Change the Replace function for this:

REPLACE(DEDValidation.[Expected Plan Effective Day], 'st', '')

Hope it helps you!

Upvotes: 1

Related Questions