Selecting records between two dates

[IrelandsOwn]

I want to select records between two dates from a table.

StartDate and EndDate are two Dates but the code below does not seem to work. I get an operator/operand type mismatch.

rstChkdisb.Open "select * from mytable where ck_date >= " & StartDate & " And ck_date <= " & EndDate & " ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly

[Lightning]

Try this:
rstChkdisb.Open "select * from mytable where ck_date >= '" & StartDate & "' And ck_date <= '" & EndDate & "' ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly
with a ' befor and after the dates. And it is better to use between:

rstChkdisb.Open "select * from mytable where ck_date between '" & StartDate & "' '" & EndDate & "' ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly

[wrack]

Originally posted by IrelandsOwn
I want to select records between two dates from a table.

StartDate and EndDate are two Dates but the code below does not seem to work. I get an operator/operand type mismatch.

rstChkdisb.Open "select * from mytable where ck_date >= " & StartDate & " And ck_date <= " & EndDate & " ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly

rstChkdisb.Open "SELECT * FROM mytable WHERE ck_date BETWEEN #" & StartDate & "# AND #" & EndDate & "# ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly

Cheers...

[wrack]

Originally posted by Lightning
Try this:
rstChkdisb.Open "select * from mytable where ck_date >= '" & StartDate & "' And ck_date <= '" & EndDate & "' ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly
with a ' befor and after the dates. And it is better to use between:

rstChkdisb.Open "select * from mytable where ck_date between '" & StartDate & "' '" & EndDate & "' ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly

when u r dealing with date u need use # instead of '

Cheers...

[IrelandsOwn]

I have tried this but am now getting a syntax error.

[swatty]

Show us what you got till now.

to select between two dates you can use the between operator instead.

like wrack mentioned

[wrack]

Originally posted by IrelandsOwn
I have tried this but am now getting a syntax error.

post the code u have here and use vbcode tags...

Cheers...

[IrelandsOwn]

rstChkdisb.Open "SELECT * FROM mytable WHERE ck_date BETWEEN #" & StartDate & "# AND #" & EndDate & "# ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly 

[wrack]

Originally posted by IrelandsOwn

rstChkdisb.Open "SELECT * FROM mytable WHERE ck_date BETWEEN #" & StartDate & "# AND #" & EndDate & "# ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly 

What error r u getting...

[swatty]

This we knew.

Can you change your code like this

strSQL = "SELECT * FROM mytable WHERE ck_date BETWEEN #" & StartDate & "# AND #" & EndDate & "# ORDER BY ck_no"
 
Debug.Print strSQL
' 
rstChkdisb.Open strSQL, Cnn2, adOpenStatic, adLockReadOnly

And show us what is printed in the immediate pane

[IrelandsOwn]

Run Time Error '-2147217900 (80040e14)':

[Microsoft][ODBC Visual FoxPro Driver] Syntax Error

[Lightning]

try the ' in stead of the #.Maybe that will help.