Date in Query

[Kenneth]

I have a Query that consist of a lot of different sales data, and one of the
colums are different date. The date goes from 1jan2003 til 31jan2003. in
this Query I only want the salesdata for 1jan2003. How do I remove the dates
, 2jan2003 til 31jan2003 without removing them from the table, from the
Query? (Because I want to use the data for 2jan2003 etc later in other
queries)

-kenneth

[Wayne Morgan]

Re: Date in Query

Example:

Select * From tblMyTable Where DateField = #1/1/2003#

This will select all records from the table where the date field is 1 Jan
2003. The query wants the date formatted in US format. The # signs are date
delimiters so that Access knows that this is a date and not division. If you
are going to make multiple queries such as this one, you may want to look in
the help file for Parameter Queries. This will cause the query to prompt you
for the date when you run it.

--
Wayne Morgan
Microsoft Access MVP


"Kenneth" <snow_flea@hotm ail.com> wrote in message
news:c6r9te$mem $1@dolly.uninet t.no...[color=blue]
> I have a Query that consist of a lot of different sales data, and one of[/color]
the[color=blue]
> colums are different date. The date goes from 1jan2003 til 31jan2003. in
> this Query I only want the salesdata for 1jan2003. How do I remove the[/color]
dates[color=blue]
> , 2jan2003 til 31jan2003 without removing them from the table, from the
> Query? (Because I want to use the data for 2jan2003 etc later in other
> queries)
>
> -kenneth
>
>[/color]

[Kenneth]

Re: Date in Query

I have tried the formula:
Select * From tblMyTable Where DateField = #1/1/2003#

and changed MyTable to Date, which is the name of my column with date, but
it doesn't work.

Have I done anything wrong?

-kenneth

--
"Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:VJbkc.314$ Uz.312@newssvr1 6.news.prodigy. com...[color=blue]
> Example:
>
> Select * From tblMyTable Where DateField = #1/1/2003#
>
> This will select all records from the table where the date field is 1 Jan
> 2003. The query wants the date formatted in US format. The # signs are[/color]
date[color=blue]
> delimiters so that Access knows that this is a date and not division. If[/color]
you[color=blue]
> are going to make multiple queries such as this one, you may want to look[/color]
in[color=blue]
> the help file for Parameter Queries. This will cause the query to prompt[/color]
you[color=blue]
> for the date when you run it.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>
>
> "Kenneth" <snow_flea@hotm ail.com> wrote in message
> news:c6r9te$mem $1@dolly.uninet t.no...[color=green]
> > I have a Query that consist of a lot of different sales data, and one of[/color]
> the[color=green]
> > colums are different date. The date goes from 1jan2003 til 31jan2003. in
> > this Query I only want the salesdata for 1jan2003. How do I remove the[/color]
> dates[color=green]
> > , 2jan2003 til 31jan2003 without removing them from the table, from the
> > Query? (Because I want to use the data for 2jan2003 etc later in other
> > queries)
> >
> > -kenneth
> >
> >[/color]
>
>[/color]

[Salad]

Re: Date in Query

Kenneth wrote:
[color=blue]
> I have tried the formula:
> Select * From tblMyTable Where DateField = #1/1/2003#
>
> and changed MyTable to Date, which is the name of my column with date, but
> it doesn't work.
>
> Have I done anything wrong?[/color]

Yes. What is your table name? Go to the database window and select
Tables. Find out what the table name is your are designing your query
around. Tmen substitute tblMyTable to that name. Now open the table in
design mode. Determine the name of your date field to filter on. Close
the table. Substitute DateField with the name of the field.

Did you have a brain freeze :-)

[color=blue]
>
> -kenneth
>
> --
> "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
> news:VJbkc.314$ Uz.312@newssvr1 6.news.prodigy. com...
>[color=green]
>>Example:
>>
>>Select * From tblMyTable Where DateField = #1/1/2003#
>>
>>This will select all records from the table where the date field is 1 Jan
>>2003. The query wants the date formatted in US format. The # signs are[/color]
>
> date
>[color=green]
>>delimiters so that Access knows that this is a date and not division. If[/color]
>
> you
>[color=green]
>>are going to make multiple queries such as this one, you may want to look[/color]
>
> in
>[color=green]
>>the help file for Parameter Queries. This will cause the query to prompt[/color]
>
> you
>[color=green]
>>for the date when you run it.
>>[/color][/color]

[Kenneth]

Re: Date in Query

The name of my table is "deliveries ", and my column inside that table is
named "date".
The formula now is then:
Expr1: (Select * From [levering] Where date = #1/1/2003#)
but this gives me an errormessage:
"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
select statement of the subquery to request only one field."

How do I "Determine the name of your date field to filter on"?

-kenneth

....my brain needs a quick start up :)

--

"Salad" <oil@vinegar.co m> wrote in message
news:C6ekc.1444 9$eZ5.9651@news read1.news.pas. earthlink.net.. .[color=blue]
> Kenneth wrote:
>[color=green]
> > I have tried the formula:
> > Select * From tblMyTable Where DateField = #1/1/2003#
> >
> > and changed MyTable to Date, which is the name of my column with date,[/color][/color]
but[color=blue][color=green]
> > it doesn't work.
> >
> > Have I done anything wrong?[/color]
>
> Yes. What is your table name? Go to the database window and select
> Tables. Find out what the table name is your are designing your query
> around. Tmen substitute tblMyTable to that name. Now open the table in
> design mode. Determine the name of your date field to filter on. Close
> the table. Substitute DateField with the name of the field.
>
> Did you have a brain freeze :-)
>
>[color=green]
> >
> > -kenneth
> >
> > --
> > "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:VJbkc.314$ Uz.312@newssvr1 6.news.prodigy. com...
> >[color=darkred]
> >>Example:
> >>
> >>Select * From tblMyTable Where DateField = #1/1/2003#
> >>
> >>This will select all records from the table where the date field is 1[/color][/color][/color]
Jan[color=blue][color=green][color=darkred]
> >>2003. The query wants the date formatted in US format. The # signs are[/color]
> >
> > date
> >[color=darkred]
> >>delimiters so that Access knows that this is a date and not division. If[/color]
> >
> > you
> >[color=darkred]
> >>are going to make multiple queries such as this one, you may want to[/color][/color][/color]
look[color=blue][color=green]
> >
> > in
> >[color=darkred]
> >>the help file for Parameter Queries. This will cause the query to prompt[/color]
> >
> > you
> >[color=darkred]
> >>for the date when you run it.
> >>[/color][/color]
>[/color]

[Jim Allensworth]

Re: Date in Query

You are not using "deliveries " as the table. Also you have named a
column using an Access reserved word (date) - you are most likely
confusing Access. Rename the field to something else (not reserved,
that is). The SQL statement should be fine then.

Select * From deliveries Where Mydate = #1/1/2003#

- Jim

On Thu, 29 Apr 2004 23:59:21 +0200, "Kenneth" <snow_flea@hotm ail.com>
wrote:
[color=blue]
>The name of my table is "deliveries ", and my column inside that table is
>named "date".
>The formula now is then:
>Expr1: (Select * From [levering] Where date = #1/1/2003#)
>but this gives me an errormessage:
>"You have written a subquery that can return more than one field without
>using the EXISTS reserved word in the main query's FROM clause. Revise the
>select statement of the subquery to request only one field."
>
>How do I "Determine the name of your date field to filter on"?
>
>-kenneth
>
>...my brain needs a quick start up :)
>
>--
>
>"Salad" <oil@vinegar.co m> wrote in message
>news:C6ekc.144 49$eZ5.9651@new sread1.news.pas .earthlink.net. ..[color=green]
>> Kenneth wrote:
>>[color=darkred]
>> > I have tried the formula:
>> > Select * From tblMyTable Where DateField = #1/1/2003#
>> >
>> > and changed MyTable to Date, which is the name of my column with date,[/color][/color]
>but[color=green][color=darkred]
>> > it doesn't work.
>> >
>> > Have I done anything wrong?[/color]
>>
>> Yes. What is your table name? Go to the database window and select
>> Tables. Find out what the table name is your are designing your query
>> around. Tmen substitute tblMyTable to that name. Now open the table in
>> design mode. Determine the name of your date field to filter on. Close
>> the table. Substitute DateField with the name of the field.
>>
>> Did you have a brain freeze :-)
>>
>>[color=darkred]
>> >
>> > -kenneth
>> >
>> > --
>> > "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in[/color][/color]
>message[color=green][color=darkred]
>> > news:VJbkc.314$ Uz.312@newssvr1 6.news.prodigy. com...
>> >
>> >>Example:
>> >>
>> >>Select * From tblMyTable Where DateField = #1/1/2003#
>> >>
>> >>This will select all records from the table where the date field is 1[/color][/color]
>Jan[color=green][color=darkred]
>> >>2003. The query wants the date formatted in US format. The # signs are
>> >
>> > date
>> >
>> >>delimiters so that Access knows that this is a date and not division. If
>> >
>> > you
>> >
>> >>are going to make multiple queries such as this one, you may want to[/color][/color]
>look[color=green][color=darkred]
>> >
>> > in
>> >
>> >>the help file for Parameter Queries. This will cause the query to prompt
>> >
>> > you
>> >
>> >>for the date when you run it.
>> >>[/color]
>>[/color]
>
>[/color]

[Kenneth]

Re: Date in Query

My Tables is still named deliveries, but my date-column is named "MyDate"
now.
My formula is like this:
Expr1: [Select * From deliveries Where MyDate = #1/1/2003#]

but when I select the "query view" then I get this message:
"Enter Parameter Value. select * From deliveries Where MyDate = #1/1/2003#"

I still see all my rows (with date 1/1/2003, 2/1/2003 etc.) but the new
column consist of whatever I add in the "Enter Parameter Value"-field.

What is wrong this time, and what should I do?

-kenneth

--

"Jim Allensworth" <jimNOT@Notdata centricsolution s.com> wrote in message
news:40917d31.2 80703781@news.w est.earthlink.n et...[color=blue]
> You are not using "deliveries " as the table. Also you have named a
> column using an Access reserved word (date) - you are most likely
> confusing Access. Rename the field to something else (not reserved,
> that is). The SQL statement should be fine then.
>
> Select * From deliveries Where Mydate = #1/1/2003#
>
> - Jim
>
> On Thu, 29 Apr 2004 23:59:21 +0200, "Kenneth" <snow_flea@hotm ail.com>
> wrote:
>[color=green]
> >The name of my table is "deliveries ", and my column inside that table is
> >named "date".
> >The formula now is then:
> >Expr1: (Select * From [levering] Where date = #1/1/2003#)
> >but this gives me an errormessage:
> >"You have written a subquery that can return more than one field without
> >using the EXISTS reserved word in the main query's FROM clause. Revise[/color][/color]
the[color=blue][color=green]
> >select statement of the subquery to request only one field."
> >
> >How do I "Determine the name of your date field to filter on"?
> >
> >-kenneth
> >
> >...my brain needs a quick start up :)
> >
> >--
> >
> >"Salad" <oil@vinegar.co m> wrote in message
> >news:C6ekc.144 49$eZ5.9651@new sread1.news.pas .earthlink.net. ..[color=darkred]
> >> Kenneth wrote:
> >>
> >> > I have tried the formula:
> >> > Select * From tblMyTable Where DateField = #1/1/2003#
> >> >
> >> > and changed MyTable to Date, which is the name of my column with[/color][/color][/color]
date,[color=blue][color=green]
> >but[color=darkred]
> >> > it doesn't work.
> >> >
> >> > Have I done anything wrong?
> >>
> >> Yes. What is your table name? Go to the database window and select
> >> Tables. Find out what the table name is your are designing your query
> >> around. Tmen substitute tblMyTable to that name. Now open the table[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> >> design mode. Determine the name of your date field to filter on.[/color][/color][/color]
Close[color=blue][color=green][color=darkred]
> >> the table. Substitute DateField with the name of the field.
> >>
> >> Did you have a brain freeze :-)
> >>
> >>
> >> >
> >> > -kenneth
> >> >
> >> > --
> >> > "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in[/color]
> >message[color=darkred]
> >> > news:VJbkc.314$ Uz.312@newssvr1 6.news.prodigy. com...
> >> >
> >> >>Example:
> >> >>
> >> >>Select * From tblMyTable Where DateField = #1/1/2003#
> >> >>
> >> >>This will select all records from the table where the date field is 1[/color]
> >Jan[color=darkred]
> >> >>2003. The query wants the date formatted in US format. The # signs[/color][/color][/color]
are[color=blue][color=green][color=darkred]
> >> >
> >> > date
> >> >
> >> >>delimiters so that Access knows that this is a date and not division.[/color][/color][/color]
If[color=blue][color=green][color=darkred]
> >> >
> >> > you
> >> >
> >> >>are going to make multiple queries such as this one, you may want to[/color]
> >look[color=darkred]
> >> >
> >> > in
> >> >
> >> >>the help file for Parameter Queries. This will cause the query to[/color][/color][/color]
prompt[color=blue][color=green][color=darkred]
> >> >
> >> > you
> >> >
> >> >>for the date when you run it.
> >> >>
> >>[/color]
> >
> >[/color]
>[/color]

[Salad]

Re: Date in Query

Kenneth wrote:[color=blue]
> My Tables is still named deliveries, but my date-column is named "MyDate"
> now.
> My formula is like this:
> Expr1: [Select * From deliveries Where MyDate = #1/1/2003#]
>
> but when I select the "query view" then I get this message:
> "Enter Parameter Value. select * From deliveries Where MyDate = #1/1/2003#"
>
> I still see all my rows (with date 1/1/2003, 2/1/2003 etc.) but the new
> column consist of whatever I add in the "Enter Parameter Value"-field.
>
> What is wrong this time, and what should I do?
>
> -kenneth[/color]

I don't think Access wants you to enter into one column your entire
select statement. BTW. the [] around your statement indicates to
Access that your select statement is a field. If in a criteria row, a
parameter.

The easiest way to get you back on track is to click on the query tab,
select New/Design. Select your Delivery table, click Close. Now drag
the * in the table delivery table to the field row. Drag the field
MyDate to the second row. Uncheck the SHOW checkbox. Enter #1/1/2003#
in the criteria row for MyDate.

Click View/Datasheet from the menu to check the results. Click View/SQL
to see the SQL Statement.

One other thing. You could copy the SQL statement in your expression,
and then click View/SQL and cut out whatever you currently have and
paste in the statement from your expression.

Definite brain freeze.

[Megan]

Re: Date in Query

Hi-

I have a table with dates in it also. I usually use a parameter
expression in the criteria box for the field I want to use.

For example: I have a field named ContactDate. In "Design View" of my
query, I enter the following criteria for ContactDate:

Between [Enter Beginning Date] And [Enter Ending Date]

Then when I run the query, I can enter the beginning date and the
ending date.

The text between the braces, [], can be anything you want. I usually
say "Enter Beginning Date" and "Enter Ending Date" so that the user
knows what to enter. The text between the braces, [], are instructions
for what you want the user to enter. For example if you're searching
for a country, it would be: [Enter a Country]. The limiting is done by
the keywords, "Between" and "And."

However when I tried the criteria: [Enter a Date] for ContactDate, my
query only returned 1 record when I have 3. So, I'm not sure how well
it will work for a single date with multiple records.

Well I hope this gave you another idea even though I don't know how to
get a single date to return more than 1 record. I'm looking through
one of my books, Microsoft Access 2002 Inside Out. If I find an
answer, I'll post it!

Hope this gives you an idea,

Megan

[Salad]

Re: Date in Query

Megan wrote:
[color=blue]
> Hi-
>
> I have a table with dates in it also. I usually use a parameter
> expression in the criteria box for the field I want to use.
>
> For example: I have a field named ContactDate. In "Design View" of my
> query, I enter the following criteria for ContactDate:
>
> Between [Enter Beginning Date] And [Enter Ending Date]
>
> Then when I run the query, I can enter the beginning date and the
> ending date.
>
> The text between the braces, [], can be anything you want. I usually
> say "Enter Beginning Date" and "Enter Ending Date" so that the user
> knows what to enter. The text between the braces, [], are instructions
> for what you want the user to enter. For example if you're searching
> for a country, it would be: [Enter a Country]. The limiting is done by
> the keywords, "Between" and "And."
>
> However when I tried the criteria: [Enter a Date] for ContactDate, my
> query only returned 1 record when I have 3. So, I'm not sure how well
> it will work for a single date with multiple records.[/color]

It should work like a champ.[color=blue]
>
> Well I hope this gave you another idea even though I don't know how to
> get a single date to return more than 1 record. I'm looking through
> one of my books, Microsoft Access 2002 Inside Out. If I find an
> answer, I'll post it![/color]

I create a table called Table1. I added 3 date records. The first
record I stored Date(), the next 2 I stored Now(). I then ran a query
to select records for today. The query returned 1 record as expected.

Date stores the date and also the time of 00:00:00. So when the
criteria is Date and you have some now values ex: 4/30/2004 12:01:13
then that will never equal 4/30/2004 00:00:00.

If you have Now()'s then you need to check for equal Date and less than
Date+1.

I am curious. I have never used the [Enter Date] method in any of my
queries, forms, or reports. Do you use that method in an application or
do you use that because you mostly work with your data in the database
window? Or do you pass parameters to the query? I've seen it where the
developer had about 5 prompts in the query and each time the popup was
displayed to enter data I became more frustrated and wondered how many
more prompts I'd have to answer until I hit pay dirt.
[color=blue]
>
> Hope this gives you an idea,
>
> Megan[/color]

[Wayne Morgan]

Re: Date in Query

Ken,

Access won't accept a subquery in an expession like this. If there is only
one record that will be returned by the expression, you can use DLookup
instead. What is/are the table(s) in the query in which you are trying to
add this expression?

--
Wayne Morgan
MS Access MVP


"Kenneth" <snow_flea@hotm ail.com> wrote in message
news:c6t25t$7m7 $1@dolly.uninet t.no...[color=blue]
> My Tables is still named deliveries, but my date-column is named "MyDate"
> now.
> My formula is like this:
> Expr1: [Select * From deliveries Where MyDate = #1/1/2003#]
>
> but when I select the "query view" then I get this message:
> "Enter Parameter Value. select * From deliveries Where MyDate =[/color]
#1/1/2003#"[color=blue]
>
> I still see all my rows (with date 1/1/2003, 2/1/2003 etc.) but the new
> column consist of whatever I add in the "Enter Parameter Value"-field.
>
> What is wrong this time, and what should I do?
>
> -kenneth[/color]