Query problem, Please help!

  • Thread starter Thread starter Min
  • Start date Start date
M

Min

Hi, Query 1 is working, but Query2 returns all record, Query3 does not work.
What's wrong? What I want to do is select invoices by month. How can I do?
Thanks! (Invoice_Detail is a multi-table query, I am working on Access
2000).


Query 1:
SELECT InvoiceID, First(CompNameEn) AS CompEn, First(InvoiceDate) AS InvDate
FROM Invoice_Detail GROUP BY InvoiceID;

SELECT InvoiceID, First(CompNameEn) AS CompEn, First(InvoiceDate) AS InvDate
FROM Invoice_Detail Where InvDate = #2004-2-27# GROUP BY InvoiceID;

Query3:
SELECT InvoiceID, First(CompName) AS CompEn, First(InvoiceDate) AS InvDate
FROM Invoice_Detail Where Month(InvDate) = Month(#2004-2-27#) GROUP BY
InvoiceID;
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the USA date format when using the # delimiter. E.g.:

Feb 27, 2004 = #2/27/2004#

If you're using MSDE or SQL Server use the single-quote delimiter and
the ISO date format:

Feb 27, 2004 = '2004-02-27'

The 3rd query has "CompName" where the other queries have "CompNameEn."

Are you sure you want to use the First() function instead of the MIN()
function. In general, I've found the First() & Last() functions
misleading and, therefore, useless. Also, they are not standard SQL
functions.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYMpxoechKqOuFEgEQK6RACgi+12Z1eNeWjxKoydA9J8jmn5WuMAoOWZ
ECEcvtvtet2JQ3h+UtLpIWMH
=kJMh
-----END PGP SIGNATURE-----
 
Thanks for reply!

I tried #2/27/2004#, '2/27/2004',... but all have the same result. I am
working on Access and VBA in Win2000 professional environment.

Actually, I found if take away the As InvDate and use
InvoiceDate=#2004-2-27#, the query will work (See below). However, there is
a problem to use the query in VBA code. I cannot use
rsSet.Fields("InvoiceDate") or rsSet.Fields!InvoiceDate to retrieve the
value, I have to use rsSet.Fields("Expr1002"), where "Expr1002" is found by
running the query directly on Access query window.

Use Min() has the same result as First().

Min

SELECT InvoiceID, First(CompName) AS Comp, First(InvoiceDate) FROM
Invoice_Detail Where Month(InvoiceDate) = Month(#2004-2-27#) GROUP BY
InvoiceID;
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The reason Expr1002 appeared as the field name is that the query
generates a name at random when the column expression in the SELECT
clause doesn't have an Alias. When you don't know the field's name you
can use the field's ordinal number.

Column Ordinal number
=================== ==============
InvoiceID 0
Comp 1
First(InvoiceDate) 2

So, you'd refer to each Field like this:

rsSet(0) refers to InvoiceID
rsSet(1) refers to Comp
rsSet(2) refers to First(InvoiceDate)

First() and Min() are different: First retrieves the first record in
the recordset; Min retrieves the smallest value in the recordset.

Last() and Max() are also different: Last retrieves the last record in
the recordset; Max retrieves the largets value in the recordset.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYPJpIechKqOuFEgEQKebQCglKF5JHNQXL/VMSLRG1DfikEn2pYAoM9D
WnpQhJ1CCAwhw6Lov+vwKST0
=ru9G
-----END PGP SIGNATURE-----
 
You cannot use the field alias but must redo the expression in the where clause
or in the having clause, depending on what your are attempting to do. Si

Either of the following should work, however, I am not sure that you will get
the data you are looking for in a reliable manner. First and Last both return a
more or less random record within the group. If that is what you want, no problem.


SELECT InvoiceID, First(CompNameEn) AS CompEn, First(InvoiceDate) AS InvDate
FROM Invoice_Detail
GROUP BY InvoiceID
HAVING First(InvoiceDate) = #2004-2-27#

or

SELECT InvoiceID, First(CompNameEn) AS CompEn, First(InvoiceDate) AS InvDate
FROM Invoice_Detail
Where InvoiceDate = #2004-2-27#
GROUP BY InvoiceID;
 
Back
Top