Query Update Parameters 2

[Lagoon1]

Hi
I need some help with my query update in excel via External data.

With Selection.QueryTable
.Connection = "ODBC;DSN=.............;"
.Sql = Array( _................where...........

ACCN Like ' " & myvalue3% & "'

The parameters I select in Microsoft Query for the above field is account number begins with.

However, when the above code is used in VBE, it changes the parameters to Like instead of begins with.

Please kindly assist.

Cheers,

Lagoon1

 

[PHV]

In SQL, LIKE 'A%' means begins with 'A', LIKE '%A' means ends with 'A' and LIKE '%A%' means contains 'A'.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi PH
What you have provided is exactly what I've typed in my sql code but when i go into microsoft query, the parameter seems to have changed to Like instead of begins with.

Are you able to kindly advise?

Thanks very much.

Cheers,

Lagoon1

 

[PHV]

Provided the name of the variable holding the parameter value is myvalue3, I would try this:

ACCN Like '" & myvalue3 & "%'"

instead of:

ACCN Like ' " & myvalue3% & "'

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi PH
Thanks very much for your prompt response. Unfortunately, the code does not work either.

Any ideas?

Cheers,

Lagoon1

 

[PHV]

What are the exact discrepancies ?

 

[Lagoon1]

Well, when i tried to execute the code, the whole line just goes into error form. and, msgbox prompts syntax error with no further details.

Cheers,

hugo

 

[PHV]

Why don't you post the whole line gone into error form ?

 

[Lagoon1]

Hi PHV
Pls see attached.

Thanks.

With Selection.QueryTable
.Connection = "ODBC;DSN=System 21B;"
.Sql = Array( _
Select……………………
From………………………..
Where…………………………
AND (ACCN04 Like ' " & myvalue3% & "') AND (PERIOD= " & myvalue & &quot
.Refresh True
End With

 

[PHV]

AND (ACCN04 Like ' " & myvalue3% & "') AND (PERIOD= " & myvalue & &quot
You didn't apply the correction I suggested you related to the % misplaced.
Perhaps will you try this :
AND (ACCNO4 Like '" & myvalue & "3%') AND (PERIOD=" & myvalue & &quot"


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi PHV
Thanks very much for the prompt response.

Myvalue is the variable that contains the number 3, thus that's why I had not applied the 3 to the %.

Please kindly advise.

Thanks very much.

Regards,

Lagoon1.

 

[Lagoon1]

Hi
Is there someone who might be able to assist?

Thanks very much.

Regards,

lagoon1

 

[PHV]

Have you tried this ?
AND (ACCNO4 Like '" & myvalue & "%') AND (PERIOD=" & myvalue & &quot"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi PHV
The latest code that you've provided seems to be a repeat of the previous code that you've given advice on, with the exception that this time its witht the brackets.

Is this correct?

Thanks very much.

Regards,

Lagoon1

 

[PHV]

Pay carefully attention at this:

(ACCNO4 Like '" & myvalue & "%')
             ^^             ^^^

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Lagoon1]

Hi PHV
Sorry, but still does not work.

Rgds,

lagoon1

 

[PHV]

Can you post the contents of the SQL property just before the Refresh ?

 

[Lagoon1]

Hi PHV
Please find attached codes. Please note that this is the original code before I've applied the suggestions that you've made.

Thanks.

Rgds,

Lagoon1

Sub Refresh_Actuals()
'Actuals Result Update
Dim myvalue As String, myvalue1 As Integer, myvalue2 As Integer, myvalue3 As Integer, myvalue4 As Integer
myvalue = Application.Sheets("Main&quot.Range("C4&quot
myvalue1 = Application.Sheets("Main&quot.Range("H3&quot
myvalue2 = Application.Sheets("Main&quot.Range("H4&quot
myvalue3 = Application.Sheets("Main&quot.Range("H5&quot
myvalue4 = Application.Sheets("Main&quot.Range("H6&quot


With Selection.QueryTable
.Connection = "ODBC;DSN=AC;"
.Sql = Array( _
"SELECT fl.company, fl.ACCN04, CHART.SHTDSC, fl.period, NPDR04+NPCR04, FYDR04+FYCR04" & Chr(13) & "" & Chr(10) & "FROM {oj PACB1.OSLGLF3CFW.fl fl LEFT OUTER JOIN PACB1.OSLGLF3CFW.CHART CHART ON fl.ACCN04 = CH" _
, _
"ART.ACCN03}" & Chr(13) & "" & Chr(10) & "WHERE CHART.company = fl.company AND CHART.ler = fl.ler AND ((fl.company='C2') AND (fl.ler In ('mf','op','co','sa','ic')) AND (fl.ACCN04 Like ' " & myvalue1% & "') AND (fl.PSTP" _
, _
"ER= " & myvalue & &quot OR (fl.company='C2') AND (fl.ler In ('mf','op','co','sa','ic')) AND (fl.ACCN04 Like ' " & myvalue2% & "') AND (fl.period= " & myvalue & &quot OR (fl.company='C2') AND (fl.ler In ('mf','op','" _
, _
"co','sa','ic')) AND (fl.ACCN04 Like ' " & myvalue3% & "') AND (fl.period= " & myvalue & &quot OR (fl.company='C2') AND (fl.ler In ('mf','op','co','sa','ic')) AND (fl.ACCN04 Like ' " & myvalue4% & "') AND (fl.PSTPE" _
, "R= " & myvalue & &quot)&quot
.Refresh True
End With

 

[PHV]

I see some problems with your select clause:
1) Mixing OR and AND without precedence parenthezis will raise unexpected result.
a AND b OR c AND d is not the same as (a AND b) OR (c AND d)
2) Your Like clauses must be constructed like this:

  fl.ACCN04 Like ' " & myvalue2 & "%')
                  !                ^

Are you sure you want the space above the ! ?
say myvalue2=2
Your clause: fl.ACCN04 Like ' 2')
My suggestion: fl.ACCN04 Like ' 2%')
Without the mentionned space: fl.ACCN04 Like '2%'
3) If myvalue is really a string, then you need this:

  fl.period= '" & myvalue & "')
             ^               ^

Anyway it would be helpfull to display the .SQL before the .Refresh to see exactly which query you send.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Lagoon1, thanks for sharing: is your problem solved ?
If so, can you explain to members how ?