=> Date Range not working properly

  • Thread starter Thread starter Rhonda Fischer
  • Start date Start date
R

Rhonda Fischer

Hello,

I am trying to collect date values from my Access form
to create a date range:

txtDateFrom: 22/01/2004
txtDateTo: 23/01/2004

However I am comparing these values with my table column
values which include the time:

eg.
[Turners Movements].[Date]
22/01/2004 08:11:21
22/01/2004 08:16:32 etc.

How can I do this.... following is my attempted query:
_________________________________________________________
SELECT [Turners Movements].[ID],
[Turners Movements].[Date]
FROM [Turners Movements]
WHERE DatePart("dd/mm/yyyy",[Turners Movements].[Date]) >=
[Forms]![frmInLoadDateRange]![txtDateFrom]
And DatePart("dd/mm/yyyy",[Turners Movements].[Date]) <=
[Forms]![frmInLoadDateRange]![txtDateTo]
_________________________________________________________

The error message I receive is the query is too
complicated. It's just that I'm not comparing 'apples
with apples'. When I leave the [Turners Movements].[Date]
unchanged the query runs but does not include any of the
end date entries.

Thank you very much for your suggestion.
Rhonda
 
Rhonda

A couple thoughts (just one person's opinion...):

I thought that the DatePart() function refers to a single part of the date,
not a format (your "dd/mm/yyyy").

Is there a chance you're running afoul different date formatting? I thought
I read somewhere that you have to query using "US" date format (i.e.,
month/day/year) values...?
 
Rhonda Fischer said:
WHERE DatePart("dd/mm/yyyy",[Turners Movements].[Date]) >=
[Forms]![frmInLoadDateRange]![txtDateFrom]
And DatePart("dd/mm/yyyy",[Turners Movements].[Date]) <=
[Forms]![frmInLoadDateRange]![txtDateTo]
Click to expand...
Try using DateValue() function instead of DatePart(). DateValue leaves off
the time portion and returns a Date value; DatePart returns an integer.
Jack Peacock
 
Hello,

I am trying to collect date values from my Access form
to create a date range:

txtDateFrom: 22/01/2004
txtDateTo: 23/01/2004

However I am comparing these values with my table column
values which include the time:

eg.
[Turners Movements].[Date]
22/01/2004 08:11:21
22/01/2004 08:16:32 etc.

How can I do this.... following is my attempted query:
_________________________________________________________
SELECT [Turners Movements].[ID],
[Turners Movements].[Date]
FROM [Turners Movements]
WHERE DatePart("dd/mm/yyyy",[Turners Movements].[Date]) >=
[Forms]![frmInLoadDateRange]![txtDateFrom]
And DatePart("dd/mm/yyyy",[Turners Movements].[Date]) <=
[Forms]![frmInLoadDateRange]![txtDateTo]
_________________________________________________________
Click to expand...

Reread the specifications for DatePart! "dd/mm/yyyy" is not a valid
argument.

Note that literal dates must be in American, mm/dd/yyyy format, or an
unambiguous format such as dd-mmm-yyyy, or they will be
misinterpreted. I'd suggest using the following as a criterion on
[Date] - and I'd also STRONGLY suggest renaming the field, since Date
is a reserved word and Access will get confused sooner or later!
= CDate(Format([Forms]![yourform]![txtDateFrom])) AND < DateAdd("d", 1, CDate([Forms]![yourform]![txtDateTo]))
Click to expand...

CDate will apply the computer's regional settings and create a legal
date/time value; adding 1 to the DateTo will cover the 24 hours
following midnight on that day; and putting the criterion directly on
your datefield rather than using DateValue() or another calculated
field will take advantage of any index on the datefield.
 
Back
Top