VBA SQL string with date variable 2

[robcarr]

Hi,

I am trying to import data into excel and I am using a DAO copy function to get the data, but I am having a problem with dates, this is what i have tried with the sql string

'strSQL = "SELECT * FROM tblBPData WHERE ((Date)=date()-4);" ' this works
'strSQL = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=date()-4);" 'this works
'strSQL = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=#" & datefield & "#);" ' this doesnt
'strSQL = "SELECT * FROM " & TableName & " WHERE (([" & FieldName & "])=#" & datefield & "#);" ' this doesnt work
strSQL = "SELECT * FROM " & TableName & " WHERE (([" & FieldName & "])=" & datefield & ");"' this doesnt work

the first strsql is taken diretly from an access database and works fine when I use it in excel vba, and pulls the correct data.
second is modified to use the tablename variable and works fine and pulls the data.

as soon as I try to use the date part as a variable it falls over.

date has been defined as date variable or string variable and it still fails




Hope this is of use, Rob.

 

[SkipVought]

hi,

What is the value in datefield?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robcarr]

datefield is the value that appears in c1 on the spreadsheet, c1 is entered as a uk date on the spreadsheet



Hope this is of use, Rob.

 

[Andrzejek]

Also, if you would include what you get when you state:

[tt]
Debug.Print strSQL
[/tt]
That would help (you ) a lot.

Have fun.

---- Andy

 

[SkipVought]

datefield is the value that appears in c1 on the spreadsheet, c1 is entered as a uk date on the spreadsheet

Your Excel sheet display format mean ABSOLUTELY NOTHING!

The underlying value of a Real Date is A NUMBER, like today's Date Serial Number is 41137.

But you still have not provided all the information that is required.

How have you declared datefield?

Hwo have you assigned datefield?

If you change the Number Format of cell C1 in Excel to GENERAL, what happens to the value that you see in C1?

Please answer each of these questions completely.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robcarr]

skip,

I am getting concerened at all your answers on my threads, they are always in an aggressive tone, I am sorry I dont put the words/descrption down how you like it, I do my best.

I had stated the date variable was set as date, and i tried it as a string

"date has been defined as date variable or string variable and it still fails"

the c1 value is a proper date, and when i change it to a general i get the date serial appear 41133 (12/08/12).




Hope this is of use, Rob.

 

[robcarr]

debug prints from the coding

this is the first sql string

SELECT * FROM tblBPData WHERE ((Date)=date()-4);

this works

this also works the second string

SELECT * FROM tblBPData WHERE ((Date)=date()-4);

this doesnt work

strSQL = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=#" & datefield & "#);"

and looks like this in debug print

SELECT * FROM tblBPData WHERE ((Date)=#12/08/2012#);

here is the full coding that I am using

Sub GetDataWithDAO()
Range("a2", Range("ap65536").End(xlUp).Offset(1, 0)).ClearContents

DAOCopyFromRecordSet "U:\Private\SKEP Source\Skep Source.mdb", _
    "tblBPData", "Date", Range("a2"), Range("c1").Value
    
End Sub

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
    FieldName As String, TargetRange As Range, datefield As String)
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
    "TableName", "FieldName", Range("C1")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    Set db = OpenDatabase(DBFullName)
    Dim strSQL As String
'strSQL = "SELECT * FROM tblBPData WHERE ((Date)=date()-4);" ' this works
'strSQL = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=date()-4);" 'this works
strSQL = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=#" & datefield & "#);" ' this doesnt
 'strSQL = "SELECT * FROM " & TableName & " WHERE (([" & FieldName & "])=#" & datefield & "#);" ' this doesnt work
' strSQL = "SELECT * FROM " & TableName & " WHERE (([" & FieldName & "])=" & datefield & ");" ' this doesnt work
Debug.Print strSQL
 
Set rs = db.OpenRecordset(strSQL, dbReadOnly)
    
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

I have left the various sql strings in just so I can see what i have tried.

Date column within the access database is defined as a shortdate.





Hope this is of use, Rob.

 

[robcarr]

changed the coding so you can see all debug prints

strSQL = "SELECT * FROM tblBPData WHERE ((Date)=date()-4);" ' this works
strSQL1 = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=date()-4);" 'this works
strSQL2 = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=#" & datefield & "#);" ' this doesnt
strSQL3 = "SELECT * FROM " & TableName & " WHERE (([" & FieldName & "])=#" & datefield & "#);" ' this doesnt work
strSQL4 = "SELECT * FROM " & TableName & " WHERE (([" & FieldName & "])=" & datefield & ");" ' this doesnt work
Debug.Print strSQL
Debug.Print strSQL1
Debug.Print strSQL2
Debug.Print strSQL3
Debug.Print strSQL4

immediate window

SELECT * FROM tblBPData WHERE ((Date)=date()-4);
SELECT * FROM tblBPData WHERE ((Date)=date()-4);
SELECT * FROM tblBPData WHERE ((Date)=#12/08/2012#);
SELECT * FROM tblBPData WHERE (([Date])=#12/08/2012#);
SELECT * FROM tblBPData WHERE (([Date])=12/08/2012);


Hope this is of use, Rob.

 

[SkipVought]

Problem is that Bill Gates' company is in Washington State, USA.

Although your Excel date is formatted as d/m/yyyy in some form, the ## conversion assumes that your date string is m/d/yyyy.

Therefore, use the Format() function to either return a yyyy/mm/dd string (preferred) or a mm/dd/yyyy string in your SQL, like...

strSQL = "SELECT * FROM " & TableName & " WHERE ((" & FieldName & ")=#" & [highlight]Format(datefield,"yyyy/mm/dd")[/highlight] & "#);"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

And I would strongly suggest to avoid using reserved words as field names in your table, like Date

Have fun.

---- Andy

 

[robcarr]

will look to change the reserved word for field names, thanks for reminding me Andrzejek.

Skip it finally works, thanks for this.

Rob.

Hope this is of use, Rob.