Between #date# And "date"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Access 2003 I am finding it impossible to obtain results when I enter
the "between dates" format. I am using Access to search QuickBooks tables
via QODBC. In an earlier version it worked. If I remove date parameters, it
works. If I use the "looks like" feature on the year only, it works. But I
need a range of years.
Any ideas?
 
Hi,


If you use a parameter, not a constant, and that the parameter is not
explicitly "typed", try CDate( ) around it:

.... dateTimeField = CDate( FORMS!formName!ControlName ) ...



or something similar.


Hoping it may help,
Vanderghast, Access MVP
 
NES said:
Using Access 2003 I am finding it impossible to obtain results when I enter
the "between dates" format. I am using Access to search QuickBooks tables
via QODBC. In an earlier version it worked. If I remove date parameters, it
works. If I use the "looks like" feature on the year only, it works. But I
need a range of years.
Any ideas?
Click to expand...

Show us the SQL that you're using.
 
Using Access 2003 I am finding it impossible to obtain results when I enter
the "between dates" format. I am using Access to search QuickBooks tables
via QODBC. In an earlier version it worked. If I remove date parameters, it
works. If I use the "looks like" feature on the year only, it works. But I
need a range of years.
Any ideas?
Click to expand...

Please post the SQL of a query that doesn't work, and indicate in what
way it doesn't work. Is the QODBC field actually a Date/Time value, or
a text string that looks like a date? Nothing has changed in 2003 with
regard to Between queries or date handling!

John W. Vinson[MVP]
 
Sorry for the delay on this. Here's the code. This returns nothing though
there are many records for 2002.

SELECT SalesReceiptLine.BillAddressAddr1, SalesReceiptLine.BillAddressAddr2,
SalesReceiptLine.BillAddressAddr3, SalesReceiptLine.BillAddressCity,
SalesReceiptLine.BillAddressState, SalesReceiptLine.BillAddressPostalCode,
Sum(SalesReceiptLine.TotalAmount) AS SumOfTotalAmount,
SalesReceiptLine.TxnDate
FROM SalesReceiptLine
WHERE (((SalesReceiptLine.TxnDate) Between #1/1/2001# And #12/31/2003#))
GROUP BY SalesReceiptLine.BillAddressAddr1,
SalesReceiptLine.BillAddressAddr2, SalesReceiptLine.BillAddressAddr3,
SalesReceiptLine.BillAddressCity, SalesReceiptLine.BillAddressState,
SalesReceiptLine.BillAddressPostalCode, SalesReceiptLine.TxnDate;
 
John, I posted the SQL command to the previous message. I am formatting the
date to the "short date" in the properties box. I'm assuming and hoping that
will take care of any date problems.
 
Hi



Does


SELECT *
FROM SalesReceiptLine
WHERE SalesReceiptLine.TxnDate Between #1/1/2001# And #12/31/2003#


return any? if not, is it possible TxnDate is literal rather than dateTime
(you can check it in table design) with a non-US format?

if SalesReceiptLine is a query rather than a table, does it returns any
record?


Vanderghast, Access MVP
 
Hi Michel
The table design shows this field to be "date/time". I haven't tried using
the DatePart function here. I'd have to concatenate the parts, and I doubt
that Access will accept that. The formatting seems to be correct. If I remove
the date parameters, I can see the entire query. It's madening. <s> Since I
am able to see the table correctly, I'm guessing that the problem does NOT
lie with QODBC.
 
I'm not sure how to implement your SQL query, however the following is a
simpler query THAT WORKS! This implies that the date is a date, I think. I
can use this to isolate records I need -- but this query will be extended (or
incorporated into another query) where the "Between" format is required.

SELECT SalesReceiptLine.TotalAmount, SalesReceiptLine.TxnDate,
SalesReceiptLine.CustomerRefFullName
FROM SalesReceiptLine
WHERE (((SalesReceiptLine.TxnDate)<#1/1/2003#));
 
Hi,


Maybe BETWEEN is not supported (by QODBC). Can you, instead of

a BETWEEN b AND c

try to use

a >= b AND a <= c



Hoping it may help,
Vanderghast, Access MVP



NES said:
I'm not sure how to implement your SQL query, however the following is a
simpler query THAT WORKS! This implies that the date is a date, I think. I
can use this to isolate records I need -- but this query will be extended
(or
incorporated into another query) where the "Between" format is required.

SELECT SalesReceiptLine.TotalAmount, SalesReceiptLine.TxnDate,
SalesReceiptLine.CustomerRefFullName
FROM SalesReceiptLine
WHERE (((SalesReceiptLine.TxnDate)<#1/1/2003#));
Click to expand...
 
John, I posted the SQL command to the previous message. I am formatting the
date to the "short date" in the properties box. I'm assuming and hoping that
will take care of any date problems.
Click to expand...

The formatting is COMPLETELY IRRELEVANT, unfortunately! It only
controls how the date is displayed, not how it's stored.

As an alternative to BETWEEN you can use the syntax

[Datefield] >= [Enter start date:] AND [Datefield] <= [Enter end
date:]


John W. Vinson[MVP]
 
John,
Thanks very much for this hint. using the =< and => did the trick. I find it
odd that this works and that the "between-And" function did not. But I'm
happy! Thanks again to all of you.
--
Norm Shimmel
Butler, PA


John Vinson said:
John, I posted the SQL command to the previous message. I am formatting the
date to the "short date" in the properties box. I'm assuming and hoping that
will take care of any date problems.
Click to expand...

The formatting is COMPLETELY IRRELEVANT, unfortunately! It only
controls how the date is displayed, not how it's stored.

As an alternative to BETWEEN you can use the syntax

[Datefield] >= [Enter start date:] AND [Datefield] <= [Enter end
date:]


John W. Vinson[MVP]
Click to expand...
 
Back
Top