Parameter Query on datetime field

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Hoping someone can help me. I am an newbie to
intermediate user of MS Acces 2002 and I'm am building a
few Database Applications while learning how to create
more advanced applications.

I have a table that I'm trying to build a query for. It's
much more complicated but for the sake of explanation I'll
say there are three fields in the query.

ShippingAppt, BOL, and Order

I am trying to filter the list by ShippingAppt which is a
DateTime value using both the Date and Time part. What I
need to be able to do is to pull up everything shipping on
a certian day regardless of what Time it's shipping. So
if I have BOL 1 with ShippingAppt = 12/12/2004 12:00:00
BOL 2 with ShippingAppt = 12/12/2004 16:00:00 and BOL 3
with ShippingAppt = 12/13/2004 19:00:00 and query for
ShippingAppt = 12/12/2004 or 12/12/04 I need to see BOL 1
and BOL 2 but not BOL 3.

I had an eariler version where I Seperated and had
ShipingDate and ShippingAppt and stored the Date part in
ShippingDate and the Time part in ShippingAppt. So I
build the query where the critera for ShippingDate was
[For Which Date?] in the query. This did a wonderfull job
and of course wheater the user entered 12/12 - 12/12/2004
or 12/12/04 it would bring up everything shipping on that
date. However, I'm trying to clean things up.

I've read several posts and articles on MSDN delvelopers
Center (or whatever it is - my brain hurts) and have tried
messing around with DatePart(), Format(), LIKE DatePart(),
LIKE. I've created new fields in the query that equal
DatePart([ShippingAppt]) and run the critera off of that.
I'm lost. I know there has to be somthing obvious I'm not
doing.

Any help would be very very appreciated.
 
Add your criteria under a calculated columnn of
ShipDate: DateValue([ShippingAppt])
 
Duane,

Thanks for your help. I've also figured out how to change
the data type on a variable used in a parameter query by
searching through the help so now the end user can just
type 12/12 and it will work. Thanks. 1 challenge down
550 to go
-----Original Message-----
Add your criteria under a calculated columnn of
ShipDate: DateValue([ShippingAppt])

--
Duane Hookom
MS Access MVP


Matt said:
Hoping someone can help me. I am an newbie to
intermediate user of MS Acces 2002 and I'm am building a
few Database Applications while learning how to create
more advanced applications.

I have a table that I'm trying to build a query for. It's
much more complicated but for the sake of explanation I'll
say there are three fields in the query.

ShippingAppt, BOL, and Order

I am trying to filter the list by ShippingAppt which is a
DateTime value using both the Date and Time part. What I
need to be able to do is to pull up everything shipping on
a certian day regardless of what Time it's shipping. So
if I have BOL 1 with ShippingAppt = 12/12/2004 12:00:00
BOL 2 with ShippingAppt = 12/12/2004 16:00:00 and BOL 3
with ShippingAppt = 12/13/2004 19:00:00 and query for
ShippingAppt = 12/12/2004 or 12/12/04 I need to see BOL 1
and BOL 2 but not BOL 3.

I had an eariler version where I Seperated and had
ShipingDate and ShippingAppt and stored the Date part in
ShippingDate and the Time part in ShippingAppt. So I
build the query where the critera for ShippingDate was
[For Which Date?] in the query. This did a wonderfull job
and of course wheater the user entered 12/12 - 12/12/2004
or 12/12/04 it would bring up everything shipping on that
date. However, I'm trying to clean things up.

I've read several posts and articles on MSDN delvelopers
Center (or whatever it is - my brain hurts) and have tried
messing around with DatePart(), Format(), LIKE DatePart (),
LIKE. I've created new fields in the query that equal
DatePart([ShippingAppt]) and run the critera off of that.
I'm lost. I know there has to be somthing obvious I'm not
doing.

Any help would be very very appreciated.
Click to expand...


.
Click to expand...
 
Back
Top