Excel Query Update Using VBA 1

[Lagoon1]

Hello
I need some help with trying to update a query that I created in Excel. As the amount of data that I extract periodically each time is quite huge, therefore, I have to add a criteria in query to filter via Month (Period). This requires constantly going into the query table to change criteria data. Are there any ways of updating the criteria field by by referencing it against a cell in excel?

Below is a brief code that i created in vbe to try to change the criteria but instead of having the value in range("a1&quot transposed to the criteria field, the text periodic is transposed to the criteria field instead.
I have deleted some of the commands so that it would not look too long. Have just included one of the line items for your reference.


It would be greatly appreciated if someone would be able to help out.

Thanks!

Cheers,

Lagoon1.


Sub Update
periodic = Range("A1&quot
With Selection.QueryTable
.Connection = .....................
.Sql = Array(......................
(FLP004.PSTPER= 10406) ' This is where the criteria field that needs to reference to the variable declared.

.Refresh False
End With
End Sub

 

[SkipVought]

Hi,

Assuming that you have a variable MyValue...

Sub Update()
periodic = Range("A1")
With Selection.QueryTable
.Connection = MyConnectString
.Sql = "Select bla1, bla2, From MyTable Where (FLP004.PSTPER=" & MyValue & ") "
.Refresh False
End With
End Sub

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

Skip,

http://www.TheOfficeExperts.com

 

[Lagoon1]

Hi SkipVought
It works perfectly fine. Thanks very much for your valuable directions. Greatly appreciated and helps a lot.

Although, the command works well, I would like to understand a bit more about what the command that you have provided.

Why do we have to put exclamation marks and concatenate the & and the variable?

I.e. " & MyValue & "

Thanks again for your valuable information.

Kind Regards,

Lagoon1.

 

[SkipVought]

Lagoon1,

No exclamation marks -- just quotes and ampersands.

You are constructing a string containing your SQL code. In reality, this applies to ANY string.

When you want to incorporate a variable into a string, you can do it in several ways...

1. Build it up

sQuery = "Select bla1, bla2, From MyTable Where (FLP004.PSTPER=" 
sQuery = sQuery & MyValue 
sQuery = sQuery & ") "

2. Concatenate the segments...

sQuery = "Select bla1, bla2, From MyTable Where (FLP004.PSTPER=" & MyValue & ") "

This is the way that I prefer to use usually, in fact, I might even do this to aid in the understandability and maintainability...

sQuery = "Select bla1, bla2, " & _ 
         "From MyTable " & _ 
         "Where (FLP004.PSTPER=" & MyValue & ") "

Each literal string must be enclosed in quotes (&quot and each string segment ca be concatenated using the ampersand (&)

Hope this helps



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

Skip,

http://www.TheOfficeExperts.com

 

[Lagoon1]

Hi SkipVought
Thats great.

I've got a much better view now.

Thanks again for your prompt response and all the great information that you have provided.

Kind regards,

Hugo

 

[Lagoon1]

Hi SkipVought
I've recently come across another issue which is quite similar to the previous question that I've asked you so I thought that its probably a better idea to follow up with you in this instant.

The problem that I am facing are listed as follows:-

Sql.=
Select distinct a,b,c,d
from ......
where.........

Distinct applies to all the fields that I've selected.
Can I omit "d" from being distinct?

The above is the sql property from Microsoft Query and by selecting unique records from the edit list, it inserts the distinct function into the sql property.

Is it possible to manually override the command?

Please let me know if you have any queries.

Thanks.

Kind Regards,

Lagoon1.

 

[SkipVought]

Lagoon1,

Please post this as a NEW THREAD.

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

Skip,

http://www.TheOfficeExperts.com