Between Date and Time Query

[Guest]

I am trying to pull information from a query, I am using a FORM that I
populate two text boxes (0 and 2), whereas I am looking for all records
between start date ##/##/#### and End date ##/##/####. However the records
that the text boxes are looking-up have a different format (##/##/####
12:35:34 am). How do I capture all of the data between using just the (date)
and not the (date and time).
TFTH,
Tom

 

[PC D]

Access stores date/time data in decimal format. The date is represented by
the value at the left of the decimal point and time is represented bybthe
value at the right of the decimal point. To achieve what you want, change
your date/time field in your query to a calculated field that looks like:
MyDate:Int([MyDateField])
Now enter this expression for the criteria:
Between [Please Enter Start Date] And [Please Enter End Date]

If you are using a form to enter the Start and End dates, use this
expression for the criteria:
Betweem Forms!MyForm!MyStartDate And Forms!MyForm!MyEndDate

 

[StopThisAdvertising]

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
(e-mail address removed)

--
To Steve:

Why PC D this time? Why not PCD anymore, or Access Resource or Help available or why not just PC DataSheet?
You think that changing names is changing anything?
Why don't you just get lost? No-one wants your advertising/job hunting here!
Over 600!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[Dirk Goldgar]

I am trying to pull information from a query, I am using a FORM that I
populate two text boxes (0 and 2), whereas I am looking for all
records between start date ##/##/#### and End date ##/##/####.
However the records that the text boxes are looking-up have a
different format (##/##/#### 12:35:34 am). How do I capture all of
the data between using just the (date) and not the (date and time).
TFTH,
Tom

For example,

SELECT * FROM MyTable
WHERE DateField >= [Forms]![MyForm]![StartDate]
AND DateField < ([Forms]![MyForm]![EndDate] + 1)

 

[Guest]

Dirk,
Thank you for your response, however I do not understand where I should use
your statement. Please explain, as I am just learning access.
TFTH,
Tom

I am trying to pull information from a query, I am using a FORM that I
populate two text boxes (0 and 2), whereas I am looking for all
records between start date ##/##/#### and End date ##/##/####.
However the records that the text boxes are looking-up have a
different format (##/##/#### 12:35:34 am). How do I capture all of
the data between using just the (date) and not the (date and time).
TFTH,
Tom

For example,

SELECT * FROM MyTable
WHERE DateField >= [Forms]![MyForm]![StartDate]
AND DateField < ([Forms]![MyForm]![EndDate] + 1)


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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
Thank you for your response, however I do not understand where I
should use your statement. Please explain, as I am just learning
access.
TFTH,
Tom

For example,

SELECT * FROM MyTable
WHERE DateField >= [Forms]![MyForm]![StartDate]
AND DateField < ([Forms]![MyForm]![EndDate] + 1)

What I gave you was the SQL view of a query, using as example names
"MyTable" for the table name, "DateField" for the name of the date field
in the table, "MyForm" for the name of the form, and "StartDate" and
"EndDate" for the names of the limiting text boxes on the form. If
you're building your query in Design View, you can do the same filtering
by entering

= [Forms]![MyForm]![StartDate] And < ([Forms]![MyForm]![EndDate] +

1)

(all on one line)

on the Criteria line under the date field in the design grid.

How is your form set up? Will it display both the start- and end-date
text boxes and the data from the query, or will you open another form or
query datasheet to show the resulting records?

 

[Guest]

Dirk,
I am currently using a form with two text boxes in which I am populating
each with a date. I tried your criteria, however it was giving me and error
message stating the the argument was too complex.
I am going to try and reformat the text boxes so that they will accept a
"date and time" entry which should make the filter criteria work more easily.
Any other ideas?
TFTH,'
Tom

Dirk,
Thank you for your response, however I do not understand where I
should use your statement. Please explain, as I am just learning
access.
TFTH,
Tom

For example,

SELECT * FROM MyTable
WHERE DateField >= [Forms]![MyForm]![StartDate]
AND DateField < ([Forms]![MyForm]![EndDate] + 1)

What I gave you was the SQL view of a query, using as example names
"MyTable" for the table name, "DateField" for the name of the date field
in the table, "MyForm" for the name of the form, and "StartDate" and
"EndDate" for the names of the limiting text boxes on the form. If
you're building your query in Design View, you can do the same filtering
by entering

= [Forms]![MyForm]![StartDate] And < ([Forms]![MyForm]![EndDate] +

1)

(all on one line)

on the Criteria line under the date field in the design grid.

How is your form set up? Will it display both the start- and end-date
text boxes and the data from the query, or will you open another form or
query datasheet to show the resulting records?

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
I am currently using a form with two text boxes in which I am
populating each with a date. I tried your criteria, however it was
giving me and error message stating the the argument was too complex.

Probably you made a mistake in specifying the criterion.

I am going to try and reformat the text boxes so that they will
accept a "date and time" entry which should make the filter criteria
work more easily.

It probably would, but it wouldn't correspond to the way people want to
specify start and end dates. So it might solve your technical problem,
but it would be cumbersome to use.

Any other ideas?

I think it would be better to get the form to work the way it is.
Please post the following:

+ the names of the start- and end-date text boxes
+ the name of the form
+ the recordsource of the form. Assuming that's a query, post the SQL
of that query.

And how is this supposed to work in practice? Will the user have to
click on a command button after entering start- and end-dates, or should
the form automatically requery itself when either of those text boxes is
changed?

 

[Guest]

Dirk,
I think you are right... I will have to requery the entered dates. I also
did have to change the criterion, which it is now working very well.
Thanks,
Tom