Between dates / Long date format

[Peter]

Hi all,
I just converted from 2003 to 2007 and at the same time I took the
opportunity to “clean up†some in tables…I changed [DateCreated] format from
short to long date since the Date/Time stamp
Is valuable (previous I used one separate date and time) ..Here is the
issue. Running the query with the parameters in the [DateCreated] field,
Between(First Date) AND (Second Date) does not function.. what am I doing
wrong?

Thanks!

 

[Allen Browne]

Internally, Access stores date/time fields as a number, where the integer
part represents the date, and the fraction the time (e.g. 0.5 = noon (half a
day.)) Changing the format doesn't change the existing data in the tables.

Therefore something else must be going on. If [First Date] and [Second Date]
represent parameters in your example, make sure:
a) you include the square brackets around their names.

b) You declare the parameters (on the menu/ribbon) so Access knows their
type. Youll end up with 2 rows in your dialog:
[First Date] Date/Time
[Second Date] Date/Time

c) If these parameters are actually read from unbound text boxes on a form,
set the Format property of the text boxes to General Date so Access
understands the dates.

d) If your regional settings use a non-US date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

I didn't understand the bit about "Is valuable." If you've changed from
Date/Time to a Text format, none of this will work (at least for US/UK date
formats.)