error in between operator

[veer]

hello expert
i want to insert the records from one database to another on a particular condition i,e between two dates
if i insert all the records it works fine but i need the records on particular date
but when i insert the records by using between operator then it shows the
"syntax error in query string Date between 12\10\2007 And 14\10\2007"
my query is
squery = "Insert into " & Text4 & " IN '" & Text2 & "' select * from " & Text3 & " IN '" & Text1 & "' where Date Between " & Text5 & " AND " & Text6 & ""

please send some idea
thanks

[QVeen72]

Hi,

Enclose Dates with #

[code=vb]
squery = "Insert into " & Text4 & " IN '" & Text2 & "' select * from " & Text3 & " IN '" & Text1 & "' where Date Between #" & Format(Text5,"d d-mm-yyyy") & "# AND #" & Format(Text6,"d d-mm-yyyy") & "#"
[/code]

PS: dont use Keywords like "Date" as field names..

Regards
Veena

[veer]

hello expert
Its working but when i put two date in text5 and text6 then it search the data from text6 value onwords, i changed the field name date to Ypdate
e.g
text5 = 12\10\2007 and text6 = 14\10\2007
it starts searching from 14\10\2007 and all the values after this date
thanks in advance

Hi,

Enclose Dates with #

[code=vb]
squery = "Insert into " & Text4 & " IN '" & Text2 & "' select * from " & Text3 & " IN '" & Text1 & "' where Date Between #" & Format(Text5,"d d-mm-yyyy") & "# AND #" & Format(Text6,"d d-mm-yyyy") & "#"
[/code]

PS: dont use Keywords like "Date" as field names..

Regards
Veena

[QVeen72]

Hi,

Why do you have 2 "IN" in your SQL Statement..

change it :

[code=oracle]
squery = "Insert into [" & Trim(Text4) & "].[" & Trim(Text2) & "] select * from [" & Trim(Text3) & "].[" & Trim(Text1) & "] Where Date Between #" & Format(Text5,"d d-mm-yyyy") & "# AND #" & Format(Text6,"d d-mm-yyyy") & "#"

[/code]

Correct me if I'am wrong:
Text4: Destination Path
Text2 : Destination Table Name
Text3: Source Path
Text2 : Source Table Name


Regards
Veena