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

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") & "#));"

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

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") & "#));"

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