match two date record

[fyanym]

i need to find some record in mysql database, how to i write sql statement to select record between two date...

example:

i want to select record in database from 01/01/2005 to 31/12/2005

[buntine]

Something like this:

SELECT * FROM tableName WHERE dateField BETWEEN #01/01/2005# AND #31/01/2005#;

Regards.

[fyanym]

thanx ! how about match with datefrom, dateto, timefrom and time to...


if match with date is like this rite?
strSQL= "SELECT * FROM bill WHERE date1 BETWEEN '"& datefrom &"' AND '"&dateto &"'"

[buntine]

Nearly. You need to use hash instead of single-quote when dealing with dates in MS Access.

strSQL = "SELECT * FROM bill WHERE date1 BETWEEN #"& datefrom &"# AND #" & dateto & "#;"

Thats should work.

Regards.

[fyanym]

thanx...i use mysql as database...so it work well.
somebody can help which match with date and also time?

datefrom
dateto
timefrom
timeto

[buntine]

Oh ok. Thats fine.

You should be able to use the BETWEEN ... AND clause with time, also.

strSQL= "SELECT * FROM bill WHERE date1 BETWEEN '" & datefrom & "' AND '" & dateto & "' AND time1 BETWEEN '" & timefrom & "' AND '" & timeto & "';"

Regards.

[fyanym]

thanx very much!!!

[fyanym]

i use mysql database, and the date1 and time1 field i use varchar, so it cant match the data sharply, i try to use cDATE, but its doesnt work too...what cant i do?

rs.open "SELECT * FROM bill WHERE date1 BETWEEN '" & cDate(datefrom) & "' AND '" & cDate(dateto) & "' AND time1 BETWEEN '" & cDate(timefrom) & "' AND '" & cDate(timeto) & "'",con,3,2

[buntine]

Change them to date and time fields if at all possible.

[wmif]

i use mysql database, and the date1 and time1 field i use varchar, so it cant match the data sharply, i try to use cDATE, but its doesnt work too...what cant i do?

rs.open "SELECT * FROM bill WHERE date1 BETWEEN '" & cDate(datefrom) & "' AND '" & cDate(dateto) & "' AND time1 BETWEEN '" & cDate(timefrom) & "' AND '" & cDate(timeto) & "'",con,3,2

this didnt work because you are still passing it as a string to the connection.

[fyanym]

So what i can do? how the sql statement write?