how to format date to have a specific format

[Guest]

Hi. I have a query that search between dates.

But once the main program from where my linked has Date and time together I
had to make a short date:

I had this:
04-10-2007 08:35:44

with the short date I have this:
04-10-2007

the problem is that when I insert the criteria the date: 04-10-2007 to list
all records
of 04-100-2007 the access automatically transform that in: 4-10-2007

I went to the regional settings and the settings are: dd-MM-yyyy

The criteria value is a value that comes from a form such as:
[forms]![chart]![dateone]

What can I do to force the criteria to use: short date as 04-10-2007


Thanks.

Regards in advance,
Marco

 

[fredg]

Hi. I have a query that search between dates.

But once the main program from where my linked has Date and time together I
had to make a short date:

I had this:
04-10-2007 08:35:44

with the short date I have this:
04-10-2007

the problem is that when I insert the criteria the date: 04-10-2007 to list
all records
of 04-100-2007 the access automatically transform that in: 4-10-2007

I went to the regional settings and the settings are: dd-MM-yyyy

The criteria value is a value that comes from a form such as:
[forms]![chart]![dateone]

What can I do to force the criteria to use: short date as 04-10-2007

Thanks.

Regards in advance,
Marco

Your message is not very clear.
There is no date 4-100-2007.
I'll assume you are entering 04-10-2007 (for 4-October-2007, and
Access is converting it to 4-10-2007 (April-4-2007).

dd-mm-yyyy is not a U.S. date format.
Regardless of you systems regional settings, you must use a valid U.S.
date format as your query criteria, i.e. mm-dd-yyyy, or be unambiguous
as a date, i.e. 4-Oct-2007.

Further, if your data (regardless of it's format) includes a Time
value, i.e. 04-10-2007 08:35:44, then your ending criteria for that
field must be for the next day (5-Oct) as 4-Oct 08:35:44 is later than
4-Oct midnight.
So to return all records for Oct 4, 2007 only, use
Between [Enter date as mm-dd-yyyy] and DateAdd("d",1,[Enter date as
mm-dd-yyyy])

 

[Guest]

Hi. thanks for your reply.

But I probably explain me wrong.

My problem is that the column has this type of values:

04-10-2007 (4th of October of 2007)

when I say 04-10-2007 I could say 02-05-2007 (2nd of May of 2007)

but when I put in criteria to search for 04-10-2007 (4th October of 2007)
the access in criteria removes the zero, so instead of 04-10-2007 appears
4-10-2007 ( I think that also 4th of October of 2007)

And I think that because of that I can't make good queries.

I even think to put # before and after the date.

Any idea to solve my problem?


Regards,
Marco

Hi. I have a query that search between dates.

But once the main program from where my linked has Date and time together I
had to make a short date:

I had this:
04-10-2007 08:35:44

with the short date I have this:
04-10-2007

the problem is that when I insert the criteria the date: 04-10-2007 to list
all records
of 04-100-2007 the access automatically transform that in: 4-10-2007

I went to the regional settings and the settings are: dd-MM-yyyy

The criteria value is a value that comes from a form such as:
[forms]![chart]![dateone]

What can I do to force the criteria to use: short date as 04-10-2007

Thanks.

Regards in advance,
Marco

Your message is not very clear.
There is no date 4-100-2007.
I'll assume you are entering 04-10-2007 (for 4-October-2007, and
Access is converting it to 4-10-2007 (April-4-2007).

dd-mm-yyyy is not a U.S. date format.
Regardless of you systems regional settings, you must use a valid U.S.
date format as your query criteria, i.e. mm-dd-yyyy, or be unambiguous
as a date, i.e. 4-Oct-2007.

Further, if your data (regardless of it's format) includes a Time
value, i.e. 04-10-2007 08:35:44, then your ending criteria for that
field must be for the next day (5-Oct) as 4-Oct 08:35:44 is later than
4-Oct midnight.
So to return all records for Oct 4, 2007 only, use
Between [Enter date as mm-dd-yyyy] and DateAdd("d",1,[Enter date as
mm-dd-yyyy])

 

[John W. Vinson]

when I say 04-10-2007 I could say 02-05-2007 (2nd of May of 2007)

but when I put in criteria to search for 04-10-2007 (4th October of 2007)
the access in criteria removes the zero, so instead of 04-10-2007 appears
4-10-2007 ( I think that also 4th of October of 2007)

And I think that because of that I can't make good queries.

I even think to put # before and after the date.

Any idea to solve my problem?

An Access Date/Time value is NOT a string. It is not stored in any particular
format, with or without leading zeros. It's stored as a Double Float number, a
count of days and fractions of a day since midnight, December 30, 1899.

If you have a date/time field in your table with today's date in it, what's
actually stored is 39359.000000000. Searching the field for #4-Oct# or #10/4#
or #10/4/2007# or #10/04/2007# will work. Searching for #04/10/2007# will NOT
work, since literal dates must be in either US mm/dd/yyyy format, or some
unambiguous format such as #4-Oct-2007# or #2007-10-04#.

John W. Vinson [MVP]

 

[Guest]

Hello John. Once again you are there for me. I think I should offer youy a
gift (Im not joking, send me your address).

I'm from portugal, we our case the date is dd-mm-yyyy

The problem is that the main access database was create by another user, not
portuguese.

When I run the query what I see is:
04-10-2007 (4th of October of 2007)

The field type is date/time. This is what I see on the original column:
04-10-2007 12:17:33

So in my query I have a column that does this:
Data1: format([Date]); 'short date')

and returns me this:
04-10-2007

And in that column I put this criteria:
04-10-2007

So it should return me all records from with the date of 04-10-2007.

But when I put 04-10-2007 in criteria the access removes the zero I runs the
query with: 4-10-2007


If I put this: 10-10-2007 on criteria it works fine.

Why??

I also had a problem similar with time. In my column appears to me 09:00 and
if the query criteria I put 9:00 it didn't worked.

Please help me out. I'm very stuckes here. I really don't know what else to
do.

Regards,
Marco

 

[Rick Brandt]

Hello John. Once again you are there for me. I think I should
offer youy a gift (Im not joking, send me your address).

I'm from portugal, we our case the date is dd-mm-yyyy

BUT... Date literals in Access queries do NOT care how you express dates
locally. You MUST use US format or a non-ambiguous format. ISO format
YYYY-MM-DD is the best to use.

Access is smart enough to use your local format when the DAY is greater than 12
because it "knows" that the month never exceeds that value. For dates where the
day is 12 or less your local format will not work.

 

[raskew via AccessMonster.com]

This MSKB articles provides information on how dates are stored in Access.
http://support.microsoft.com/kb/q130514/

Bob