excel - sql
 

hi guys

Need help. This is my problem.
I would like to work in excel with data from MS Access. So I write macro,
use Microsoft Query etc. Everything works OK, but I'd like to have
possibility to change the data range from cells of excel. What I mean...

Below is a example of SQL query:

SELECT CALKOWITE.DATA, CALKOWITE.CZAS_PRACY_CALK,
CALKOWITE.CZAS_POSTOJU_CALK, CALKOWITE.DET
FROM `C:\Dynamics\COBRA\daneM1`.CALKOWITE CALKOWITE
WHERE (CALKOWITE.DATA={ts '2006-02-22 06:59:30'})
ORDER BY CALKOWITE.DATA

In section WHERE I have to write specific data (in this case - date)
Is there a possibility that I could use there one cell from excel?
I tried insert something like this:
'"Range("B1").Value"'
or
&Cells(3, 6).Value&

It does not work

Please help.

Thank you in advance

roody

excel - sql
 

You can do it, you just need to make sure the SQL result stays the same

so for example

SELECT AgeBal.DateOn FROM AgeBal WHERE (((AgeBal.DateOn)#5/1/2006#));

is a valid SQL string - using the date from the spreadsheet would give
something like
MySQL="SELECT AgeBal.DateOn FROM AgeBal WHERE (((AgeBal.DateOn)#" &
format(range("B1").value,"m/d/yyyy") & "#));"

excel - sql
 

roody,
Check the Excel Help for "parameter_query".

NickHK

"roody" wrote in message
...
hi guys

Need help. This is my problem.
I would like to work in excel with data from MS Access. So I write macro,
use Microsoft Query etc. Everything works OK, but I'd like to have
possibility to change the data range from cells of excel. What I mean...

Below is a example of SQL query:

SELECT CALKOWITE.DATA, CALKOWITE.CZAS_PRACY_CALK,
CALKOWITE.CZAS_POSTOJU_CALK, CALKOWITE.DET
FROM `C:\Dynamics\COBRA\daneM1`.CALKOWITE CALKOWITE
WHERE (CALKOWITE.DATA={ts '2006-02-22 06:59:30'})
ORDER BY CALKOWITE.DATA

In section WHERE I have to write specific data (in this case - date)
Is there a possibility that I could use there one cell from excel?
I tried insert something like this:
'"Range("B1").Value"'
or
&Cells(3, 6).Value&

It does not work

Please help.

Thank you in advance

roody

excel - sql
 

It works!

thank you very, very much

roody

Uzytkownik napisal w wiadomosci
ups.com...
You can do it, you just need to make sure the SQL result stays the same

so for example

SELECT AgeBal.DateOn FROM AgeBal WHERE (((AgeBal.DateOn)#5/1/2006#));

is a valid SQL string - using the date from the spreadsheet would give
something like
MySQL="SELECT AgeBal.DateOn FROM AgeBal WHERE (((AgeBal.DateOn)#" &
format(range("B1").value,"m/d/yyyy") & "#));"

excel - sql
 

try with this:
Dim d As Date

d = Range("A1").Value

Dim strDate As String

'If your sql engine support this type of string date '2006-10-28 00:00:00'

strDate = DatePart("yyyy", d) & "-" & DatePart("m", d) & "-" & _
DatePart("d", d) & " " & DatePart("h", d) & ":" & DatePart("n", d) & ":" & _
DatePart("s", d)

'Then you can use strDate as parameter

Cecco (ITA)

"roody" wrote:

hi guys

Need help. This is my problem.
I would like to work in excel with data from MS Access. So I write macro,
use Microsoft Query etc. Everything works OK, but I'd like to have
possibility to change the data range from cells of excel. What I mean...

Below is a example of SQL query:

SELECT CALKOWITE.DATA, CALKOWITE.CZAS_PRACY_CALK,
CALKOWITE.CZAS_POSTOJU_CALK, CALKOWITE.DET
FROM `C:\Dynamics\COBRA\daneM1`.CALKOWITE CALKOWITE
WHERE (CALKOWITE.DATA={ts '2006-02-22 06:59:30'})
ORDER BY CALKOWITE.DATA

In section WHERE I have to write specific data (in this case - date)
Is there a possibility that I could use there one cell from excel?
I tried insert something like this:
'"Range("B1").Value"'
or
&Cells(3, 6).Value&

It does not work

Please help.

Thank you in advance

roody