A97 Query with BLANK Data

I have a 2 table query in Access 97, with a Vendor "short"
name in the main table pulling a Vendor "long" name from
a linked table to print a report (Sql is below)

This all works fine, EXCEPT when I have not updated the
Vendor table, which leads to the Receive table not having a
record to link to, and the daily receiving report is missing
the information for that receipt due to the non-linkage

How do I modify the query to go ahead and return what data
IS available in the Receive table, and just print a blank
where the Vendor "long" name should be?

Receive table is input from a barcode scanner on a loading
dock, with a limit of 8 characters for Vendor "short" name,
which relates to Vendor table with actual Vendor name to
print on the daily receiving report


SELECT Receive.DateStamp, Receive.Operator, Receive.BarCode,
Receive.PONum, Receive.Vendor, Receive.Deliver, Vendor.VendorName
FROM Receive INNER JOIN Vendor ON Receive.Vendor = Vendor.Vendor
WHERE (((Receive.DateStamp)=[forms]![Print2]![TodayDate]))
ORDER BY Receive.DateStamp DESC , Receive.Operator;


John Thomas Smith
http://www.direct2usales.com
http://www.pacifier.com/~jtsmith

re: A97 Query with BLANK Data

John Thomas Smith wrote:[color=blue]
> I have a 2 table query in Access 97, with a Vendor "short"
> name in the main table pulling a Vendor "long" name from
> a linked table to print a report (Sql is below)
>
> This all works fine, EXCEPT when I have not updated the
> Vendor table, which leads to the Receive table not having a
> record to link to, and the daily receiving report is missing
> the information for that receipt due to the non-linkage
>
> How do I modify the query to go ahead and return what data
> IS available in the Receive table, and just print a blank
> where the Vendor "long" name should be?
>
> Receive table is input from a barcode scanner on a loading
> dock, with a limit of 8 characters for Vendor "short" name,
> which relates to Vendor table with actual Vendor name to
> print on the daily receiving report
>
>
> SELECT Receive.DateStamp, Receive.Operator, Receive.BarCode,
> Receive.PONum, Receive.Vendor, Receive.Deliver, Vendor.VendorName
> FROM Receive INNER JOIN Vendor ON Receive.Vendor = Vendor.Vendor
> WHERE (((Receive.DateStamp)=[forms]![Print2]![TodayDate]))
> ORDER BY Receive.DateStamp DESC , Receive.Operator;[/color]

Replace INNER with LEFT

--
regards,

Br@dley

re: A97 Query with BLANK Data

In the design of the query, if you calculate the field like
vendor_short:nz(Vendor_name, ""), you will never get no reponse, you'll
get an empty response.

The best solution would be to fix your inputing routine to not allow
entering a new Vendor without also adding the short code.

Good luck.
Barry.


Br@dley wrote:[color=blue]
> John Thomas Smith wrote:[color=green]
> > I have a 2 table query in Access 97, with a Vendor "short"
> > name in the main table pulling a Vendor "long" name from
> > a linked table to print a report (Sql is below)
> >
> > This all works fine, EXCEPT when I have not updated the
> > Vendor table, which leads to the Receive table not having a
> > record to link to, and the daily receiving report is missing
> > the information for that receipt due to the non-linkage
> >
> > How do I modify the query to go ahead and return what data
> > IS available in the Receive table, and just print a blank
> > where the Vendor "long" name should be?
> >
> > Receive table is input from a barcode scanner on a loading
> > dock, with a limit of 8 characters for Vendor "short" name,
> > which relates to Vendor table with actual Vendor name to
> > print on the daily receiving report
> >
> >
> > SELECT Receive.DateStamp, Receive.Operator, Receive.BarCode,
> > Receive.PONum, Receive.Vendor, Receive.Deliver, Vendor.VendorName
> > FROM Receive INNER JOIN Vendor ON Receive.Vendor = Vendor.Vendor
> > WHERE (((Receive.DateStamp)=[forms]![Print2]![TodayDate]))
> > ORDER BY Receive.DateStamp DESC , Receive.Operator;[/color]
>
> Replace INNER with LEFT
>
> --
> regards,
>
> Br@dley[/color]