Hi all,

I'm not sure if this is the right forum to post in as my Q may b
MSAccess related. I am using an ADO connection to retieve data from a
Access database using Excel VBA - this seems to be functioning properl
(I am able to get data as required).

My problem is modifying my SQL statement - currently I have;


Code
-------------------

.Open "SELECT [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText

-------------------


The above is just a test to see weather or not it worked (and it does)
I want to be able to select the data based on week numbers. The curren
[Date] is in format '24/03/2006' - how do I modify this statement?

TIA, any ideas appreciated......and again someone here may know (bob??

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52953

You could use the VBA DatePart function: DatePart("ww", your date)

"gti_jobert" wrote:


Hi all,

I'm not sure if this is the right forum to post in as my Q may be
MSAccess related. I am using an ADO connection to retieve data from an
Access database using Excel VBA - this seems to be functioning properly
(I am able to get data as required).

My problem is modifying my SQL statement - currently I have;


Code:
--------------------

.Open "SELECT [Part No], [Batch Qty] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText

--------------------


The above is just a test to see weather or not it worked (and it does).
I want to be able to select the data based on week numbers. The current
[Date] is in format '24/03/2006' - how do I modify this statement?

TIA, any ideas appreciated......and again someone here may know (bob??)


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=529538

I've been triyng that;


Code:
--------------------

Function VBAWeekNum(D As Date, FW As Integer) As Integer
VBAWeekNum = CInt(Format(D, "ww", FW))
End Function

--------------------



Code:
--------------------

.Open "SELECT [Part No], VBAWeekNum([Date], 1) AS [Week] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText

--------------------


The code comes up with an error, the coding is wrong - anyone know the
syntax to add a VBA function into an SQL Access Statement?


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=529538

This is the entire Function;


Code
-------------------

Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
'SQL select data
*.Open "SELECT [Part No], " & VBAWeekNum( & "[Date]" &, 1) & " As [Week] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText*
For intColIndex = 0 To rs.Fields.count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(0, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

-------------------

--
gti_jober
-----------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...fo&userid=3063
View this thread: http://www.excelforum.com/showthread.php?threadid=52953

Hi gti_jobert

My tip is like this
DATEPART(ww, your_date_field) AS WEEKNUMBER

Good luck

"gti_jobert" wrote:


This is the entire Function;


Code:
--------------------

Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
'SQL select data
*.Open "SELECT [Part No], " & VBAWeekNum( & "[Date]" &, 1) & " As [Week] FROM [" & TableName & "] " & _
" WHERE [Part No] = '01801-00408'", cn, , , adCmdText*
For intColIndex = 0 To rs.Fields.count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(0, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=529538

The problem is this line:
"SELECT [Part No], " & VBAWeekNum( & "[Date]" &, 1) & " As [Week] ... etc

The SQL statement will be executed by the driver but it contains VBAWeekNum
which is a UDF and therefore inaccessible by the driver.

Try:

"SELECT [Part No], DATEPART('WW',[Date]) As Week ... etc

DATEPART is part of the driver'w dialect.