ACCESS FEED: Re: [MS_AccessPros] Need help with query syntax

Selasa, 19 Agustus 2014

Re: [MS_AccessPros] Need help with query syntax

John,

Something is missing. I'm getthing this error:

Syntax error (missing operator) in query expression v.TNXTIM_55FROM ([PART MASTER] AS p INNER JOIN [PO Receipts] AS v ON p.PRTNUM_01 = v.PRTNUM_55) INNER JOIN [Vendor Master] ON v.VENID_55 = [Vendor Master].VENID_08WHERE (((v.TNXDTE_55 + v.TNXTIM_55FROM)=(SELECT Max(TNXDTE_55 + TNXTIM_55FROM)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;

Doyce

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Doyce-

You can add dates and times, like this:

SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55FROM ([PART MASTER] AS p INNER JOIN [PO Receipts] AS v ON p.PRTNUM_01 = v.PRTNUM_55) INNER JOIN [Vendor Master] ON v.VENID_55 = [Vendor Master].VENID_08
WHERE (((v.TNXDTE_55 + v.TNXTIM_55FROM)=(SELECT Max(TNXDTE_55 + TNXTIM_55FROM)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 19, 2014, at 2:08 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello friends,
The query below is supposed to select the last time a part was received. However I have discovered if a part is received twice on the same day, it shows up twice and I only want the latest receipt. I have added the TXNTIM_55 field but don't know how to add the max value to the query.
SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55FROM ([PART MASTER] AS p INNER JOIN [PO Receipts] AS v ON p.PRTNUM_01 = v.PRTNUM_55) INNER JOIN [Vendor Master] ON v.VENID_55 = [Vendor Master].VENID_08
WHERE (((v.TNXDTE_55)=(SELECT Max(TNXDTE_55)
FROM [PO Receipts] As v2
WHERE v2.PRTNUM_55 = p.PRTNUM_01)))
ORDER BY p.PRTNUM_01;
Doyce