Qry help...Maybe a double post...my apologies in advance...

[Jacob]

Access 2003

Ok, I am having troubles...I am using this as a critiera in a query in the
field DateSold...

<=CVDate([Enter date]) OR Is Null


When I enter a date, it does bring back my null values like it should, but
when I enter a date, it still pulls dates from beyound what I enter (Greater
than)...Any ideas what is causing this?

My table field for DateSold is set as TEXT and SHORT date with a input
mask of 99/99/0000;0;_

I also tried it as a date field with the same input mask.


Thank you in advance for any guidance.


Jacob

 

[Dirk Goldgar]

Access 2003

Ok, I am having troubles...I am using this as a critiera in a query in the
field DateSold...

<=CVDate([Enter date]) OR Is Null

When I enter a date, it does bring back my null values like it should, but
when I enter a date, it still pulls dates from beyound what I enter
(Greater
than)...Any ideas what is causing this?

My table field for DateSold is set as TEXT and SHORT date with a input
mask of 99/99/0000;0;_

I also tried it as a date field with the same input mask.

I would certainly NOT use a Text field to store a date. The field should be
set as a Date/Time field. If you still have the problem, it may be due to
confusion due to regional date settings. How are your dates entered, as
month/day/year or as day/month/year? How will Access interpret an entry of
5/6/2009 -- as May 6th or as June 5th?

If none of the above ideas solves your problem, please post the complete SQL
of your query.

 

[Jacob]

OUTSTANDING!!! Dirk, I changed my table back to Date/Time and reran the
query. Of course it failed. I so tried to switch the month as you suggested
and it filtered perfectly. All I have now is how do I correct this? It would
appear that when I use 01/07/2009 it sees it as Jan 7th of 2009 opposed to
what is actually entered which is july 1st 2009. Any idea how I reconcile
this?

Access 2003

Ok, I am having troubles...I am using this as a critiera in a query in
the
field DateSold...

<=CVDate([Enter date]) OR Is Null

When I enter a date, it does bring back my null values like it should,
but
when I enter a date, it still pulls dates from beyound what I enter
(Greater
than)...Any ideas what is causing this?

My table field for DateSold is set as TEXT and SHORT date with a input
mask of 99/99/0000;0;_

I also tried it as a date field with the same input mask.

I would certainly NOT use a Text field to store a date. The field should
be set as a Date/Time field. If you still have the problem, it may be due
to confusion due to regional date settings. How are your dates entered,
as month/day/year or as day/month/year? How will Access interpret an
entry of 5/6/2009 -- as May 6th or as June 5th?

If none of the above ideas solves your problem, please post the complete
SQL of your query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

OUTSTANDING!!! Dirk, I changed my table back to Date/Time and reran the
query. Of course it failed. I so tried to switch the month as you
suggested and it filtered perfectly. All I have now is how do I correct
this? It would appear that when I use 01/07/2009 it sees it as Jan 7th of
2009 opposed to what is actually entered which is july 1st 2009. Any idea
how I reconcile this?

What are your regional date settings? Is your system set for
month/day/year or for day/month/year?

How are the dates being entered into the table that you are querying? Are
they entered manually into the field, or are they automatically assigned the
current system date by code or default value?

When you enter the cutoff date for your query at the [Enter date] parameter
prompt, I take it you enter it in day/month/year format. Is that right?

IF your regional date setting is for day/month/year format, you can try
changing your query criteria to:

<=DateValue([Enter date]) OR Is Null

.... and see if that makes the difference. According to the help file,
"DateValue recognizes the order for month, day, and year according to the
Short Date format you specified for your system." I'm not sure exactly how
CVDate handles ambiguous dates.

 

[Jacob]

Ok Dirk, I have a good one for you....here are your answers...

My regional settings are dd/MM/yyyy Displays in the example as....

Short Date 11/08/2009
Long Date Tuesday, 11 August, 2009

When it is set to this, I can type in my date normally and everything comes
back ok. However, the dates in the qry are showing in this format mm/dd/yyyy


My concern is that it seems that Access somehow has the two backwards. I
can reverse my regional setting to mm/dd/yyyy and it shows right in the qry
after I run it but have to type in the month first. GRRRR! I understand if
this is a computer issue....

Jacob

OUTSTANDING!!! Dirk, I changed my table back to Date/Time and reran the
query. Of course it failed. I so tried to switch the month as you
suggested and it filtered perfectly. All I have now is how do I correct
this? It would appear that when I use 01/07/2009 it sees it as Jan 7th of
2009 opposed to what is actually entered which is july 1st 2009. Any idea
how I reconcile this?

What are your regional date settings? Is your system set for
month/day/year or for day/month/year?

How are the dates being entered into the table that you are querying? Are
they entered manually into the field, or are they automatically assigned
the current system date by code or default value?

When you enter the cutoff date for your query at the [Enter date]
parameter prompt, I take it you enter it in day/month/year format. Is
that right?

IF your regional date setting is for day/month/year format, you can try
changing your query criteria to:

<=DateValue([Enter date]) OR Is Null

... and see if that makes the difference. According to the help file,
"DateValue recognizes the order for month, day, and year according to the
Short Date format you specified for your system." I'm not sure exactly
how CVDate handles ambiguous dates.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Ok Dirk, I have a good one for you....here are your answers...

My regional settings are dd/MM/yyyy Displays in the example as....

Short Date 11/08/2009
Long Date Tuesday, 11 August, 2009

When it is set to this, I can type in my date normally and everything
comes back ok. However, the dates in the qry are showing in this format
mm/dd/yyyy


My concern is that it seems that Access somehow has the two backwards. I
can reverse my regional setting to mm/dd/yyyy and it shows right in the
qry after I run it but have to type in the month first. GRRRR! I
understand if this is a computer issue....

If the query is selecting the correct data, but just formatting the dates
incorrectly, that is presumably the fault of the Format property of (a) the
table field, (b) the query field, or (c) the form control (if any) that is
displaying the data. If no form is involved here, then (c) doesn't apply.

So check the table design and the query design. In the table, check the
Format property for the field. Does it specify the date format? Either
clear it or change it to "Short Date". Note: even if you know it was set to
t"Short Date" before, make sure it is still set to that format.

Check the query that is returning the data. In the query's design view,
click on the grid column for the DateSold field, then open the property
sheet for that field. Check the Format property. Make sure that it's
either blank or set to Short Date.

Make a similar check with any form control that is bound to this field.

If all the properties are set correctly, we have to entertain the
possibility that somehow the dates were entered incorrectly, or else were
converted incorrectly when you switched the field type from Date/Time to
Text or from Text to Date/Time. You never answered my question about how
values are entered in these fields: manually, by code, or by default value
referring to the Date() function. Please answer that.

See if you can pick a record where you know what the date ought to be, but
the date expressed in short date format is ambiguous. Then find out what
the real date entered in that field by querying it and forcing the date
format to something totally ambiguous; e.g.,

SELECT
DateSold,
Format(DateSold, "dd-mmmm-yyyy") As FormattedDate
FROM YourTable

See what the results of that are, and whether the dates are really as you
expect.

 

[Jacob]

Ok, as far as I can tell, the table is set to short date, as is the form
involved. The dates are entered manually by the end user. I will try all the
stuff you listed here, but something is out of wack and I think you have
provided me more than enough direction here. I really appreciate all the
time you have put into helping me with this. I will update you possibly
tomorrow when I have a complete answer. Thank you again Dirk...your really
good with this stuff.

 

[Dirk Goldgar]

Ok, as far as I can tell, the table is set to short date, as is the form
involved. The dates are entered manually by the end user. I will try all
the stuff you listed here, but something is out of wack and I think you
have provided me more than enough direction here. I really appreciate all
the time you have put into helping me with this. I will update you
possibly tomorrow when I have a complete answer. Thank you again Dirk

You're welcome. Post back when you find the answer, or if you get stuck.

 

[AccessVandal via AccessMonster.com]

The query editor in your criteria will always default to the US format of
mm/dd/yyyy, even if you set your regional setting to dd/mm/yyyy.

The best solution is to set your Regional setting to dd/mmm/yyyy. (12-Aug-
2009)

Now with this setting, your criteria will automatically convert to
dd/mmm/yyyy whenever key in dd/mm/yy or dd/mm/yyyy but not mm/dd/yyyy as your
as your regional setting will convert it to dd/mmm/yyyy.

It seems that the query editor has problems understanding if you put your
regional setting to dd/mm/yyyy format but it has no problems understanding
dd/mmm/yyyy format.