Date/Time Selection problem!!!!!

[MarieG]

Startig Point: I am pulling data using ODBC.. I cannot modify the Tables in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN [date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table that I
have made that CAN be modified and contain the dates that I am trying to
pull.

My Problem is that even though in MY table, the date format is the same as
the other Table, it does not pull data that are within these boundaries.. it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

 

[Ken Snell [MVP]]

It's possible that ACCESS is not seeing the data from the ODBC table as
dates. Try this:

BETWEEN "#" & [date from] & "#" AND "#" & [date to] & "#"

 

[MarieG]

Thanks, I tried your suggesion, but it returned this: Data type mismatch in
expression

It's possible that ACCESS is not seeing the data from the ODBC table as
dates. Try this:

BETWEEN "#" & [date from] & "#" AND "#" & [date to] & "#"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Startig Point: I am pulling data using ODBC.. I cannot modify the Tables
in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN [date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table that
I
have made that CAN be modified and contain the dates that I am trying to
pull.

My Problem is that even though in MY table, the date format is the same as
the other Table, it does not pull data that are within these boundaries..
it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

 

[Douglas J. Steele]

Actually, Ken had a typo there. It should have been

BETWEEN "#" & [date from] & "# AND #" & [date to] & "#"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, I tried your suggesion, but it returned this: Data type mismatch
in
expression

It's possible that ACCESS is not seeing the data from the ODBC table as
dates. Try this:

BETWEEN "#" & [date from] & "#" AND "#" & [date to] & "#"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Startig Point: I am pulling data using ODBC.. I cannot modify the
Tables
in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN
[date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table
that
I
have made that CAN be modified and contain the dates that I am trying
to
pull.

My Problem is that even though in MY table, the date format is the same
as
the other Table, it does not pull data that are within these
boundaries..
it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

 

[Ken Snell [MVP]]

Actually, Ken had a typo there. It should have been

BETWEEN "#" & [date from] & "# AND #" & [date to] & "#"

Thanks, Doug!

 

[Duane Hookom]

Since the from and to dates are from fields in a table, I don't think the #s
are necessary. It would help to see the SQL view of the query.

Can we assume the significant fields are date data types? When viewed in
datasheet, the dates should appear right-aligned.
--
Duane Hookom
Microsoft Access MVP

Actually, Ken had a typo there. It should have been

BETWEEN "#" & [date from] & "# AND #" & [date to] & "#"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, I tried your suggesion, but it returned this: Data type mismatch
in
expression

It's possible that ACCESS is not seeing the data from the ODBC table as
dates. Try this:

BETWEEN "#" & [date from] & "#" AND "#" & [date to] & "#"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Startig Point: I am pulling data using ODBC.. I cannot modify the
Tables
in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN
[date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table
that
I
have made that CAN be modified and contain the dates that I am trying
to
pull.

My Problem is that even though in MY table, the date format is the same
as
the other Table, it does not pull data that are within these
boundaries..
it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

 

[John Spencer]

I would try forcing the date type using CDate

BETWEEN CDate([date from]) AND CDate([date to])

Or as an alternative declaring the parameters at the start of the query

PARAMETERS [Date From] DateTime, [Date To] DateTime;
SELECT ...

If you are working in query design view then select Query : Parameters
from the menu.
In the first column enter the parameter name exactly as you want to use
it and select the datatype in the second column.

You still need the parameter prompt in the criteria section.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Startig Point: I am pulling data using ODBC.. I cannot modify the Tables in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN [date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table that I
have made that CAN be modified and contain the dates that I am trying to
pull.

My Problem is that even though in MY table, the date format is the same as
the other Table, it does not pull data that are within these boundaries.. it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

The Format of your local field is irrelevant: dates aren't stored as strings
but as numbers, a count of days and fractions of a day (times) since an
arbitrary start point. What is the datatype of the ODBC-linked date field -
Date/Time or Text? Could you open your query in SQL view and post the SQL text
here?

 

[MarieG]

Here it is in SQL view.. Thanks so much for your help guys! YOU ROCK!!

SELECT dbo_VisitTransactions.LastModifiedBy,
dbo_VisitTransactions.LastModified, dbo_PatientVisit.TicketNumber,
dbo_VisitTransactions.Payments, dbo_VisitTransactions.Adjustments
FROM [Date Range], dbo_VisitTransactions INNER JOIN dbo_PatientVisit ON
dbo_VisitTransactions.PatientVisitid = dbo_PatientVisit.PatientVisitId
WHERE (((dbo_VisitTransactions.LastModifiedBy) Like "tmc-danielle*") AND
((dbo_VisitTransactions.LastModified) Between CDate([from date]) And
CDate([to date]))) OR (((dbo_VisitTransactions.LastModifiedBy) Like
"tmc-ginger*"))
ORDER BY dbo_VisitTransactions.LastModifiedBy;

Startig Point: I am pulling data using ODBC.. I cannot modify the Tables in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN [date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table that I
have made that CAN be modified and contain the dates that I am trying to
pull.

My Problem is that even though in MY table, the date format is the same as
the other Table, it does not pull data that are within these boundaries.. it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

The Format of your local field is irrelevant: dates aren't stored as strings
but as numbers, a count of days and fractions of a day (times) since an
arbitrary start point. What is the datatype of the ODBC-linked date field -
Date/Time or Text? Could you open your query in SQL view and post the SQL text
here?

 

[John Spencer]

AHA! YOUR where clause appears to say to get records where
LastModifiedBy is like tmc-Daneille* AND is between a specified date range

AND in addition to that return ALL records where the lastModifiedby is
like tmc-Ginger with NO date range specified.

In query design view you would need to repeat the date criteria on the
second line of criteria where you specify tmc-ginger*

The SQL should look something like this. Access will reformat that into
a bit more complicated version of the where clause.

SELECT dbo_VisitTransactions.LastModifiedBy,
dbo_VisitTransactions.LastModified, dbo_PatientVisit.TicketNumber,
dbo_VisitTransactions.Payments, dbo_VisitTransactions.Adjustments
FROM [Date Range], dbo_VisitTransactions INNER JOIN dbo_PatientVisit ON
dbo_VisitTransactions.PatientVisitid = dbo_PatientVisit.PatientVisitId

WHERE (dbo_VisitTransactions.LastModifiedBy Like "tmc-danielle*" OR
AND dbo_VisitTransactions.LastModifiedBy Like "tmc-ginger*")

AND dbo_VisitTransactions.LastModified Between CDate([from date]) And
CDate([to date])
ORDER BY dbo_VisitTransactions.LastModifiedBy;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Here it is in SQL view.. Thanks so much for your help guys! YOU ROCK!!

SELECT dbo_VisitTransactions.LastModifiedBy,
dbo_VisitTransactions.LastModified, dbo_PatientVisit.TicketNumber,
dbo_VisitTransactions.Payments, dbo_VisitTransactions.Adjustments
FROM [Date Range], dbo_VisitTransactions INNER JOIN dbo_PatientVisit ON
dbo_VisitTransactions.PatientVisitid = dbo_PatientVisit.PatientVisitId
WHERE (((dbo_VisitTransactions.LastModifiedBy) Like "tmc-danielle*") AND
((dbo_VisitTransactions.LastModified) Between CDate([from date]) And
CDate([to date]))) OR (((dbo_VisitTransactions.LastModifiedBy) Like
"tmc-ginger*"))
ORDER BY dbo_VisitTransactions.LastModifiedBy;

Startig Point: I am pulling data using ODBC.. I cannot modify the Tables in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN [date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table that I
have made that CAN be modified and contain the dates that I am trying to
pull.

My Problem is that even though in MY table, the date format is the same as
the other Table, it does not pull data that are within these boundaries.. it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG

The Format of your local field is irrelevant: dates aren't stored as strings
but as numbers, a count of days and fractions of a day (times) since an
arbitrary start point. What is the datatype of the ODBC-linked date field -
Date/Time or Text? Could you open your query in SQL view and post the SQL text
here?

 

[MarieG]

OH MY GOODNESS!!! IT WORKED!!! Thank you so much John for your help!
Obviously, I am a novice. I will not be making that same mistake again!
Again, thank you so much, you are amazing!

AHA! YOUR where clause appears to say to get records where
LastModifiedBy is like tmc-Daneille* AND is between a specified date range

AND in addition to that return ALL records where the lastModifiedby is
like tmc-Ginger with NO date range specified.

In query design view you would need to repeat the date criteria on the
second line of criteria where you specify tmc-ginger*

The SQL should look something like this. Access will reformat that into
a bit more complicated version of the where clause.

SELECT dbo_VisitTransactions.LastModifiedBy,
dbo_VisitTransactions.LastModified, dbo_PatientVisit.TicketNumber,
dbo_VisitTransactions.Payments, dbo_VisitTransactions.Adjustments
FROM [Date Range], dbo_VisitTransactions INNER JOIN dbo_PatientVisit ON
dbo_VisitTransactions.PatientVisitid = dbo_PatientVisit.PatientVisitId

WHERE (dbo_VisitTransactions.LastModifiedBy Like "tmc-danielle*" OR
AND dbo_VisitTransactions.LastModifiedBy Like "tmc-ginger*")

AND dbo_VisitTransactions.LastModified Between CDate([from date]) And
CDate([to date])
ORDER BY dbo_VisitTransactions.LastModifiedBy;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Here it is in SQL view.. Thanks so much for your help guys! YOU ROCK!!

SELECT dbo_VisitTransactions.LastModifiedBy,
dbo_VisitTransactions.LastModified, dbo_PatientVisit.TicketNumber,
dbo_VisitTransactions.Payments, dbo_VisitTransactions.Adjustments
FROM [Date Range], dbo_VisitTransactions INNER JOIN dbo_PatientVisit ON
dbo_VisitTransactions.PatientVisitid = dbo_PatientVisit.PatientVisitId
WHERE (((dbo_VisitTransactions.LastModifiedBy) Like "tmc-danielle*") AND
((dbo_VisitTransactions.LastModified) Between CDate([from date]) And
CDate([to date]))) OR (((dbo_VisitTransactions.LastModifiedBy) Like
"tmc-ginger*"))
ORDER BY dbo_VisitTransactions.LastModifiedBy;

Startig Point: I am pulling data using ODBC.. I cannot modify the Tables in
any way.

The Date field is in this format: 1/15/2009 12:30:00 AM

I have in the Criteria section of my query the following: BETWEEN [date
from] AND [date to]

The [date from] and [date to] are fields that are coming from a table that I
have made that CAN be modified and contain the dates that I am trying to
pull.

My Problem is that even though in MY table, the date format is the same as
the other Table, it does not pull data that are within these boundaries.. it
pulls ALL dates, like there isn't criteria at all.

PLS HELP!!

MarieG
The Format of your local field is irrelevant: dates aren't stored as strings
but as numbers, a count of days and fractions of a day (times) since an
arbitrary start point. What is the datatype of the ODBC-linked date field -
Date/Time or Text? Could you open your query in SQL view and post the SQL text
here?