Between Date Issue

  • Thread starter Thread starter rollover99 via AccessMonster.com
  • Start date Start date
R

rollover99 via AccessMonster.com

I am having an issue. I many databases that are linked ODBC. I have created
a query that I am trying to get the between date function to work. At first
I got an 'ODBC - Call Failed' Error 3146 so I tried formatting the date to a
short date via Format([Dates],"Short Date") this works in getting me one
step forward. In a new query I write the statement Between [start] and [end]
to limit the output of records, but get all records. My question is how do I
get the records to only show between the start date and the end date. I have
looked at many threads on this to no aveil. I have attached the SQL for both
queries. Any input would be appreciated.



SELECT Format([Invoices all]![Date],"Short Date") AS Dates, [Invoices all].
[Invoice#], [Invoices all].[Rot#], [Invoices all].[Sold by], [Invoices all].
[Entered by], [Invoices all].Prgm, [Invoices all].[Bill To], company.
COMPANYNAME AS Company, [Invoices all].PN, [Invoices all].DESCRIPTION AS
[Desc], [Invoices all].Type, [Invoices all].Qty, [Invoices all]!Qty*[Invoices
all]!Cost AS Amount, [Invoices all].Pool, [Invoices all].CLF
FROM [Invoices all] INNER JOIN company ON [Invoices all].[Bill To] = company.
ID;


SELECT Query1.*
FROM Query1
WHERE (((Query1.Dates) Between [start] And [end]));
 
Guessing that Start and End are parameter prompts. I would ensure that
Access knows that they are supposed to be dates by forcing the type with
CDate or by declaring the parameters at the beginning of the query

Parameters [Start] DateTime, [End] DateTime;
SELECT Format([Invoices all]![Date],"Short Date") AS Dates
, [Invoices all].[Invoice#]
, [Invoices all].[Rot#]
, [Invoices all].[Sold by]
, [Invoices all].[Entered by]
, [Invoices all].Prgm
, [Invoices all].[Bill To]
, company.COMPANYNAME AS Company
, [Invoices all].PN,
[Invoices all].DESCRIPTION AS [Desc]
, [Invoices all].Type
, [Invoices all].Qty
, [Invoices all]!Qty*[Invoices all]!Cost AS Amount
, [Invoices all].Pool
, [Invoices all].CLF
FROM [Invoices all] INNER JOIN company
ON [Invoices all].[Bill To] = company.ID
WHERE [Invoices all]![Date] Between CDate([Start]) and CDate([End])
 
I would not format a date field in the query. Your formatted result could be
resolved as a string. This would place 12/22/2006 between 1/1/2006 and
2/1/2006. Just send the real date value through your queries and format it
in your form or report.

Also, consider using text boxes on forms for your date criteria entry. I
don't consider parameter prompts acceptable in an application for other
users.
 
John

Still showing all records. Its like the between statement is not even there.
Checked the format before, not that it makes a difference after I formatted
to short date
but even that in the table is a date/time.

Clueless on this end
 
John

I stand corrected. On further investigation the results are showing the
dates between the specified but it
is showing all years (ex.)

06/01/2001
06/01/2002
06/01/2003

I am entereing 06/01/2006
 
Does your where clause look like the one below?

WHERE [Invoices all].[Date] Between CDate([Start]) and CDate([End])

Does 06/01/2006 mean June 6th of 2006 or does it mean January 6, 2006? As
an experiment try entering the Start and End dates in the following format
yyyy-mm-dd and see what you get returned.

As an aside, when you use the format function on a date you turn the date
into a string. And if you search against that string you will get very
different results then if you search against a date.

As a string "06/01/2004" is between "06/01/2001" and "06/30/2001", as a date
06/01/2004 is not between the two dates.
 
Back
Top