Query Update problem.

[glavster]

Hello I'm trying to do an update query by compairing certain fields. Here is the query so far,

UPDATE [Enhanced-GB] INNER JOIN va_categories ON ([Enhanced-GB].SubCategory = va_categories.parent_category_id) AND ([Enhanced-GB].Sub_SubCategory = va_categories.category_name) AND ([Enhanced-GB].Category = va_categories.category_path) SET [Enhanced-GB].Sub_SubCategory = [va_categories].[category_id];

the problem is with this line "AND ([Enhanced-GB].Category = va_categories.category_path)" The [Enhanced-GB].Category is not equal to va_categories.category_path but only to a part of it. It in this format "0,1,2, and [Enhanced-GB].Category is only equal to the 1.

Can anyone tell me how to fix that.

Thanks again,
Paul

 

[lameid]

AND (Split ([Enhanced-GB].Category,",")(1)= va_categories.category_path)

 

[glavster]

Thanks lameid, I get an error when trying to save the query so maybe I've copied your solution wrongly into the query.

This is what I've got now,

UPDATE [Enhanced-GB] INNER JOIN va_categories ON ([Enhanced-GB].SubCategory = va_categories.parent_category_id) AND ([Enhanced-GB].Sub_SubCategory = va_categories.category_name) AND (Split ([Enhanced-GB].Category,",")(1)= va_categories.category_path) SET [Enhanced-GB].Sub_SubCategory = [va_categories].[category_id];

But I get the Error "Invalid use of '.', '!', '()'. in expression".

Thanks,
Paul

 

[lameid]

Looks good to me but Access can be finiky about non-equijoins... Try this... Watch the records to update before you click through, just in case.

UPDATE [Enhanced-GB] 
INNER JOIN va_categories 
ON ([Enhanced-GB].SubCategory = va_categories.parent_category_id) 

AND ([Enhanced-GB].Sub_SubCategory = va_categories.category_name) 


SET [Enhanced-GB].Sub_SubCategory = [va_categories].[category_id]

Where (Split ([Enhanced-GB].Category,",")(1)= va_categories.category_path) 
;

 

[glavster]

Just a quick question, is this not splitting the wrong field. It's the va_categories.category_path that has the format 0,1,2,

Thanks,
Paul

 

[lameid]

Yes it is wrong then... I read your OP the other way where [Enhanced-GB].Category had the data. You can just swap the fields to fix it.

 

[glavster]

Thanks but I still get the Error, but this time while trying to run the query.

Paul

 

[lameid]

Does it by chance put the cursor somewhere in the SQL view? Try running it from SQL view... I might just not be seeing it. Also you could try another set of parentesis aroung the expression that is getting you to the second element between the commas.

 

[glavster]

No the cursor stays at the end. Sorrt but the parentesis did not work either.

Paul

 

[lameid]

Oops... I thought I saw split used in a query before. Most have been wrong.

Put this code in a module...

Function SecondCommaValue(strIn As String) As String
    SecondCommaValue = Split(strIn, ",")(1)
End Function

UPDATE [Enhanced-GB] 
INNER JOIN va_categories 
ON ([Enhanced-GB].SubCategory = va_categories.parent_category_id) 

AND ([Enhanced-GB].Sub_SubCategory = va_categories.category_name) 


SET [Enhanced-GB].Sub_SubCategory = [va_categories].[category_id]

Where [Enhanced-GB].Category = SecondCommaValue(va_categories.category_path) 
;

Sorry for the half baked solution before. -

 

[glavster]

Excellent, that looks like it works. I'll have to start running the queries from scratch to be sure as the tables are a little messed up at the moment but I think it's a go.

Thanks again,
Paul

 

[glavster]

Hello again lameid, I'm afraid i've run into problems with the module. It worked up untill I linked the tables in MS Access with the tables in the MySQL database. When I run it I get the following error.

Run-time error '9':
Subscript out of range.

And when I run the debug it highlights this line in the module,

SecondCommaValue = Split(strIn, ",")(1)

So do you have any idea how I could fix this.

Thanks,
Paul

 

[glavster]

btw lameid, i know it's hard to get the split function to work in queries but I've read that you could use a simple split using the the Left(), Mid(), Right()and if that fails you can use the wrapper function. The thing is I can't get them to work.

Paul

 

[lameid]

Do you have your MYSQL tables linked in Access?

Are you using those tables in Access like they are native (can you go to design view or are you locked in SQL view because it is an SQL pass-through query)?

If you are not using the tables as if they are native, you have to get at the value the way MySQL does. Unfortunately I know nothing about that variant of SQL. On the other hand, it should simply work otherwise.

If you wanted not to use the split function you would have to use INSTR to find the positions of the first two commas and use that to pull out the value using Mid.

 

[glavster]

Do you have your MySQL tables linked in Access?
YES

I can view the tables in design view but I have to do any editing from the original source.

If you wanted not to use the split function you would have to use INSTR to find the positions of the first two commas and use that to pull out the value using Mid.
CAN YOU EXPLAIN THIS A BIT MORE.

Thanks,
Paul

 

[lameid]

I'm sorry I meant to say design the query...

If so it should just work...
But another way to go...

Function SecondCommaValue(strIn As String) As String
    Dim intFirstComma As Integer
    Dim intSecondComma As Integer
    intFirstComma = InStr(1, strIn, ",")
    If intFirstComma = 0 Then
        SecondCommaValue = ""
    Else
        intSecondComma = InStr(intFirstComma + 1, strIn, ",")
        If intSecondComma = 0 Then
            SecondCommaValue = Right(strIn, Len(strIn) - intFirstComma)
        Else
            SecondCommaValue = Mid(strIn, intFirstComma + 1, intSecondComma - intFirstComma - 1)
        End If
    End If
    [green]'SecondCommaValue = Split(strIn, ",")(1)[/green]
End Function

 

[glavster]

That looks like it is working but the only thing is it takes so long to run the queries when the tables are linked to MySQL on a remote server.

I'm wondering if there is a faster way of doing this.

Thanks again,
Paul

 

[lameid]

You could use and SQL Pass-through query... A Query that passes a statement directly to the server. The catch here is you have to write a valid MYSQL statement... and I don't know the MYSQL variant... It likely supports a way to do this. You'd have to ask a MYSQL Guru or catch someone here that knows both.

 

[glavster]

OK lameid thanks for the help. I'll let this run first and see how it's working and then I'll work on SQL Pass-through.

Thanks,
Paul

 

[glavster]

Hello again lameid, I've posted about the SQL pass through queries and I'm hoping I'll get some help soon. But I wonder if you could help me with one other issue. I get an error when running one query, well it's not really an error but it means that not all entries are updated. Attached is a screenshot of the MS Access popup message.

I was wondering what I should do first to identify the problem.

Thanks,
Paul