Entering Parameter in DateSerial expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006 to
1/31/2006), it will list all the records that occurred in January of any
year. Is there a different way to do this to obtain this information?
 
Dear Jamie:

Quite confused.

When it prompts you for RECEIVED DATE you enter this:

"1/1/2006 to 1/31/2006"

Is that what you mean?

Anyway, to get a future date, try the DateAdd() function.

Tom Ellison
 
The field that I am trying to pull dates from is "180 Days", which had the
DateSerial expression; however, I changed the expression to [RECEIVED
DATE]+180 to show a date that is 180 days from the RECEIVED DATE.

What I am trying to do is pull records from Between [Start Date], which I
would enter "1/1/06" And [End Date], which I would enter "1/31/06" (of course
w/o quotes) in the 180 Days field.

Tom Ellison said:
Dear Jamie:

Quite confused.

When it prompts you for RECEIVED DATE you enter this:

"1/1/2006 to 1/31/2006"

Is that what you mean?

Anyway, to get a future date, try the DateAdd() function.

Tom Ellison


JamieM said:
I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006 to
1/31/2006), it will list all the records that occurred in January of any
year. Is there a different way to do this to obtain this information?
Click to expand...
Click to expand...
 
Dear Jamie:

I still recommend the DateAdd function.

Still not getting your explanation. How do you "pull" something from a
column? Do you mean you want to filter something?

Do you mean:

WHERE CDate([RECEIVED DATE]) + 180 BETWEEN [Start Date] AND [End Date]

Still not reading you clearly. Give examples perhaps.

Is [RECEIVED DATE] a column or a prompt?

Tom Ellison


JamieM said:
The field that I am trying to pull dates from is "180 Days", which had the
DateSerial expression; however, I changed the expression to [RECEIVED
DATE]+180 to show a date that is 180 days from the RECEIVED DATE.

What I am trying to do is pull records from Between [Start Date], which I
would enter "1/1/06" And [End Date], which I would enter "1/31/06" (of
course
w/o quotes) in the 180 Days field.

Tom Ellison said:
Dear Jamie:

Quite confused.

When it prompts you for RECEIVED DATE you enter this:

"1/1/2006 to 1/31/2006"

Is that what you mean?

Anyway, to get a future date, try the DateAdd() function.

Tom Ellison


JamieM said:
I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED
DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006
to
1/31/2006), it will list all the records that occurred in January of
any
year. Is there a different way to do this to obtain this information?
Click to expand...
Click to expand...
Click to expand...
 
JamieM said:
I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006 to
1/31/2006), it will list all the records that occurred in January of any
year. Is there a different way to do this to obtain this information?
Click to expand...


While the long way around, I would expect that to work.
Maybe ther is something od in the [RECEIVED DATE] field??

Note that an equivalent expression to your DateSerial is:
CDate([RECEIVED DATE]) + 180
but the more rigorous expression is:
DateAdd("d", 180, [RECEIVED DATE])
 
I did change the expression to something much simpler. Also, I just deleted
the query and started over. While it seems to work, I did notice that when I
enter the 1/1/06 and 3/31/06 parameters, it gives me the correct information
but includes one record of 12/17/05. But when I enter a different parameter
(such as 4/1/06 and 6/30/06) it gives me the correct information. Do you
have any idea why it gives me the 12/05 record in my 1/1/06 and 3/31/06
parameter.

Marshall Barton said:
JamieM said:
I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006 to
1/31/2006), it will list all the records that occurred in January of any
year. Is there a different way to do this to obtain this information?
Click to expand...


While the long way around, I would expect that to work.
Maybe ther is something od in the [RECEIVED DATE] field??

Note that an equivalent expression to your DateSerial is:
CDate([RECEIVED DATE]) + 180
but the more rigorous expression is:
DateAdd("d", 180, [RECEIVED DATE])
Click to expand...
 
It's starting to look like the dates are being treated as
text strings. A text comparison will return true for:
"1/1/06" < "10/1/02"
since "/" < "0"

Since DateAdd (and DateSerial) return a date value I'm not
sure how something is becoming text. Could you post a
Copy/Paste of query's SQL statement?
--
Marsh
MVP [MS Access]

I did change the expression to something much simpler. Also, I just deleted
the query and started over. While it seems to work, I did notice that when I
enter the 1/1/06 and 3/31/06 parameters, it gives me the correct information
but includes one record of 12/17/05. But when I enter a different parameter
(such as 4/1/06 and 6/30/06) it gives me the correct information. Do you
have any idea why it gives me the 12/05 record in my 1/1/06 and 3/31/06
parameter.

JamieM said:
I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006 to
1/31/2006), it will list all the records that occurred in January of any
year. Is there a different way to do this to obtain this information?
Click to expand...
Click to expand...
Marshall Barton said:
While the long way around, I would expect that to work.
Maybe ther is something od in the [RECEIVED DATE] field??

Note that an equivalent expression to your DateSerial is:
CDate([RECEIVED DATE]) + 180
but the more rigorous expression is:
DateAdd("d", 180, [RECEIVED DATE])
Click to expand...
Click to expand...
 
I just realized I did not add criteria to the one field so I don't need the
parameters. Sorry to waste your time and thanks for the help.

Marshall Barton said:
It's starting to look like the dates are being treated as
text strings. A text comparison will return true for:
"1/1/06" < "10/1/02"
since "/" < "0"

Since DateAdd (and DateSerial) return a date value I'm not
sure how something is becoming text. Could you post a
Copy/Paste of query's SQL statement?
--
Marsh
MVP [MS Access]

I did change the expression to something much simpler. Also, I just deleted
the query and started over. While it seems to work, I did notice that when I
enter the 1/1/06 and 3/31/06 parameters, it gives me the correct information
but includes one record of 12/17/05. But when I enter a different parameter
(such as 4/1/06 and 6/30/06) it gives me the correct information. Do you
have any idea why it gives me the 12/05 record in my 1/1/06 and 3/31/06
parameter.

JamieM wrote:
I want to enter the following parameter:

Between [Start Date] And [End Date]

in the following expression in a query:

180 DAYS: DateSerial(year([RECEIVED DATE]), month([RECEIVED DATE]),
day([RECEIVED DATE])+180)

However, when I enter the parameter information (for example: 1/1/2006 to
1/31/2006), it will list all the records that occurred in January of any
year. Is there a different way to do this to obtain this information?
Click to expand...
Marshall Barton said:
While the long way around, I would expect that to work.
Maybe ther is something od in the [RECEIVED DATE] field??

Note that an equivalent expression to your DateSerial is:
CDate([RECEIVED DATE]) + 180
but the more rigorous expression is:
DateAdd("d", 180, [RECEIVED DATE])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top