Selecting records between two dates

Printable View

Nov 7th, 2002, 05:09 AM
IrelandsOwn

Selecting records between two dates

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
Nov 7th, 2002, 05:12 AM
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
Nov 7th, 2002, 05:13 AM
wrack

Re: Selecting records between two dates
Quote:

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
VB Code:
```
rstChkdisb.Open "SELECT * FROM mytable WHERE ck_date BETWEEN #" & StartDate & "# AND #" & EndDate & "# ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly
```
Cheers...
Nov 7th, 2002, 05:15 AM
wrack

Quote:

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...
Nov 7th, 2002, 05:25 AM
IrelandsOwn

I have tried this but am now getting a syntax error.
Nov 7th, 2002, 05:34 AM
swatty

Show us what you got till now.

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

like wrack mentioned
Nov 7th, 2002, 05:39 AM
wrack

Quote:

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...
Nov 7th, 2002, 05:42 AM
IrelandsOwn

PHP Code:

rstChkdisb.Open "SELECT * FROM mytable WHERE ck_date BETWEEN #" & StartDate & "# AND #" & EndDate & "# ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly
Nov 7th, 2002, 05:51 AM
wrack

Quote:

Originally posted by IrelandsOwn

PHP Code:

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

What error r u getting...

This we knew.

Can you change your code like this
VB Code:
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

Nov 7th, 2002, 06:31 AM
IrelandsOwn

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

[Microsoft][ODBC Visual FoxPro Driver] Syntax Error
Nov 7th, 2002, 06:57 AM
Lightning

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