Problem with Dates in Access/Visual Foxpro query

[mark.godecke]

I'm using Access 2000. There are two Visual Foxpro tables I have
created links to with ODBC, called 'client' and 'meals'. I'm trying to
use a query on them, but it gives unusual results. Here is the query:

SELECT Client.PersonID, Client.LastName, Client.FirstName,
Meals.TempStop, Meals.Restart, Meals.MealDate,
UserEnteredDate.SearchDate
FROM Client, Meals, UserEnteredDate
WHERE LastName = 'SMITH' AND MealDate = SearchDate

'UserEnteredDate' is an Access table, with 'SearchDate' formated as a
Short Date. When I run the query, I get results like these (I'm
leaving out the FirstName field so I can fit it all on one line):

PersonID LastName TempStop Restart MealDate SearchDate
2350 SMITH 12:00:00AM 12:00:00AM 5/24/2005 7/12/2006
2350 SMITH 5/9/2005 12/14/2005 5/24/2005 7/12/2006

There are two major problems with this. One, it does not seem to be
bothering to match the MealDate with the SearchDate. Two, Access is
reading Foxpro's null values as 12:00:00AM. What I would like to be
able to do is exclude results that have 12:00:00AM entered for
TempStop, as eventually I will be putting in a BETWEEN statement which
will include this field. When I tried adding a WHERE statement for
this, it also had no effect. I tried excluding it as both '12:00:00AM'
and just excluding null values. Does anybody have any suggestions?
I'm pretty new to all this SQL stuff, so I'd really appreciate not only
code suggestions to try, but an explanation of why I'm getting the
results I'm getting.

 

[KARL DEWEY]

Try this --
SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop] Is Null, Null, [Meals].[TempStop]) AS Temp_Stop,
IIF([Meals].[Restart] Is Null, Null, [Meals].[Restart]) AS Re-Start,
Meals.MealDate,
UserEnteredDate.SearchDate
FROM Client, Meals, UserEnteredDate
WHERE LastName = 'SMITH' AND MealDate = SearchDate;

 

[mark.godecke]

When I try

SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop] Is Null, Null, [Meals].[TempStop]) AS Temp_Stop,
IIF([Meals].[Restart] Is Null, Null, [Meals].[Restart]) AS Re-Start,
Meals.MealDate,
UserEnteredDate.SearchDate
FROM Client, Meals, UserEnteredDate
WHERE LastName = 'SMITH' AND MealDate = SearchDate;

I get the error "The SELECT statement includes a reserved word or an
argument name that is misspelled or missing, or the punctuation is
incorrect.

 

[John Spencer]

Try changing As Re-Start to as Re_Start or to As [Re-Start]. The query
engine is probably trying to subtract Start from Re or something else to do
with that name. That will probably take care of the error. I think you
will still get values in Temp_Stop and Re_Start.

You might have to change the IIF to
IIF(TempStop=#12:00:00 AM#,Null, TempStop) as Temp_Stop
or perhaps
IIF(CInt(TempStop)=0,Null,TempStop) as Temp_Stop

 

[mark.godecke]

Ok, I have made significant progress thanks to your help. Here is the
query in current form.

SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop]=#12:00:00 AM#,Null, TempStop) AS Temp_Stop,
IIF([Meals].[Restart]=#12:00:00 AM#,Null, Restart) AS [Re-Start],
Meals.MealDate,
UserEnteredDate.SearchDate
FROM Client, Meals, UserEnteredDate
WHERE LastName = 'SMITH' AND MealDate = SearchDate;

These are the results I'm getting, leaving out the "FirstName" column
to save space.

PersonID LastName Temp_Stop Re-Start MealDate SearchDate
2350 Smith (NULL) (NULL) 5/24/2005 7/12/2006

The 12:00:00AM problem is solved, but it still is not only showing
records where 'SearchDate' and 'MealDate' match, though it does match
the lastname to 'Smith'. I really need to be able to plug in dates and
get results that match only those dates.

 

[John Spencer]

Try a little troubleshooting.

SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop]=#12:00:00 AM#,Null, TempStop) AS Temp_Stop,
IIF([Meals].[Restart]=#12:00:00 AM#,Null, Restart) AS [Re-Start],
Meals.MealDate,
FROM Client, Meals
WHERE LastName = 'SMITH' AND MealDate = #5/24/2005#

Does that return the expected records?

Also, I note that you don't have any joins in this query. Well, you have a
cartesian join (all records of each table joined to all records of the other
tables). That can be a mess. But I can't puzzle out why that should return
the results you show.

 

[mark.godecke]

Here is my troubleshooting query, with an inner join added

SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop]=#12:00:00 AM#,Null, TempStop) AS Temp_Stop,
IIF([Meals].[Restart]=#12:00:00 AM#,Null, Restart) AS [Re-Start],
Meals.MealDate, UserEnteredDate.SearchDate
FROM (Client INNER JOIN Meals ON Client.PersonID = Meals.PersonID),
UserEnteredDate
WHERE MealDate = #7/11/2006#

Does the join look alright? It appears to have eliminated a lot of
results. Before I had 5,000+ records returned, the majority of which
seemed to be duplicates. These duplicates are now gone, so that's
progress. However, the WHERE statement still does nothing. Here is a
sample of my results from the above query

PersonID LastName Temp_Stop Re-Start MealDate SearchDate
39 Smith Robert 5/9/2005 12/14/2005 5/24/2005 7/11/2006
46 Jones James (NULL) (NULL) 5/24/2005 7/11/2006

I've also noticed that the date 5/24/2005 shows up extremely
frequently, making up the majority of results, with the occassional
other date (like 6/1/2005) appearing. I have no idea why this would be
the case, because when I look at the Foxpro table in a dbf file viewer,
I see between 300 and 500 records for every date as far back as
3/15/2005. Perhaps I have setup the ODBC link incorrectly? In any
case, can you think of any reason why it would not be matching the
MealDate to what is in the query?

 

[mark.godecke]

To answer one of my own question, Yes I did the ODBC link incorrectly.
When I first linked the table, Access asked me to select up to 10
fields that would uniquely identify each record. Apparently the fields
I selected were not sufficient to do this. Now that I relinked the
table, the query seems to be working.

 

[John Spencer]

Mark,

Try running the query without any reference to UserEnteredDate

 

[John Spencer]

TRY

SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop]=#12:00:00 AM#,Null, TempStop) AS Temp_Stop,
IIF([Meals].[Restart]=#12:00:00 AM#,Null, Restart) AS [Re-Start],
Meals.MealDate
FROM (Client INNER JOIN Meals ON Client.PersonID = Meals.PersonID)
WHERE MealDate = #7/11/2006#

If that works and gives you the desired results, try this next.
SELECT Client.PersonID, Client.LastName, Client.FirstName,
IIF([Meals].[TempStop]=#12:00:00 AM#,Null, TempStop) AS Temp_Stop,
IIF([Meals].[Restart]=#12:00:00 AM#,Null, Restart) AS [Re-Start],
Meals.MealDate
FROM (Client INNER JOIN Meals ON Client.PersonID = Meals.PersonID)
WHERE MealDate = CDate([Enter Meal Date])

If that gives you the desired results, then you know the problem has to do
with
UserEnteredDate.SearchDate

Is it possible that you have MULTIPLE Records in this table? Each record in
the table with a different date could bring back multiple records. Kind of
grasping at straws here.