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

Jumat, 22 Agustus 2014

Re: [MS_AccessPros] Need help with query syntax

Hi Doyce,

you are missing a space before WHERE here:

[Vendor Master].VENID_08WHERE

(could be the way email rendered too ~)

since the SQL is not checking for Null, I would recommend wrapping field references with NZ

v.TNXDTE_55 + v.TNXTIM_55FROM
-->
nz(v.TNXDTE_55,0) + nz(v.TNXTIM_55FROM,0)

TNXDTE_55 + TNXTIM_55FROM
-->
nz(TNXDTE_55,0) + nz(TNXTIM_55FROM,0)

since you used + and not &, I am assuming these values are numbers. If they are values, do this:
NZ((TNXDTE_55 + TNXTIM_55FROM),"")

what is v. ? the alias from your example uses v2

Warm Regards,
Crystal

Remote Programming & Training -- connect to me, let's build it together

Do you want to know how to make a graph with Microsoft Access ?
http://www.youtube.com/watch?v=YhgNX6BWWmk



				How To Make a Graph with Microsoft Access (cc)

				View on www.youtube.com	Preview by Yahoo

Through sharing, we will all get better

~ have an awesome day ~

On Tuesday, August 19, 2014 8:46 AM, "winberry.doyce@roadsysinc.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John. I've received no error messages but the addition of the time component has made the query run so slow it is useless. I think I found the solution however. The primary key for the PO Receipts table is an autonumber field called RECNUM_55. I substituted it for the date/time component and the query runs very fast and gives the results I'm looking for. Thanks for you help!!! I have learned so much from you and others here.

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

Ooops. Just reread it. This should work:

SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55

FROM ([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_55)=

(SELECT Max(TNXDTE_55 + TNXTIM_55)

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 4:05 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Doyce-

Sorry, I didn't notice a space missing between your last field name and the FROM keyword. Try this:

SELECT DISTINCT p.PRTNUM_01, v.VENID_55, [Vendor Master].COMNAM_08 AS Comnam, v.TNXDTE_55, v.TNXTIM_55
FROM ([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 3:27 PM, winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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