Inner join on Memo fields (1 Viewer)

SkyCraw · Apr 9, 2014

Good morning everyone!

I've got an issue pertaining to inner joins within an update query. This query will not run now (giving the expected "must use an updateable query" error) due to two fields involved in one of the inner joins are now both memo fields.

Is there any way (and I assume there must be in vba) to recreate this query so it will run with inner joins involving memo fields?

If not, is there a way in vba to do a find and replace within a table? For example, replace the "A" in "A-001" with "B"?

If either is possible, let me know!

pr2-eugin · Apr 9, 2014

Find and Replace should not be a problem. Give some example data (actual data), because the one you have would be way too simple.

Code:

Replace("A-001", "A", "B")

SkyCraw · Apr 9, 2014

In that case, what I'll be replacing is our company's model numbers based on a value in a control box so, from a data perspective, it would look more like the following:

Code:

Replace("PD15-001", "PD15", "PD12")

However, from a vba perspective, I was intending on doing something like this:

Code:

Dim OMN, NMN as string

OMN = [Forms]![thisform]![old model no]
NMN = [Forms]![thisform]![new model no]

DoCmd.OpenTable "thistable"

Replace([Option No], OMN, NMN)

DoCmd.Close acTable, "thistable"

pr2-eugin · Apr 9, 2014

No you would be doing something like.

Code:

CurrentDB.Execute "UPDATE thistable SET thistable.[Option No] = Replace(thistable.[Option No], " & _
                                                                        [Forms]![thisform]![old model no] & ", " & _
                                                                        [Forms]![thisform]![new model no] & ");"

SkyCraw · Apr 9, 2014

For some reason, it was having issues with parameters when executing (even after checking all the references and spelling

), but I was able to mimic the SQL execution within an actual query and made it run with no hiccups.

Regardless, thanks for all the help pr2!

pr2-eugin · Apr 9, 2014

Not sure why there should be a problem. Glad you have it sorted though. Good Luck.

Inner join on Memo fields (1 Viewer)

SkyCraw

Registered User.

pr2-eugin

Super Moderator

SkyCraw

Registered User.

pr2-eugin

Super Moderator

SkyCraw

Registered User.

pr2-eugin

Super Moderator

Similar threads

Users who are viewing this thread