ExcelBanter - Date Format

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Date Format (https://www.excelbanter.com/excel-programming/436488-date-format.html)

bijan

Date Format

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

Date Format

Hi Bijan

Try this:

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

Regards,
Per

"bijan" skrev i meddelelsen
...
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

Jacob Skaria

Date Format

Try without single quotes..

" WHERE tst.valid_date= " & vara

If this post helps click Yes
---------------
Jacob Skaria

"bijan" wrote:

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

bijan

Date Format

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

"Jacob Skaria" wrote:

Try without single quotes..

" WHERE tst.valid_date= " & vara

If this post helps click Yes
---------------
Jacob Skaria

"bijan" wrote:

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

bijan

Date Format

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
"Per Jessen" wrote:

Hi Bijan

Try this:

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

Regards,
Per

"bijan" skrev i meddelelsen
...
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

.

Jacob Skaria

Date Format

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

"bijan" wrote:

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

"Jacob Skaria" wrote:

Try without single quotes..

" WHERE tst.valid_date= " & vara

If this post helps click Yes
---------------
Jacob Skaria

"bijan" wrote:

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

All times are GMT +1. The time now is 03:34 AM.