MS Access - Between Dates queries using variables from forms

[sheilasabado]

I have a table with [Call Start] as Date/Time General Date which has data from March 30, 2010 until May 9, 2010. I want to create a query wherein I can get a date range from user given starting and ending date from a Form (GenForm):

User enters ff value in GenForm:

StartDate = 07-Apr-10
EndDate = 09-May-10

This is my query in MS Access Design View

Field: Format([Call Start],"Short Date")
-----------------------------------------------------------------
Criteria: Between Format([Forms]![GenForm]![StartDate], "Short Date") And Format([Forms]![GenForm]![EndDate], "Short Date")
-------------------------------------------------------------------
Result: It gives me data only for Apr 7, April 8, April 9, May 7, May 8 and May 9.

I think it only looks at the days of the date and not the entire date. Anyone can help me, please?

 

[namliam]

When you format you get a text field then doing a between breaks as dates will be considered strings

String wize this is right, because a 1 is greater than a 0 and you are wanting anything between 07- and 09-
That the excludes anything at starts with a 1 or 2 or 3.

If you want to get this working you have to use REAL dates
You have to either use CDate (make sure to use ISO or us dates to get it right)
or a function like DateSerial to convert your string date into a real date
or use a date (picker) control on your form to prevent this mess

Good luck and Welcome to AWF

 

[sheilasabado]

When you format you get a text field then doing a between breaks as dates will be considered strings

String wize this is right, because a 1 is greater than a 0 and you are wanting anything between 07- and 09-
That the excludes anything at starts with a 1 or 2 or 3.

If you want to get this working you have to use REAL dates
You have to either use CDate (make sure to use ISO or us dates to get it right)
or a function like DateSerial to convert your string date into a real date
or use a date (picker) control on your form to prevent this mess

Good luck and Welcome to AWF

You are heaven sent! Thank you for this. I do have a date (picker) control in my form so I just had to insert CDate and it worked wonders. For the benefit of the others who are having the same problem here it is:

User enters ff value in GenForm:

StartDate = 07-Apr-10
EndDate = 09-May-10

This is my query in MS Access Design View

Field: CDate(Format([Call Start],"Short Date"))
-----------------------------------------------------------------
Criteria: Between [Forms]![GenForm]![StartDate] And [Forms]![GenForm]![EndDate]

 

[namliam]

If you have a date picker control you dont need to use CDate at all, should already be a real date. What you are now doing is taking the Call start (date) converting that to a string (using format) then converting back into a date (CDate)

While atleast these are explicit conversions, it is, erm, less than optimal:
Field: [Call Start]
-----------------------------------------------------------------
Criteria: Between [Forms]![GenForm]![StartDate] And [Forms]![GenForm]![EndDate]