DATE SERIAL

[Guest]

Hi I have done the dateserial(left([date},4),Mid([date],6,2),Right([date],2))
which seems to have worked fine, except it will not let me format the date
all it returns is 01-Jan-06 etc.. So when I go to do a date parameter it
does not restrict to what I am asking for in the format 01/01/06 but give me
everything. What am I missing? Thanks

 

[Allen Browne]

Suggestions:

1. Wrap CVDate() around your calcuated field:
CVDate(DateSerial(Left([Date],4),Mid([Date],6,2),Right([Date],2)))

2. Declare the data parameter. Choose Parameters on the Query menu. In the
dialog, enter exactly the same name you used in the query, and specify it is
a Date/Time type.

To understand how to avoid problems with dates in your dmy format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of why the CVDate(), see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

I tried this Allen and it did not work. I used the parameter box and called
it the correct thing, but all I get is a parameter query box? Any ideas
about what I am doing wrong?

Suggestions:

1. Wrap CVDate() around your calcuated field:
CVDate(DateSerial(Left([Date],4),Mid([Date],6,2),Right([Date],2)))

2. Declare the data parameter. Choose Parameters on the Query menu. In the
dialog, enter exactly the same name you used in the query, and specify it is
a Date/Time type.

To understand how to avoid problems with dates in your dmy format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of why the CVDate(), see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi I have done the
dateserial(left([date},4),Mid([date],6,2),Right([date],2))
which seems to have worked fine, except it will not let me format the date
all it returns is 01-Jan-06 etc.. So when I go to do a date parameter it
does not restrict to what I am asking for in the format 01/01/06 but give
me
everything. What am I missing? Thanks

 

[Allen Browne]

Perhaps you could explain what you meant by:
when I go to do a date parameter

I assumed you had a date parameter in your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried this Allen and it did not work. I used the parameter box and
called
it the correct thing, but all I get is a parameter query box? Any ideas
about what I am doing wrong?

Suggestions:

1. Wrap CVDate() around your calcuated field:
CVDate(DateSerial(Left([Date],4),Mid([Date],6,2),Right([Date],2)))

2. Declare the data parameter. Choose Parameters on the Query menu. In
the
dialog, enter exactly the same name you used in the query, and specify it
is
a Date/Time type.

To understand how to avoid problems with dates in your dmy format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of why the CVDate(), see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Hi I have done the
dateserial(left([date},4),Mid([date],6,2),Right([date],2))
which seems to have worked fine, except it will not let me format the
date
all it returns is 01-Jan-06 etc.. So when I go to do a date parameter
it
does not restrict to what I am asking for in the format 01/01/06 but
give
me
everything. What am I missing? Thanks

 

[Guest]

I thought that is what you meant in answer 2 where you say go to parameters
in the query menu and use the same name as in the query. Have I
misunderstood? are we not talking about the [date] field or are we talking
about the whole name of the query?

Perhaps you could explain what you meant by:
when I go to do a date parameter

I assumed you had a date parameter in your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried this Allen and it did not work. I used the parameter box and
called
it the correct thing, but all I get is a parameter query box? Any ideas
about what I am doing wrong?

Suggestions:

1. Wrap CVDate() around your calcuated field:
CVDate(DateSerial(Left([Date],4),Mid([Date],6,2),Right([Date],2)))

2. Declare the data parameter. Choose Parameters on the Query menu. In
the
dialog, enter exactly the same name you used in the query, and specify it
is
a Date/Time type.

To understand how to avoid problems with dates in your dmy format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of why the CVDate(), see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Hi I have done the
dateserial(left([date},4),Mid([date],6,2),Right([date],2))
which seems to have worked fine, except it will not let me format the
date
all it returns is 01-Jan-06 etc.. So when I go to do a date parameter
it
does not restrict to what I am asking for in the format 01/01/06 but
give
me
everything. What am I missing? Thanks

 

[Allen Browne]

Sorry, I don't follow what you are asking.

Switch the query to SQL View (View menu), copy the whole SQL statement, and
post it here. I will then try to understand your question in light of the
actual query you are working on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I thought that is what you meant in answer 2 where you say go to parameters
in the query menu and use the same name as in the query. Have I
misunderstood? are we not talking about the [date] field or are we
talking
about the whole name of the query?

Perhaps you could explain what you meant by:
when I go to do a date parameter

I assumed you had a date parameter in your query.

I tried this Allen and it did not work. I used the parameter box and
called
it the correct thing, but all I get is a parameter query box? Any
ideas
about what I am doing wrong?

:

Suggestions:

1. Wrap CVDate() around your calcuated field:
CVDate(DateSerial(Left([Date],4),Mid([Date],6,2),Right([Date],2)))

2. Declare the data parameter. Choose Parameters on the Query menu. In
the
dialog, enter exactly the same name you used in the query, and specify
it
is
a Date/Time type.

To understand how to avoid problems with dates in your dmy format,
see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of why the CVDate(), see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Hi I have done the
dateserial(left([date},4),Mid([date],6,2),Right([date],2))
which seems to have worked fine, except it will not let me format
the
date
all it returns is 01-Jan-06 etc.. So when I go to do a date
parameter
it
does not restrict to what I am asking for in the format 01/01/06 but
give
me
everything. What am I missing? Thanks

 

[Guest]

Thanks Allen, I did solve it by going into the SQL and realized that for some
reason it was saying "long date" so I changed that to short date and it
worked.

Sorry, I don't follow what you are asking.

Switch the query to SQL View (View menu), copy the whole SQL statement, and
post it here. I will then try to understand your question in light of the
actual query you are working on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I thought that is what you meant in answer 2 where you say go to parameters
in the query menu and use the same name as in the query. Have I
misunderstood? are we not talking about the [date] field or are we
talking
about the whole name of the query?

Perhaps you could explain what you meant by:
when I go to do a date parameter

I assumed you had a date parameter in your query.

I tried this Allen and it did not work. I used the parameter box and
called
it the correct thing, but all I get is a parameter query box? Any
ideas
about what I am doing wrong?

:

Suggestions:

1. Wrap CVDate() around your calcuated field:
CVDate(DateSerial(Left([Date],4),Mid([Date],6,2),Right([Date],2)))

2. Declare the data parameter. Choose Parameters on the Query menu. In
the
dialog, enter exactly the same name you used in the query, and specify
it
is
a Date/Time type.

To understand how to avoid problems with dates in your dmy format,
see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of why the CVDate(), see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Hi I have done the
dateserial(left([date},4),Mid([date],6,2),Right([date],2))
which seems to have worked fine, except it will not let me format
the
date
all it returns is 01-Jan-06 etc.. So when I go to do a date
parameter
it
does not restrict to what I am asking for in the format 01/01/06 but
give
me
everything. What am I missing? Thanks