Return empty field on linked FoxPro databas

[Shanin]

I am trying to eliminate a double entry that we are doing, one in an access
database for schedules, and one in a FoxPro that we use to calculate our
overtime. I have linked to the FoxPro and have the query built, but it won't
include any schedule where no one is assigned. I've tried the Is Null by
itself to see if I could get just those to pull up and they won't even pull
up. If I look at the table, they are there and they are blank. The separate
table I have running strictly from Access with the schedules will pull up the
blank ones from the Access table. Any ideas?

 

[KARL DEWEY]

Unmatched query.

 

[Cindy Winegarden]

Hi Shanin,

It really helps to show the query you're using, and more about the data. If
you're looking for rows in a parent table that have no matching child rows
then Karl's approach is right, but if you're looking for rows in a table
where some column value has not been specified then you need to know that
FoxPro uses a concept of "empty" in addition to Null, and they are not the
same. Therefore, in this case checking for nulls would not work.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

 

[Shanin]

I think I figured it out. I changed the Inner Join to a Left Join and it
seems to pull up the blanks now. And before anyone scolds me at having
"name" as a field....I didn't do that, that's the field name that came in our
FoxPro database so I was stuck with it..lol. Here is my SQL:


SELECT tkempsch.day_of_wk, tblDayofWeekList.DayofWeek, ([name]) & ", " &
[first_name] AS Employee, tkempsch.date, tkempsch.job, tkempsch.time_in,
tkempsch.time_out, tkempsch.hours
FROM (tkempsch LEFT JOIN py_emplo ON tkempsch.employee = py_emplo.employee)
INNER JOIN tblDayofWeekList ON tkempsch.day_of_wk = tblDayofWeekList.DayNumber
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#))
ORDER BY tkempsch.date, tkempsch.time_in
WITH OWNERACCESS OPTION;

 

[Cindy Winegarden]

Hi Shanin,

I'm glad you've solved your problem. You are correct that the way to find
non-matching rows is to use a Left Join and then look for rows where the
values from the child table are null.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx

I think I figured it out. I changed the Inner Join to a Left Join and it
seems to pull up the blanks now. And before anyone scolds me at having
"name" as a field....I didn't do that, that's the field name that came in
our
FoxPro database so I was stuck with it..lol. Here is my SQL:


SELECT tkempsch.day_of_wk, tblDayofWeekList.DayofWeek, ([name]) & ", " &
[first_name] AS Employee, tkempsch.date, tkempsch.job, tkempsch.time_in,
tkempsch.time_out, tkempsch.hours
FROM (tkempsch LEFT JOIN py_emplo ON tkempsch.employee =
py_emplo.employee)
INNER JOIN tblDayofWeekList ON tkempsch.day_of_wk =
tblDayofWeekList.DayNumber
WHERE (((tkempsch.date) Between #4/29/2007# And #5/5/2007#))
ORDER BY tkempsch.date, tkempsch.time_in
WITH OWNERACCESS OPTION;

Hi Shanin,

It really helps to show the query you're using, and more about the data.
If
you're looking for rows in a parent table that have no matching child
rows
then Karl's approach is right, but if you're looking for rows in a table
where some column value has not been specified then you need to know that
FoxPro uses a concept of "empty" in addition to Null, and they are not
the
same. Therefore, in this case checking for nulls would not work.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx