Date Parameter

[brownti via AccessMonster.com]

I have a union query shown below. I would like to add a where condition but
am having a very difficult time getting the the whole date/time to work out.
tblDelivery.TrimStart and tblDelivery.TrimFinal are Date/Time fields
formatted as "m/d/yyyy" i dont care at all about the time.

SELECT tblDelivery.UnitID, tblJobInfo.JobID, tblJobInfo.JobNumber, [Address]
& " " & [UnitNumber] & " " & [City] AS Location, tblUnitSelections.Wing,
"TrimStart" AS DeliveryDescription, tblDelivery.TrimStart AS DeliveryDate
FROM (tblJobInfo INNER JOIN tblUnitSelections ON tblJobInfo.JobID =
tblUnitSelections.JobID) INNER JOIN tblDelivery ON tblUnitSelections.UnitID =
tblDelivery.UnitID
WHERE (((tblDelivery.TrimStart) Is Not Null))
ORDER BY tblDelivery.UnitID

UNION ALL SELECT tblDelivery.UnitID, tblJobInfo.JobID, tblJobInfo.JobNumber,
[Address] & " " & [UnitNumber] & " " & [City] AS Location, tblUnitSelections.
Wing, "TrimFinal" AS DeliveryDescription, tblDelivery.TrimFinal AS
DeliveryDate
FROM (tblJobInfo INNER JOIN tblUnitSelections ON tblJobInfo.JobID =
tblUnitSelections.JobID) INNER JOIN tblDelivery ON tblUnitSelections.UnitID =
tblDelivery.UnitID
WHERE (((tblDelivery.TrimFinal) Is Not Null))
ORDER BY tblDelivery.UnitID



What i am trying is to create another Select query with the following code:
SELECT qryScheduleCombine.DeliveryDate, *
FROM qryScheduleCombine
WHERE (((qryScheduleCombine.DeliveryDate)<=Date()));

This SQL doesnt look at the year at all. It only seems to be searching based
on the months and days. i would also like to create a condition where i can
select two dates (based from a calendar control) and only show records
between those two dates. I have been struggling with this for about a day
and a half and have given up...

 

[Ken Snell \(MVP\)]

Sounds as if Jet/ACCESS are seeing your date/time values as text/string
values and not as date values. If you've indeed set the datatype of the
tblDelivery.TrimStart and tblDelivery.TrimFinal fields as DateTime, then I
don't see why your query should return them as text/string values. But your
description of how the search is being done indicates that they are indeed
text values.

Easiest thing to do here is to change this WHERE clause:
WHERE (((qryScheduleCombine.DeliveryDate)<=Date()));

to this:
WHERE (((CDate(qryScheduleCombine.DeliveryDate))<=Date()));

 

[brownti via AccessMonster.com]

Yep that made it work. Can you explain what the CDate does? It was seeing
them as text values (they were aligned to the left of the "cell"). i dont
know why though...

Sounds as if Jet/ACCESS are seeing your date/time values as text/string
values and not as date values. If you've indeed set the datatype of the
tblDelivery.TrimStart and tblDelivery.TrimFinal fields as DateTime, then I
don't see why your query should return them as text/string values. But your
description of how the search is being done indicates that they are indeed
text values.

Easiest thing to do here is to change this WHERE clause:
WHERE (((qryScheduleCombine.DeliveryDate)<=Date()));

to this:
WHERE (((CDate(qryScheduleCombine.DeliveryDate))<=Date()));

I have a union query shown below. I would like to add a where condition
but

[quoted text clipped - 40 lines]

between those two dates. I have been struggling with this for about a day
and a half and have given up...

 

[brownti via AccessMonster.com]

How could i do a between where condition? Below is what i tried and it did
not return any results.

SELECT *
FROM qryScheduleCombine
WHERE (((CDate([DeliveryDate]))>=12/29/2008 And (CDate([DeliveryDate]))
<=1/2/2009 And (CDate([DeliveryDate]))>=Date()));

Thanks for the help! I really appreciate it.

Sounds as if Jet/ACCESS are seeing your date/time values as text/string
values and not as date values. If you've indeed set the datatype of the
tblDelivery.TrimStart and tblDelivery.TrimFinal fields as DateTime, then I
don't see why your query should return them as text/string values. But your
description of how the search is being done indicates that they are indeed
text values.

Easiest thing to do here is to change this WHERE clause:
WHERE (((qryScheduleCombine.DeliveryDate)<=Date()));

to this:
WHERE (((CDate(qryScheduleCombine.DeliveryDate))<=Date()));

I have a union query shown below. I would like to add a where condition
but

[quoted text clipped - 40 lines]

between those two dates. I have been struggling with this for about a day
and a half and have given up...

 

[brownti via AccessMonster.com]

I think i got it. How does this look?

WHERE (((CDate([DeliveryDate])) Between #12/29/2008# And #1/3/2009# And
(CDate([DeliveryDate]))));

How could i do a between where condition? Below is what i tried and it did
not return any results.

SELECT *
FROM qryScheduleCombine
WHERE (((CDate([DeliveryDate]))>=12/29/2008 And (CDate([DeliveryDate]))
<=1/2/2009 And (CDate([DeliveryDate]))>=Date()));

Thanks for the help! I really appreciate it.

Sounds as if Jet/ACCESS are seeing your date/time values as text/string
values and not as date values. If you've indeed set the datatype of the

[quoted text clipped - 14 lines]

 

[Ken Snell \(MVP\)]

That will work *so long as* your regional setting provides your date values
as mm/dd/yyyy (or m/d/yyyy) format -- the US format.

CDate is a built-in function that converts a string that looks like a date
to an actual date/time value.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I think i got it. How does this look?

WHERE (((CDate([DeliveryDate])) Between #12/29/2008# And #1/3/2009# And
(CDate([DeliveryDate]))));

How could i do a between where condition? Below is what i tried and it
did
not return any results.

SELECT *
FROM qryScheduleCombine
WHERE (((CDate([DeliveryDate]))>=12/29/2008 And (CDate([DeliveryDate]))
<=1/2/2009 And (CDate([DeliveryDate]))>=Date()));

Thanks for the help! I really appreciate it.

Sounds as if Jet/ACCESS are seeing your date/time values as text/string
values and not as date values. If you've indeed set the datatype of the

[quoted text clipped - 14 lines]

between those two dates. I have been struggling with this for about a
day
and a half and have given up...