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

 

[Lagoon1]

Hi PHV
I havent had the chance to test it out yet as I've left the file back in the office.

I will keep you posted once i get around it next week.

But nevertheless, I would like to thank you for your tireless efforts in trying to solve the problem.

Thanks very much.

Kind Regards,

Lagoon1

 

[SkipVought]

lagoon,

Looks like PH has helped you a whole lot. Don't forget to Thank PHV for this valuable post!

Let me add a small tip.

In the QueryTable get rid of the Arrays. They make things confusing. I like to assign the SQL string to a varaible (like you did with the connect string) As a debug tool, you could even assign the sSQL variable to a Cell and observe your SQL.
[tt]
sSQL = "SELECT fl.company, fl.ACCN04, CHART.SHTDSC, fl.period, NPDR04+NPCR04, FYDR04+FYCR04 " & _
"FROM {oj PACB1.OSLGLF3CFW.fl fl LEFT OUTER JOIN PACB1.OSLGLF3CFW.CHART CHART ON fl.ACCN04 = CHART.ACCN03} " & _
"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.PSTPER= " & 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.PSTPER= " & myvalue & &quot)"
'assign SQL to a cell for observation
[IV1] = sSQL
With Selection.QueryTable
.Connection = "ODBC;DSN=AC;"
.Sql = sSQL
.Refresh True
End With
[/tt]
I also added parentheses around the OR statements. This may or may not be needed. Depends how you need the logic to work.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I think that the Like statements ought to be formatted
[tt]
"AND (fl.ACCN04 Like ' " & myvalue1 & "%') " & _
[/tt]
with the % in the literal not in the variable

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884