Between Date Issue

[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]));

 

[John Spencer]

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])

 

[Duane Hookom]

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.

 

[rollover99 via AccessMonster.com]

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

 

[rollover99 via AccessMonster.com]

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

 

[John Spencer]

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.