Form Range Query Question

[NeonSky via AccessMonster.com]

Hello,

I am stumped as to why the below query is not functioning correctly.

SELECT tblTempAD.*
FROM tblTempAD
WHERE tblTempAD.Arrivals Between [forms]![fConsecConfo]![StartDate] AND
[forms]![fConsecConfo]![EndDate];

In tblTempAD I have multiple records where "tblTempAD.Arrivals" is between
the dates called from the forms...Though it is only pulling back records
where "tblTempAD.Arrivals" actually falls directly on the "start date". Which
is perpelexing because shouldnt the "between" statement pull any "arrival
dates" within the form range?

(for example form values StartDate = 11/1/2008, and EndDate = 11/8/2008, and
tblTempAD.Arrivals holds records with values 11/1/2008, 11/2/2008, 11/3/2008,
and only 11/1/2008 is being pulled by the query)

Thank you for your time and assistance!

 

[Jeanette Cunningham]

Hi,
here are my notes on using date ranges on forms, hope this helps.

Between gotchas for date ranges
Between #1/01/2008# And #1/31/2008#

works perfectly if what you actually want is all records between...

1/01/2008 12:00:00 AM and
1/31/2008 12:00:00 AM

when you supply a date with no time specified
(midnight is always assumed). That means all of the records that have a
date of
1/31/2008 but have times later in the day are not included.

More fool-proof is...

SELECT *
FROM SomeTable
WHERE DateField >= #StartDateValueWithNoTime#
AND DateField < DateAdd("d", 1, #EndDateValueWithNoTime#)


Another example that takes account of nulls for start or end dates

Dim strWhere As String
'if not in US, format dates to US date format
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
'End date, but no start date
If Not IsNull(Me.txtEndDate) Then
strWhere = YourTableName.TheDate <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
'Start date, but no End date
strWhere = YourTableName.TheDate >= " & Format(Me.txtStartDate,
conDateFormat)
Else
'Both start and end dates
strWhere = YourTableName.TheDate >= Format(Me.txtStartDate, conDateFormat) _
& " And " & <=Format(Me.txtEndDate, conDateFormat)
End If
End If

Jeanette Cunningham

 

[NeonSky via AccessMonster.com]

Hello Jeanette, thanks for your response! I am kinda confused why the below
is not working out though....

SELECT Arrivals_A.LNAME, Arrivals_A.ARRIVAL, Arrivals_A.ADDR1, Arrivals_A.
ConcurrentCode, Arrivals_A.LOCKOFF, Arrivals_A.SIZE, Arrivals_A.VISTA,
Arrivals_A.OWNED, Arrivals_A.CONFIRMED
FROM tblReservationData AS Arrivals_A LEFT JOIN tblReservationData AS
Arrivals_B ON (Arrivals_A.LOCKOFF = Arrivals_B.LOCKOFF) AND (Arrivals_A.SIZE
= Arrivals_B.SIZE) AND (Arrivals_A.VISTA = Arrivals_B.VISTA) AND (Arrivals_A.
OWNED = Arrivals_B.OWNED) AND (Arrivals_A.CONFIRMED = Arrivals_B.CONFIRMED)
AND (Arrivals_A.ADDR1 = Arrivals_B.ADDR1) AND (Arrivals_A.LNAME = Arrivals_B.
LNAME) AND (Arrivals_A.ARRIVAL = Arrivals_B.DEPARTURE)
WHERE (((Arrivals_A.ARRIVAL)>=[forms]![FConsecConfo]![StartDate]) AND (
(Arrivals_B.LNAME) Is Null) AND ((Arrivals_B.ADDR1) Is Null) AND ((Arrivals_B.
CONFIRMED) Is Null));

My "WHERE" criteria It is only returning records where the "Arrivals_A.
Arrival" is "=" to "[forms]![fConsecConfo]![StartDate]", I get no returns on
the ">"....any idea why? Thanks for your input!

Hi,
here are my notes on using date ranges on forms, hope this helps.

Between gotchas for date ranges
Between #1/01/2008# And #1/31/2008#

works perfectly if what you actually want is all records between...

1/01/2008 12:00:00 AM and
1/31/2008 12:00:00 AM

when you supply a date with no time specified
(midnight is always assumed). That means all of the records that have a
date of
1/31/2008 but have times later in the day are not included.

More fool-proof is...

SELECT *
FROM SomeTable
WHERE DateField >= #StartDateValueWithNoTime#
AND DateField < DateAdd("d", 1, #EndDateValueWithNoTime#)

Another example that takes account of nulls for start or end dates

Dim strWhere As String
'if not in US, format dates to US date format
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
'End date, but no start date
If Not IsNull(Me.txtEndDate) Then
strWhere = YourTableName.TheDate <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
'Start date, but no End date
strWhere = YourTableName.TheDate >= " & Format(Me.txtStartDate,
conDateFormat)
Else
'Both start and end dates
strWhere = YourTableName.TheDate >= Format(Me.txtStartDate, conDateFormat) _
& " And " & <=Format(Me.txtEndDate, conDateFormat)
End If
End If

Jeanette Cunningham

Hello,

[quoted text clipped - 19 lines]

Thank you for your time and assistance!