Date Format

[bijan]

Hi all,
How can I define a correct date format for varialbe(vara) in this code:
Dim A As String
vara = DateValue(Me.TextBox1.Value)
dbPath = "T:\REF.dbc"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open = "Provider=vfpoledb;UID=TST;password=12345;Data Source=" &
dbPath
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "GI"
A = " SELECT ALL tst.contract_c,tst.name_f,tst.amount,tst.curren_e," & _
" tst.valid_date;" & _
" FROM tst" & _
" WHERE tst.valid_date>= '" & vara & "'"
Set rs = conn.Execute(A, , 1)
Worksheets("GI").Range("A10").CopyFromRecordset rs
rs.Close
conn.Close
For example if i replace {2007/11/11} instead of '" & vara & '" it works
perfect
Thanks and Regards
Bijan

 

[Per Jessen]

Hi Bijan

Try this:

vara = Format(DateValue(Me.TextBox1.Value),"yyyy/mm/dd")

Regards,
Per

 

[Jacob Skaria]

Try without single quotes..

" WHERE tst.valid_date>= " & vara

If this post helps click Yes

 

[bijan]

Hi jacob,
I have tried it but it doesn't work
Bijan

 

[bijan]

Hi Jessen,
It return variable value as string format ,the problem is how to use or
not {} with variable in the query
Best Regards
Bijan

 

[Jacob Skaria]

--Is the date stored as datetime in DB?
--Is variable 'vara' is declared as Date