Date Function

[Maria]

Hi,

My company needs to do driver observations on all of our drivers once per
month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report of all
drivers who have been observed, and another report for all drivers who still
need to be observed. The observation date field usually has a past date in
it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists all the
drivers who have already had observations. I then used the same query, but
for the criteria, I put Not Between [Enter Beginning Date] And [Enter Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the date of
a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria

 

[tina]

in the ObservationDate field's criteria row, try the following

< [Enter Date]

you'd need to enter the first day of the month in the prompt.

or you could write the query to automatically pull records where the
ObservationDate is prior to the *current* month, with the following criteria

< DateSerial(Year(Date()), Month(Date()), 1)

hth

 

[Maria]

Thanks for the reply Tina. I tried both ways you mentioned but I can't seem
to get it to work. It seems so simple, yet I can't figure it out. I wonder
what it is I'm doing wrong.

in the ObservationDate field's criteria row, try the following

< [Enter Date]

you'd need to enter the first day of the month in the prompt.

or you could write the query to automatically pull records where the
ObservationDate is prior to the *current* month, with the following criteria

< DateSerial(Year(Date()), Month(Date()), 1)

hth

Hi,

My company needs to do driver observations on all of our drivers once per
month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report of all
drivers who have been observed, and another report for all drivers who still
need to be observed. The observation date field usually has a past date in
it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists all the
drivers who have already had observations. I then used the same query, but
for the criteria, I put Not Between [Enter Beginning Date] And [Enter Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the date of
a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria

 

[tina]

well, assuming that the ObservationDate field is a Date/Time data type (in
the table), you may need to define the query parameters. from the query
design view, on the menu bar, click Query | Parameters. in the dialog box's
Parameter column, enter the parameter, as

[Enter Date]

in the Data Type column, choose Date/Time from the droplist.

as to why the "DateSerial()" solution didn't work, i have no idea. *is*
ObservationDate a Date/Time field? when you tried the DateSerial() solution,
what happened? did the query return *no* records? records equal to, or in
the future of, the first day of the current month? did you get an error
message?

hth

Thanks for the reply Tina. I tried both ways you mentioned but I can't seem
to get it to work. It seems so simple, yet I can't figure it out. I wonder
what it is I'm doing wrong.

in the ObservationDate field's criteria row, try the following

< [Enter Date]

you'd need to enter the first day of the month in the prompt.

or you could write the query to automatically pull records where the
ObservationDate is prior to the *current* month, with the following criteria

< DateSerial(Year(Date()), Month(Date()), 1)

hth

Hi,

My company needs to do driver observations on all of our drivers once per
month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report

of
all

drivers who have been observed, and another report for all drivers who still
need to be observed. The observation date field usually has a past

date

in

it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists

all
the

drivers who have already had observations. I then used the same query, but
for the criteria, I put Not Between [Enter Beginning Date] And [Enter Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the

date
of

a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria

 

[Maria]

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested, I got an error "Syntax error in parameter clause". The DateSerial
returned no records at all. All of the date fields are date/time format. I
put an = sign after the < in the DateSerial and it returned 20 records for
11/1/05. I need all records prior to 11/1/05. Every month, I will need
records prior to that current month's first day.

 

[John Vinson]

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested, I got an error "Syntax error in parameter clause". The DateSerial
returned no records at all. All of the date fields are date/time format. I
put an = sign after the < in the DateSerial and it returned 20 records for
11/1/05. I need all records prior to 11/1/05. Every month, I will need
records prior to that current month's first day.

You should be putting just

[Enter Date]

in the Parameter dialog; the < operator (or any other operator) needs
to be before the parameter in the Criteria box.

John W. Vinson[MVP]

 

[Br@dley]

Maria wrote:
<>

"The Observation Date field is either empty or has the date of a
previous month"

Can't you use this criteria on your date field....

Isnull Or < DateSerial(Year(Date()), Month(Date()), 1)

???

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

 

[tina]

When I put < [Enter Date] in the parameter area from the toolbar as you

suggested

if you'll re-read my post, hon, you'll see that i did *not* suggest putting
the criteria expression in the Parameter column of the dialog box. i
specifically said to put the parameter

[Enter Date]

in the column, and gave you the above example. correct the entry in the
Parameter column of the dialog box, and try running the query again. what
results to you get?

The DateSerial returned no records at all.

okay. *copy* the complete criteria expression from your query design view
(the DateSerial() criteria), and *paste* it into a post (don't type it in) -
that way we can see exactly what's in the criteria. also, try using a
"hard-coded" date in your expression, as

< #12/1/2005#

what results do you get?

hth

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested, I got an error "Syntax error in parameter clause". The DateSerial
returned no records at all. All of the date fields are date/time format. I
put an = sign after the < in the DateSerial and it returned 20 records for
11/1/05. I need all records prior to 11/1/05. Every month, I will need
records prior to that current month's first day.

Hi,

My company needs to do driver observations on all of our drivers once per
month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report of all
drivers who have been observed, and another report for all drivers who still
need to be observed. The observation date field usually has a past date in
it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists all the
drivers who have already had observations. I then used the same query, but
for the criteria, I put Not Between [Enter Beginning Date] And [Enter Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the date of
a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria

 

[Maria]

You are all geniuses! I did it wrong the first time, and now it works! Thank
you all so much.

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested

if you'll re-read my post, hon, you'll see that i did *not* suggest putting
the criteria expression in the Parameter column of the dialog box. i
specifically said to put the parameter

[Enter Date]

in the column, and gave you the above example. correct the entry in the
Parameter column of the dialog box, and try running the query again. what
results to you get?

The DateSerial returned no records at all.

okay. *copy* the complete criteria expression from your query design view
(the DateSerial() criteria), and *paste* it into a post (don't type it in) -
that way we can see exactly what's in the criteria. also, try using a
"hard-coded" date in your expression, as

< #12/1/2005#

what results do you get?

hth

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested, I got an error "Syntax error in parameter clause". The DateSerial
returned no records at all. All of the date fields are date/time format. I
put an = sign after the < in the DateSerial and it returned 20 records for
11/1/05. I need all records prior to 11/1/05. Every month, I will need
records prior to that current month's first day.

Hi,

My company needs to do driver observations on all of our drivers once per
month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report

of
all

drivers who have been observed, and another report for all drivers who still
need to be observed. The observation date field usually has a past

date

in

it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists

all
the

drivers who have already had observations. I then used the same query, but
for the criteria, I put Not Between [Enter Beginning Date] And [Enter Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the

date
of

a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria

 

[tina]

you're welcome

You are all geniuses! I did it wrong the first time, and now it works! Thank
you all so much.

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested

if you'll re-read my post, hon, you'll see that i did *not* suggest putting
the criteria expression in the Parameter column of the dialog box. i
specifically said to put the parameter

[Enter Date]

in the column, and gave you the above example. correct the entry in the
Parameter column of the dialog box, and try running the query again. what
results to you get?

The DateSerial returned no records at all.

okay. *copy* the complete criteria expression from your query design view
(the DateSerial() criteria), and *paste* it into a post (don't type it in) -
that way we can see exactly what's in the criteria. also, try using a
"hard-coded" date in your expression, as

< #12/1/2005#

what results do you get?

hth

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested, I got an error "Syntax error in parameter clause". The DateSerial
returned no records at all. All of the date fields are date/time

format.

I

put an = sign after the < in the DateSerial and it returned 20 records for
11/1/05. I need all records prior to 11/1/05. Every month, I will need
records prior to that current month's first day.

Hi,

My company needs to do driver observations on all of our drivers

once
per

month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report of
all
drivers who have been observed, and another report for all drivers who
still
need to be observed. The observation date field usually has a past

date

in
it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists all
the
drivers who have already had observations. I then used the same

query,
but

for the criteria, I put Not Between [Enter Beginning Date] And [Enter
Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the date
of
a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria

 

[Amit]

Thanks

-----Original Message-----
From: tina [mailto:[email protected]]
Posted At: Wednesday, November 30, 2005 9:27 PM
Posted To: microsoft.public.access
Conversation: Date Function
Subject: Re: Date Function


you're welcome

You are all geniuses! I did it wrong the first time, and now it works! Thank
you all so much.

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested

if you'll re-read my post, hon, you'll see that i did *not* suggest putting
the criteria expression in the Parameter column of the dialog box. i
specifically said to put the parameter

[Enter Date]

in the column, and gave you the above example. correct the entry in the
Parameter column of the dialog box, and try running the query again. what
results to you get?

The DateSerial returned no records at all.

okay. *copy* the complete criteria expression from your query design view
(the DateSerial() criteria), and *paste* it into a post (don't type it in) -
that way we can see exactly what's in the criteria. also, try using a
"hard-coded" date in your expression, as

< #12/1/2005#

what results do you get?

hth

When I put < [Enter Date] in the parameter area from the toolbar as you
suggested, I got an error "Syntax error in parameter clause". The DateSerial
returned no records at all. All of the date fields are date/time

format.

I

put an = sign after the < in the DateSerial and it returned 20 records for
11/1/05. I need all records prior to 11/1/05. Every month, I will need
records prior to that current month's first day.

Hi,

My company needs to do driver observations on all of our drivers

once
per

month. My database has a field where I enter the date of the driver
observation. Towards the end of the month, I need to create a report of
all
drivers who have been observed, and another report for all drivers who
still
need to be observed. The observation date field usually has a past

date

in
it and is updated each month. I created a parameter query Between [Enter
Beginning Date] And [Enter Ending Date] and it works fine and lists all
the
drivers who have already had observations. I then used the same

query,
but

for the criteria, I put Not Between [Enter Beginning Date] And [Enter
Ending
Date] to get all the drivers who still need an observation, but no records
are returned. The Observation Date field is either empty or has the date
of
a previos month in it, so I need to specify the current month so I thought
by asking which records do NOT have THIS month's date (or whatever date
range is entered) would return the correct records. How do you ask for
records that do not have a perticular date range? Thanks.

Maria