Query with dates

[daniel aristidou]

Hi im a bit stuck on querying with dates. ive got a databse that querries using sql in visual basic 6.

I need to select only the dates which have passed or are today.
But the query returns all the records regardless of if the date has not passed

eg

todaydate = format(date, "dd/mm/yyyy")
frmCars.Cars_Data.RecordSource = "select *  from car_Rentals WHERE  (Format(due_service,'dd/mm/yyyy') >= " & "'" & todaydate & "') And (Format(due_service,'dd/mm/yyyy') = " & "'" & todaydate & "')"
frmcars.cars_data.refresh

I also tried like this

todaydate = format(date, "dd/mm/yyyy")
frmCars.Cars_Data.RecordSource = "select *  from car_Rentals WHERE  (Format(due_service,'dd/mm/yyyy') > " & "'" & todaydate & "') And (Format(due_service,'dd/mm/yyyy') = " & "'" & todaydate & "')"
frmcars.cars_data.refresh

And like this

todaydate = format(date, "dd/mm/yyyy")
frmCars.Cars_Data.RecordSource = "select *  from car_Rentals WHERE  (Format(due_service,'dd/mm/yyyy') >= " & "'" & todaydate & "'"
frmcars.cars_data.refresh

They all had a result either consisting only of one that was equl to the current date or all of the records.



in advance Thanks ........Extreme ly Thankfull for any tips

[lotus18]

Hi im a bit stuck on querying with dates. ive got a databse that querries using sql in visual basic 6.

I need to select only the dates which have passed or are today.
But the query returns all the records regardless of if the date has not passed

eg

todaydate = format(date, "dd/mm/yyyy")
frmCars.Cars_Data.RecordSource = "select *  from car_Rentals WHERE  (Format(due_service,'dd/mm/yyyy') >= " & "'" & todaydate & "') And (Format(due_service,'dd/mm/yyyy') = " & "'" & todaydate & "')"
frmcars.cars_data.refresh

I also tried like this

todaydate = format(date, "dd/mm/yyyy")
frmCars.Cars_Data.RecordSource = "select *  from car_Rentals WHERE  (Format(due_service,'dd/mm/yyyy') > " & "'" & todaydate & "') And (Format(due_service,'dd/mm/yyyy') = " & "'" & todaydate & "')"
frmcars.cars_data.refresh

And like this

todaydate = format(date, "dd/mm/yyyy")
frmCars.Cars_Data.RecordSource = "select *  from car_Rentals WHERE  (Format(due_service,'dd/mm/yyyy') >= " & "'" & todaydate & "'"
frmcars.cars_data.refresh

They all had a result either consisting only of one that was equl to the current date or all of the records.



in advance Thanks ........Extreme ly Thankfull for any tips

Try to use Between...And Operator

[creative1]

use # instead of & around date fields

[debasisdas]

I need to select only the dates which have passed or are today.

as per your statment , what about using OR operator instead of AND in the sql statment.

[daniel aristidou]

as per your statment , what about using OR operator instead of AND in the sql statment.

Thanks for your replies ill try them out thanks il psot again if ang problems
Ps i did you or as well........ i reckd my brains out lookin for solution

[daniel aristidou]

Hi guys......
I tried to change the & to # but this did not work it kept on comming up with error messages. Can someone please post the code that is correct cause im also confused as to which bit i have to apply the #.

Thanks.....
Also regarding the post mentioning between. i tried it but am not familiar with how it works.

[daniel aristidou]

Hi guys......
I tried to change the & to # but this did not work it kept on comming up with error messages. Can someone please post the code that is correct cause im also confused as to which bit i have to apply the #.

Thanks.....
Also regarding the post mentioning between. i tried it but am not familiar with how it works.

Ps please note this is for my a level so only teech me how to put the # in ect
Thanks

[lotus18]

Hi Daniel

Does your due_service and todaydate are of both date datatypes? Try to omit the format function then in the general tab, format property of due_service select shortdate. : )