Date format problem

[Guest]

I've two table, say table1 and table2, and they have date type field [date1]
and [date2] respectively. They are in Medium Date format. I've tried to loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records were
missed at the result and it seems to be the date format problem. I've tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem. Thanks

 

[Ken Snell [MVP]]

Are you storing time along with the date in those fields? That can cause the
query not to see the last date in your query's criterion range because the
value actually is larger than that date. If you're storing time, add 1 to
your date range's last date to include those values in the query.

Format of a date field is immaterial. ACCESS simply uses the format for
display purposes, not for how the data are stored. Date and time data are
stored as floating point numbers, where the integer portion of the number is
the number of days since December 20, 1899, and the fraction (decimal)
portion represents the time in terms of the fraction of 24 hours represented
by that time of day.

 

[Allen Browne]

Internally, Access stores date/time fields as a number, where the whole
number part represents the date, and the fractional part represents the time
of day (.5 = nooon, .25 = 6am, ...).

Therefore the display format for the date should not be the problem.
However, if one of the field contains a time component, then they won't
match. That would be true even if you use a Medium Date format, which
suppresses the display of the time part.

Often, the time component is introduced if you use =Now() where you intended
=Date().

 

[Marshall Barton]

I've two table, say table1 and table2, and they have date type field [date1]
and [date2] respectively. They are in Medium Date format. I've tried to loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records were
missed at the result and it seems to be the date format problem. I've tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem.

I'll bet your dates were set using Now() instead of Date().
If so, use the DateValue function in the comparisons.

 

[Guest]

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04, however,
whose records with date such as 12-Nov-04 may be missing.

 

[Guest]

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04, however,
whose records with date such as 12-Nov-04 may be missing.

Internally, Access stores date/time fields as a number, where the whole
number part represents the date, and the fractional part represents the time
of day (.5 = nooon, .25 = 6am, ...).

Therefore the display format for the date should not be the problem.
However, if one of the field contains a time component, then they won't
match. That would be true even if you use a Medium Date format, which
suppresses the display of the time part.

Often, the time component is introduced if you use =Now() where you intended
=Date().

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

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

I've two table, say table1 and table2, and they have date type field
[date1]
and [date2] respectively. They are in Medium Date format. I've tried to
loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records were
missed at the result and it seems to be the date format problem. I've
tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem. Thanks

 

[Guest]

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04, however,
whose records with date such as 12-Nov-04 may be missing.

I've two table, say table1 and table2, and they have date type field [date1]
and [date2] respectively. They are in Medium Date format. I've tried to loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records were
missed at the result and it seems to be the date format problem. I've tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem.

I'll bet your dates were set using Now() instead of Date().
If so, use the DateValue function in the comparisons.

 

[Guest]

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04, however,
whose records with date such as 12-Nov-04 may be missing.

Are you storing time along with the date in those fields? That can cause the
query not to see the last date in your query's criterion range because the
value actually is larger than that date. If you're storing time, add 1 to
your date range's last date to include those values in the query.

Format of a date field is immaterial. ACCESS simply uses the format for
display purposes, not for how the data are stored. Date and time data are
stored as floating point numbers, where the integer portion of the number is
the number of days since December 20, 1899, and the fraction (decimal)
portion represents the time in terms of the fraction of 24 hours represented
by that time of day.

--

Ken Snell
<MS ACCESS MVP>

I've two table, say table1 and table2, and they have date type field [date1]
and [date2] respectively. They are in Medium Date format. I've tried to loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records were
missed at the result and it seems to be the date format problem. I've tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem. Thanks

 

[Allen Browne]

Okay, Warren.

Now ask Access to show you the fields as numbers. Create a query, and enter
this as a calculated field into a fresh column in the Field row:
CDbl(Nz([date1], #0:00:00#))

Do you see whole numbers returned? Fractional numbers? Do they match?

This should help you to determine whether the problem is caused by:
- wrong century in the date;
- time aspects;
- incorrect data types (e.g. Text, not Date)
- incorrect date formats.


BTW, since you are using a date format different to the US, you might find
this article helpful:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04, however,
whose records with date such as 12-Nov-04 may be missing.

I've two table, say table1 and table2, and they have date type field
[date1]
and [date2] respectively. They are in Medium Date format. I've tried to
loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records
were
missed at the result and it seems to be the date format problem. I've
tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem. Thanks

 

[Guest]

Dear Allen
By using your advised function, I made two queries for the tables and the
dates fields are successfully shown as double numbers. However, when the
matching actually take place the double numbers date are automatically return
to short date format...and the same happened.

Okay, Warren.

Now ask Access to show you the fields as numbers. Create a query, and enter
this as a calculated field into a fresh column in the Field row:
CDbl(Nz([date1], #0:00:00#))

Do you see whole numbers returned? Fractional numbers? Do they match?

This should help you to determine whether the problem is caused by:
- wrong century in the date;
- time aspects;
- incorrect data types (e.g. Text, not Date)
- incorrect date formats.


BTW, since you are using a date format different to the US, you might find
this article helpful:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04, however,
whose records with date such as 12-Nov-04 may be missing.

I've two table, say table1 and table2, and they have date type field
[date1]
and [date2] respectively. They are in Medium Date format. I've tried to
loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records
were
missed at the result and it seems to be the date format problem. I've
tried
to change the date fields in Long Date format, but it didn't work. Does
anyone tell me how to fix the problem. Thanks

 

[Allen Browne]

So:
- they DO match when displayed like this, and
- there is no fractional part to the numbers, but
- they DON'T match when you display them as dates?

Don't worry about the short date format. That's not relevant if they are
Date/Time fields. If you open your table in design view, they are date/time
fields aren't they? Or are they Text fields?

When you see the dates in your query, do they left-align (like text) or
right-align (like numbers)?

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

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

Dear Allen
By using your advised function, I made two queries for the tables and the
dates fields are successfully shown as double numbers. However, when the
matching actually take place the double numbers date are automatically
return
to short date format...and the same happened.

Okay, Warren.

Now ask Access to show you the fields as numbers. Create a query, and
enter
this as a calculated field into a fresh column in the Field row:
CDbl(Nz([date1], #0:00:00#))

Do you see whole numbers returned? Fractional numbers? Do they match?

This should help you to determine whether the problem is caused by:
- wrong century in the date;
- time aspects;
- incorrect data types (e.g. Text, not Date)
- incorrect date formats.


BTW, since you are using a date format different to the US, you might
find
this article helpful:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04,
however,
whose records with date such as 12-Nov-04 may be missing.


:

I've two table, say table1 and table2, and they have date type field
[date1]
and [date2] respectively. They are in Medium Date format. I've tried
to
loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records
were
missed at the result and it seems to be the date format problem. I've
tried
to change the date fields in Long Date format, but it didn't work.
Does
anyone tell me how to fix the problem. Thanks

 

[Guest]

I really worry about the short date format because the one of the date field
is at the right hand site of sql where statement. Such as
"table2![date2] = #" & table1![date1] & "#"
If the value of table1![date1] is #12-Nov-04# and is interpreted as
"12/11/2004" or ("11-Dec-04"), the right result in table2 should not be found.

So:
- they DO match when displayed like this, and
- there is no fractional part to the numbers, but
- they DON'T match when you display them as dates?

Don't worry about the short date format. That's not relevant if they are
Date/Time fields. If you open your table in design view, they are date/time
fields aren't they? Or are they Text fields?

When you see the dates in your query, do they left-align (like text) or
right-align (like numbers)?

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

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

Dear Allen
By using your advised function, I made two queries for the tables and the
dates fields are successfully shown as double numbers. However, when the
matching actually take place the double numbers date are automatically
return
to short date format...and the same happened.

Okay, Warren.

Now ask Access to show you the fields as numbers. Create a query, and
enter
this as a calculated field into a fresh column in the Field row:
CDbl(Nz([date1], #0:00:00#))

Do you see whole numbers returned? Fractional numbers? Do they match?

This should help you to determine whether the problem is caused by:
- wrong century in the date;
- time aspects;
- incorrect data types (e.g. Text, not Date)
- incorrect date formats.


BTW, since you are using a date format different to the US, you might
find
this article helpful:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04,
however,
whose records with date such as 12-Nov-04 may be missing.


:

I've two table, say table1 and table2, and they have date type field
[date1]
and [date2] respectively. They are in Medium Date format. I've tried
to
loop
through the table1 to find records in table2 by using a combination of
conditions with matching of [date1] to [date2]. Some expected records
were
missed at the result and it seems to be the date format problem. I've
tried
to change the date fields in Long Date format, but it didn't work.
Does
anyone tell me how to fix the problem. Thanks

 

[Allen Browne]

Ouch! If you are concatenating literal date values into a SQL statement, you
*must* force them into the mm/dd/yyyy format, i.e.:
"table2![date2] = #" & Format([date1], "mm/dd/yyyy") & "#"

The explanation is in the article I already referred you to. That article
has come from 12 years experience working with Access in a dd/mm/yy country.

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

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

I really worry about the short date format because the one of the date
field
is at the right hand site of sql where statement. Such as
"table2![date2] = #" & table1![date1] & "#"
If the value of table1![date1] is #12-Nov-04# and is interpreted as
"12/11/2004" or ("11-Dec-04"), the right result in table2 should not be
found.

So:
- they DO match when displayed like this, and
- there is no fractional part to the numbers, but
- they DON'T match when you display them as dates?

Don't worry about the short date format. That's not relevant if they are
Date/Time fields. If you open your table in design view, they are
date/time
fields aren't they? Or are they Text fields?

When you see the dates in your query, do they left-align (like text) or
right-align (like numbers)?

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

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

Dear Allen
By using your advised function, I made two queries for the tables and
the
dates fields are successfully shown as double numbers. However, when
the
matching actually take place the double numbers date are automatically
return
to short date format...and the same happened.

:

Okay, Warren.

Now ask Access to show you the fields as numbers. Create a query, and
enter
this as a calculated field into a fresh column in the Field row:
CDbl(Nz([date1], #0:00:00#))

Do you see whole numbers returned? Fractional numbers? Do they match?

This should help you to determine whether the problem is caused by:
- wrong century in the date;
- time aspects;
- incorrect data types (e.g. Text, not Date)
- incorrect date formats.


BTW, since you are using a date format different to the US, you might
find
this article helpful:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04,
however,
whose records with date such as 12-Nov-04 may be missing.


:

I've two table, say table1 and table2, and they have date type
field
[date1]
and [date2] respectively. They are in Medium Date format. I've
tried
to
loop
through the table1 to find records in table2 by using a combination
of
conditions with matching of [date1] to [date2]. Some expected
records
were
missed at the result and it seems to be the date format problem.
I've
tried
to change the date fields in Long Date format, but it didn't work.
Does
anyone tell me how to fix the problem. Thanks

 

[Guest]

The problem was solved. Thank you for your help.

Ouch! If you are concatenating literal date values into a SQL statement, you
*must* force them into the mm/dd/yyyy format, i.e.:
"table2![date2] = #" & Format([date1], "mm/dd/yyyy") & "#"

The explanation is in the article I already referred you to. That article
has come from 12 years experience working with Access in a dd/mm/yy country.

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

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

I really worry about the short date format because the one of the date
field
is at the right hand site of sql where statement. Such as
"table2![date2] = #" & table1![date1] & "#"
If the value of table1![date1] is #12-Nov-04# and is interpreted as
"12/11/2004" or ("11-Dec-04"), the right result in table2 should not be
found.

So:
- they DO match when displayed like this, and
- there is no fractional part to the numbers, but
- they DON'T match when you display them as dates?

Don't worry about the short date format. That's not relevant if they are
Date/Time fields. If you open your table in design view, they are
date/time
fields aren't they? Or are they Text fields?

When you see the dates in your query, do they left-align (like text) or
right-align (like numbers)?

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

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

Dear Allen
By using your advised function, I made two queries for the tables and
the
dates fields are successfully shown as double numbers. However, when
the
matching actually take place the double numbers date are automatically
return
to short date format...and the same happened.

:

Okay, Warren.

Now ask Access to show you the fields as numbers. Create a query, and
enter
this as a calculated field into a fresh column in the Field row:
CDbl(Nz([date1], #0:00:00#))

Do you see whole numbers returned? Fractional numbers? Do they match?

This should help you to determine whether the problem is caused by:
- wrong century in the date;
- time aspects;
- incorrect data types (e.g. Text, not Date)
- incorrect date formats.


BTW, since you are using a date format different to the US, you might
find
this article helpful:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

There is no time information on either date fields.
I've got the right result records with clear date, eg 16-Nov-04,
however,
whose records with date such as 12-Nov-04 may be missing.


:

I've two table, say table1 and table2, and they have date type
field
[date1]
and [date2] respectively. They are in Medium Date format. I've
tried
to
loop
through the table1 to find records in table2 by using a combination
of
conditions with matching of [date1] to [date2]. Some expected
records
were
missed at the result and it seems to be the date format problem.
I've
tried
to change the date fields in Long Date format, but it didn't work.
Does
anyone tell me how to fix the problem. Thanks