Dates

  • Thread starter Thread starter Derek
  • Start date Start date
D

Derek

I am trying to make a query that will take 2 dates from a form and find all
the records between the 2 dates. I have used the suggested coding but am
getting 0 results. I have pasted the code below. Any help would be
appreciated.

Thanks.
Derek

Between [forms]![frmdaytrans]![txtfdate] And [forms]![frmdaytrans]![txttdate]
 
Is the field with the dates a DateTime datatype or a text field?
If DateTime then try --
Between CVDate([forms]![frmdaytrans]![txtfdate]) And
CVDate([forms]![frmdaytrans]![txttdate])
 
It is a date time field. I tried your suggestion and still receive no
results. I even created a new Query and just had the date field in it and
still received no results.

KARL DEWEY said:
Is the field with the dates a DateTime datatype or a text field?
If DateTime then try --
Between CVDate([forms]![frmdaytrans]![txtfdate]) And
CVDate([forms]![frmdaytrans]![txttdate])


--
KARL DEWEY
Build a little - Test a little


Derek said:
I am trying to make a query that will take 2 dates from a form and find all
the records between the 2 dates. I have used the suggested coding but am
getting 0 results. I have pasted the code below. Any help would be
appreciated.

Thanks.
Derek

Between [forms]![frmdaytrans]![txtfdate] And [forms]![frmdaytrans]![txttdate]
Click to expand...
Click to expand...
 
It is a date time field. I tried your suggestion and still receive no
results. I even created a new Query and just had the date field in it and
still received no results.
Click to expand...

If you open your table in datasheet view, what's in the date field? Does it
contain a time portion (e.g. looks like 2/25/2008 02:42:23pm)? If so you won't
see any results on the last day: you'll need to use a criterion like
= CVDate([forms]![frmdaytrans]![txtfdate]) And < DateAdd("d", 1, CVDate([forms]![frmdaytrans]![txttdate]))
Click to expand...

If that doesn't help please open your query in SQL view (open it in design
view and use View...SQL on the menu), and post the SQL text here. It may also
help to post a couple of records which you expect to see and don't, together
with the dates you're entering as criteria.
 
SELECT tblPaymentHistory.Date, tblOwners.LastName, tblOwners.FirstName,
tblPaymentHistory.OwnerID, tblPaymentHistory.AnimalsName,
tblPaymentHistory.ForWhatYear, tblPaymentHistory.[Tag#],
tblPaymentHistory.Amount
FROM (tblOwners INNER JOIN tblAnimals ON tblOwners.OwnerID =
tblAnimals.OwnerID) INNER JOIN tblPaymentHistory ON (tblOwners.OwnerID =
tblPaymentHistory.OwnerID) AND (tblAnimals.OwnerID =
tblPaymentHistory.OwnerID) AND (tblAnimals.AnimalName =
tblPaymentHistory.AnimalsName)
WHERE (((tblPaymentHistory.Date) Between [forms]![frmdaytrans]![txtfdate]
And [forms]![frmdaytrans]![txttdate]))
ORDER BY tblOwners.LastName;


John W. Vinson said:
It is a date time field. I tried your suggestion and still receive no
results. I even created a new Query and just had the date field in it and
still received no results.
Click to expand...

If you open your table in datasheet view, what's in the date field? Does it
contain a time portion (e.g. looks like 2/25/2008 02:42:23pm)? If so you won't
see any results on the last day: you'll need to use a criterion like
= CVDate([forms]![frmdaytrans]![txtfdate]) And < DateAdd("d", 1, CVDate([forms]![frmdaytrans]![txttdate]))
Click to expand...

If that doesn't help please open your query in SQL view (open it in design
view and use View...SQL on the menu), and post the SQL text here. It may also
help to post a couple of records which you expect to see and don't, together
with the dates you're entering as criteria.
Click to expand...
 
SELECT tblPaymentHistory.Date, tblOwners.LastName, tblOwners.FirstName,
tblPaymentHistory.OwnerID, tblPaymentHistory.AnimalsName,
tblPaymentHistory.ForWhatYear, tblPaymentHistory.[Tag#],
tblPaymentHistory.Amount
FROM (tblOwners INNER JOIN tblAnimals ON tblOwners.OwnerID =
tblAnimals.OwnerID) INNER JOIN tblPaymentHistory ON (tblOwners.OwnerID =
tblPaymentHistory.OwnerID) AND (tblAnimals.OwnerID =
tblPaymentHistory.OwnerID) AND (tblAnimals.AnimalName =
tblPaymentHistory.AnimalsName)
WHERE (((tblPaymentHistory.Date) Between [forms]![frmdaytrans]![txtfdate]
And [forms]![frmdaytrans]![txttdate]))
ORDER BY tblOwners.LastName;
Click to expand...

Thanks but....

It may^H^H^H will also help^H^H^H^H be necessary to post a couple of records
which you expect to see and don't, together with the dates you're entering as
criteria. I take it that frmDayTrans is in fact open?

One suggestion: change the fieldname Date to PaymentDate or some other name
that's not a reserved word. Access can and will get confused.
 
It could be that the dates parameters are not being correctly interpreted.
You might try

WHERE tblPaymentHistory.[Date] Between
CDate([forms]![frmdaytrans]![txtfdate])
And CDate([forms]![frmdaytrans]![txttdate])

Or Declaring the parameters at the beginning of the query

Parameters [forms]![frmdaytrans]![txtfdate] DateTime,
[forms]![frmdaytrans]![txttdate] DateTime;
SELECT tblPaymentHistory.[Date], ...

If that still does not return data, what happens if you drop the criteria?
Do you get records then?
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John W. Vinson said:
SELECT tblPaymentHistory.Date, tblOwners.LastName, tblOwners.FirstName,
tblPaymentHistory.OwnerID, tblPaymentHistory.AnimalsName,
tblPaymentHistory.ForWhatYear, tblPaymentHistory.[Tag#],
tblPaymentHistory.Amount
FROM (tblOwners INNER JOIN tblAnimals ON tblOwners.OwnerID =
tblAnimals.OwnerID) INNER JOIN tblPaymentHistory ON (tblOwners.OwnerID =
tblPaymentHistory.OwnerID) AND (tblAnimals.OwnerID =
tblPaymentHistory.OwnerID) AND (tblAnimals.AnimalName =
tblPaymentHistory.AnimalsName)
WHERE (((tblPaymentHistory.Date) Between [forms]![frmdaytrans]![txtfdate]
And [forms]![frmdaytrans]![txttdate]))
ORDER BY tblOwners.LastName;
Click to expand...

Thanks but....

It may^H^H^H will also help^H^H^H^H be necessary to post a couple of
records
which you expect to see and don't, together with the dates you're entering
as
criteria. I take it that frmDayTrans is in fact open?

One suggestion: change the fieldname Date to PaymentDate or some other
name
that's not a reserved word. Access can and will get confused.
Click to expand...
 
Back
Top