Problem with Visual Foxpro dbf query

[mark.godecke]

I have two Visual Foxpro tables linked into my Access 2000 database
with odbc. The database is for a Meals on Wheels non-profit. One of
the foxpro tables is called 'clients' and the other 'meals'. Client
information like name and address are in the 'clients' table, which is
linked to the 'meals' table with the field 'personid'. The meals table
has entries for each day of the month for every client with information
on what meal they got that day, or will get that day.

Occasionally, a client will want their meals stopped. There are two
fields in the 'meals' table for this. One is the 'tempstop' field,
which is the date they want meal delivery to stop. The second field is
'restart' which is the date they want meal delivery to continue. I
need to query this table, so that I can tell if the client is
temporarily stopped on a specific date. I want to display the results
of this query as a checkbox on a form.

Here is what I've tried, courtesy of the Access Query Design View.

SELECT client.personid, client.lastname, client.firstname,
meals.tempstop, meals.restart,
IIf([Meals]![restart]>=[UserEnteredDate]![UserEnteredDate],True,False)
AS OnTempStop
FROM UserEnteredDate, client
INNER JOIN meals
ON client.personid = meals.personid
WHERE ((([Meals]![tempstop])<=[UserEnteredDate]));


The IIf part of the query does not seem to work, as everybody shows up
false. Also, Access insists on showing dates that are blank in Visual
Foxpro as 12:00:00AM, which may be throwing me off too. This is
because a 'restart' date is not always entered when a client's meals
are stopped. Example: Patty's tempstop date is 07/10/2006, her restart
date is shown as 12:00:00AM in Access. Finally, it gives me all the
records in the meals table for the specific 'personid', when I just
want one row per person.

I was able to find a knowledge base article about the 12:00:00AM thing
titled "PRB: 12/30/1899 Appears as a Blank Date in Microsoft Access"
Article ID:150433, but it didn't give me any idea of how to work around
the issue. If anyone has any ideas on how to rewrite this query so
that it does what I want, I would appreciate it.

 

[Cindy Winegarden]

Hi Mark,

Hopefully I understand: every client has an entry for every day with either
a meal listed or TempStop and Restart are not null. The days when TempStop
and Restart are not null are when the date is less than TempStop or greater
than Restart. That means you can have several rows with the same TempStop
and Restart values. To eliminate the duplicates you must either Select
Distinct or Group by the fields in your Select list.

I'm not sure whether you want to show everybody, with a check mark if
they're stopped, or only people who are stopped.

In FoxPro an empty date is always less than any specific date.

Try this code:
'-- Show everybody
Select Client.PersonID, Client.LastName, Client.FirstName, Meals.TempStop,
Meals.Restart, IIF(Meals.TempStop <= UserEnteredDate And Meals.Restart >=
UserEnteredDate, True, False) As IsStopped From Client Inner Join Meals On
Client.PersonID = Meals.PersonID Group By Client.PersonID, Client.LastName,
Client.FirstName, Meals.TempStop, Meals.Restart, IIF(Meals.TempStop <=
UserEnteredDate And Meals.Restart >= UserEnteredDate, True, False)

'-- Show only people who are stopped
.... Where Meals.TempStop <= UserEnteredDate And Meals.Restart >=
UserEnteredDate ....

I'll leave the 12/30/1899 issue to someone more familiar with Access.


--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
(e-mail address removed)

......
IIf([Meals]![restart]>=[UserEnteredDate]![UserEnteredDate],True,False)
......
WHERE ((([Meals]![tempstop])<=[UserEnteredDate]));