Between Two Dates

[ddrew]

Im using the following code as part ofa search engine:

Between [Forms]![Search]![txtStartDate] And [Forms]![Search]![txtFinishDate]

It dosent appear to find all of the records only some of them, and none in 2008, can anyone see anything glaringly obviour!

 

[Ron_dK]

Between [Forms]![Search]![txtStartDate] And [Forms]![Search]![txtFinishDate]

Looks to me that the txtstartdate and txtfinishdate are txt fields. If so, you'd better change them to date fields and try again.

Hth

 

[ddrew]

Checked them both, they are unbound text boxes, but with a format of Short Date

 

[CraigDolphin]

Actually I think the txt refers to them being textbox controls on the form and this shouldn't be the problem.

It dosent appear to find all of the records only some of them, and none in 2008, can anyone see anything glaringly obviour!

Can you post the sql for your query? There may be some other conditions creating the 'missing' records.

My guess is that it might be the type of joins that you use to link the source tables in your query. If a record in one table doesn't have a record in another linked table from which one of the fields in your query is drawn, then that record won't show up at all unless you change the type of join such that it shows all records from table1 regardless of records from table 2. By deault, the join types usually show only records from table1 with matching records in table 2.

Search on join types like inner join, outer join, right join etc to learn more.

 

[raskew]

Check this out to see how Access stores dates. http://support.microsoft.com/kb/q130514/

What counts is the underlying source, e.g. table of the fields. Formatting a text field as short-date does not make it data/time data format. The DateValue() function will, however, return a date from a string.
Here's an example from the debug (immediate) window:

x = "2/1/08"
y = datevalue(x)
? y
2/1/2008 
'to show it's now stored in date/time data format:
? cdbl(y)
 39479

HTH - Bob

 

[CraigDolphin]

...they are unbound text boxes...

What counts is the underlying source...

I always thought that only matters if they're bound to a field in a recordsource?

 

[FoFa]

Well you could try:
Between cdate([Forms]![Search]![txtStartDate]) And cdate([Forms]![Search]![txtFinishDate])
this should stop any date conversion errors, as long as the field on in the table you are comparing to is a date data type.
The other thing is if time is stored along with the date in the table, your Finish Date will only be through midnight of that date (not all inclusive through that day), so if your Finish Date is 01/02/2008 (US date format) a date/time of 1 second after midnight would not match the criteria because 01/02/2008 00:00:01 is greater than 01/02/2008 00:00:00 which is the default if no time is specified.
If you date field on your table is a text field, you have a whole lot of other issue because date matching is not taking place, text matching is. Maybe more information is required to debug online.