Query table by the date

[Nicolae Fieraru]

Hi All,

I have a table tblProducts where I have four fields:\
Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate
(Date/Time - Medium Date)

The EnterDate is automatically filled (with Now()) and the purchase date is
entered manually.
Meantime I became aware that instead of Now() I should use Date() for the
date
The table had already records in it before I start working on it. Now I try
to query the table for all the records
where EnterDate = #25/06/2004#

The problem I have is that the query works fine only for the records where
the EnterDate contains only the date and not the time stamp. If I go in the
table and I click on EnterDate field for different records, I can see that
some of them are listed as 24-Jun-04 and stay like that when I click on
them, while others are listed as 25-June-2004 and change to 25/06/2004
11:34:44 AM when I click on them. How should I create a query which is able
to pick both types of EnterDate? At present my query works only for the
dates which weren't generated with Now()

Regards,
Nicolae

[Allen Browne]

Use an Update query to drop the time component from your existing fields.

1. Create a query into this table.

2. Change it to an Update: Update on Query menu.
Access adds an Update row to the grid.

3. Drag EnterDate field into the grid.

4. In the Update row beneath this field, enter:
DateValue([EnterDate])

5. In the Criteria row beneath this field, enter:
Is Not Null

6. Run the query.

This replaces the date/time value in all records with just the date value.

Presumably you have already changed the Default Value property of the field
to:
=Date()
to take care of future records as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi All,

I have a table tblProducts where I have four fields:\
Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate
(Date/Time - Medium Date)

The EnterDate is automatically filled (with Now()) and the purchase date is entered manually.
Meantime I became aware that instead of Now() I should use Date() for the
date
The table had already records in it before I start working on it. Now I try to query the table for all the records
where EnterDate = #25/06/2004#

The problem I have is that the query works fine only for the records where
the EnterDate contains only the date and not the time stamp. If I go in the table and I click on EnterDate field for different records, I can see that
some of them are listed as 24-Jun-04 and stay like that when I click on
them, while others are listed as 25-June-2004 and change to 25/06/2004
11:34:44 AM when I click on them. How should I create a query which is able to pick both types of EnterDate? At present my query works only for the
dates which weren't generated with Now()

Regards,
Nicolae

[Nicolae Fieraru]

Hi Allen,

Thank you very much. I will do that, it is better to have the right kind of
data in the table. The problem is I am working with a small set of data, and
the real back end database is at the customer location. I will have to run
the query there.
Meantime I found a solution to be able to query the database, no matter of
the type of date I have in the table.
I used this kind of formatting:

SELECT ...
WHERE
(Format([DateEntered],"dd\/mm\/yyyy")=Forms!frmCustomersByDate!txtDate));

This gives me all the records, no matter if the date includes the time or
not.

Regards,
Nicolae

[Allen Browne]

Okay. Can see what you are doing.

Because Access was designed in the US where they format their dates as
mm/dd/yyyy, there is a chance that your WHERE clause will be incorrectly
interpreted.

To make sure that doesn't happen:
1. If txtDate is an unbound text box, set its Format property to Short Date
so that Access knows it is a date (and also won't accept invalid entries.

2. Use DateValue() so you are dealing with a true date rather than a string
representation of the date (which Format() gives).

3. Declare the parameter in your query. In query design view, choose
Parameters from the Query menu, and in the dialog box, enter:
[Forms]![frmCustomersByDate]![txtDate] Date/Time

That comes from 11 years experience with Access in a dd/mm/yyy country. For
an explanation on avoiding these problems in general, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40******@duster.adelaide.on.net...

Thank you very much. I will do that, it is better to have the right kind of data in the table. The problem is I am working with a small set of data, and the real back end database is at the customer location. I will have to run the query there.
Meantime I found a solution to be able to query the database, no matter of
the type of date I have in the table.
I used this kind of formatting:

SELECT ...
WHERE
(Format([DateEntered],"dd\/mm\/yyyy")=Forms!frmCustomersByDate!txtDate));

This gives me all the records, no matter if the date includes the time or
not.

Regards,
Nicolae

[Nicolae Fieraru]

Thank you very much, Allen. That information is very valuable.
I am not sure what parameters are. A few weeks ago I start building queries
by hand (in sql mode) and I defined parameters in the first line. And I
added those parameters in the where condition. But when I learnt to create
queries in the design mode, I noticed the parameters in the first line
weren't present anymore. What is the point of defining parameters, when I
can write straight in the Where conditon, like:
Where (tbTable.Field1 = Forms!MyForm!txtBox)
?

And I am not sure, but it seems that because of the first line, where I
defined parameters, I couldn't export the query to a mailmerge. Once we
removed the parameters line, we could create the mailmerge documents... (I
am not 100% sure, have to test more)
Regards,
Nicolae

[Allen Browne]

A "parameter" is a value you supply at runtime for the query.
It causes Access to pop up a dialog asking for a value.

Any name that Access can't resolve to be a table or field name or a valid
word for SQL, it assumes to be a parameter. For example, you get the
parameter box if you misspell a field name.

While it is not essential to declare your parameters, it does help Access to
understand the data type and provide basic type-checking. For example, if
you declare a Date/Time parameter, and the user enters:
29/2/2005
Access will reprompt the user (because 2005 is not a leap year). If you do
not declare the parameter, and the user enters:
12/2/2005
will that be interpreted as Dec 2 or 12 Feb?
What about:
13/2/2005
Are you surprised at the difference between the last 2 examples?

How about:
29/2
Is that interpreted differently in a leap year than a normal year?
Or is there a chance that might Access interpret that entry as 29 divided by
2 = 14.5, which internally represents the date/time of noon on 13 Jan 1900?

There is too much scope for misunderstanding if you leave your parameters
undeclared and untyped.

The issue also arises for calculated fields in your queries. See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Thank you very much, Allen. That information is very valuable.
I am not sure what parameters are. A few weeks ago I start building queries by hand (in sql mode) and I defined parameters in the first line. And I
added those parameters in the where condition. But when I learnt to create
queries in the design mode, I noticed the parameters in the first line
weren't present anymore. What is the point of defining parameters, when I
can write straight in the Where conditon, like:
Where (tbTable.Field1 = Forms!MyForm!txtBox)
?

And I am not sure, but it seems that because of the first line, where I
defined parameters, I couldn't export the query to a mailmerge. Once we
removed the parameters line, we could create the mailmerge documents... (I
am not 100% sure, have to test more)
Regards,
Nicolae

[Reggie]

Allen, Great info. Made me open my eye's! I normally declare my parameters,
but more out of habit. I see the light now!

--
Reggie

----------
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

A "parameter" is a value you supply at runtime for the query.
It causes Access to pop up a dialog asking for a value.

Any name that Access can't resolve to be a table or field name or a valid
word for SQL, it assumes to be a parameter. For example, you get the
parameter box if you misspell a field name.

While it is not essential to declare your parameters, it does help Access to understand the data type and provide basic type-checking. For example, if
you declare a Date/Time parameter, and the user enters:
29/2/2005
Access will reprompt the user (because 2005 is not a leap year). If you do
not declare the parameter, and the user enters:
12/2/2005
will that be interpreted as Dec 2 or 12 Feb?
What about:
13/2/2005
Are you surprised at the difference between the last 2 examples?

How about:
29/2
Is that interpreted differently in a leap year than a normal year?
Or is there a chance that might Access interpret that entry as 29 divided by 2 = 14.5, which internally represents the date/time of noon on 13 Jan 1900?
There is too much scope for misunderstanding if you leave your parameters
undeclared and untyped.

The issue also arises for calculated fields in your queries. See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Thank you very much, Allen. That information is very valuable.
I am not sure what parameters are. A few weeks ago I start building

queries

by hand (in sql mode) and I defined parameters in the first line. And I
added those parameters in the where condition. But when I learnt to create queries in the design mode, I noticed the parameters in the first line
weren't present anymore. What is the point of defining parameters, when I can write straight in the Where conditon, like:
Where (tbTable.Field1 = Forms!MyForm!txtBox)
?

And I am not sure, but it seems that because of the first line, where I
defined parameters, I couldn't export the query to a mailmerge. Once we
removed the parameters line, we could create the mailmerge documents... (I am not 100% sure, have to test more)
Regards,
Nicolae

[Nicolae Fieraru]

Thank you again, Allen. It is obvious about the advantages of declaring the
parameters. I've been criticised for typing the queries by hand and
declaring the parameters, but in the end it isn't that bad. Anyway, it is a
lot easier to use the query builder which comes with Access, but at least I
can declare the parameters after I have the query working

Regards,
Nicolae