date varable

[bijan]

Hi All,
I wonder how to set a date varable for my sqlstring , I tried this :
dim sqlstring as string
vardate1=format(me.textbox1.value, "MMMM DD YYYY")
and after connection in criteria part of my sqlstring =" where something <>
'vardate1'"
debugger show me that the varable filled with certain date but with string
type not date.
any help would be appreciated.

 

[Daniel.C]

Try :
" where something <> " & vardate1
Regards.
Daniel

 

[ShaneDevenshire]

Hi,

Try

CDate(expression)

around your text date. Check the VBA Help system for "Type Conversion
Functions"

 

[bijan]

Hi
Thanks for help, but I haven't overcome the problem yet,I thought is better
to Post the vba code to find out what is wrong in it:
Private Sub CommandButton1_Click()
Dim a As String
vara = Worksheets("MAIN").Range("A5") ' defiend as custom/date "DD MMM YYYY"
varb = Worksheets("MAIN").Range("A6") ' defiend as custom/date "DD MMM YYYY"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open = "Provider=MSDAORA.1;Password=Mypass;User ID=TEST;Data
Source=DBTEST;Persist Security Info=True"
a = " SELECT TBL1.NAME, TBL2.LID" & _
" FROM TBL1, TBL2" & _
" WHERE (TBL1.NO=1" & _
" AND TBL2.BLD<>'vara'" & _
" AND TBL2.LID<>'varb')" & _
" ORDER BY TBl1.NAME ASC"
Set rs = conn.Execute(a, , 1)
Worksheets("MAIN").Range("A10").CopyFromRecordset rs
rs.Close
conn.Close
End Sub

 

[Daniel.C]

Hi.
You can't include variables inside quotes. Try :
" AND TBL2.BLD<>'" & vara & "'" & _
instead of
" AND TBL2.BLD<>'vara'" & _
etc.
Daniel

 

[bijan]

Thanks Daniel,
It works perfetc!!!

Hi.
You can't include variables inside quotes. Try :
" AND TBL2.BLD<>'" & vara & "'" & _
instead of
" AND TBL2.BLD<>'vara'" & _
etc.
Daniel