INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...keep up the good work with this forum, I think this is the best one around. ...you actually try to help people learn for themselves. ...I commend you on providing a very good, open learning atmosphere, where usually egos are left behind..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

VBA SQL string with date variable (2)

Read More Threads Like This One

robcarr (Programmer)

16 Aug 12 6:29

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. yoda

SkipVought (Programmer)

16 Aug 12 8:31

hi,

What is the value in datefield?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

robcarr (Programmer)

16 Aug 12 8:43

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. yoda

Andrzejek (Programmer)

16 Aug 12 8:49

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

Debug.Print strSQL

That would help (you ) a lot.

Have fun.

---- Andy

SkipVought (Programmer)

16 Aug 12 8:59

Quote:
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,
_{Just traded in my old subtlety...

for a NUANCE!}

robcarr (Programmer)

16 Aug 12 9:14

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. yoda

robcarr (Programmer)

16 Aug 12 9:20

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

CODE

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. yoda

robcarr (Programmer)

16 Aug 12 9:23

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. yoda

SkipVought (Programmer)

16 Aug 12 9:36

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...

CODE

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

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Andrzejek (Programmer)

16 Aug 12 10:05

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

Have fun.

---- Andy

robcarr (Programmer)

20 Aug 12 3:39

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. yoda

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

VBA SQL string with date variable (2)

Quote:

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!