Help with logic on a query..

[graeme34 via AccessMonster.com]

Hi could anybody please adapt the following query to get it to do what I want
to do

The end result I want is to select the highest despatch number from tbl.
Despatch that has the Order number from a text box control. All the other
fields are just Items I need for my Invoice report.
Heres the query, currently it is selecting nothing, its running but
returning an empty recordset.
SELECT DISTINCT tblAccount.AccountName, tblAccount.[Account Address 1],
tblAccount.[Account Address 2], tblAccount.[Account Address 3], tblAccount.
[Account Town / City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber,
tblSalesOrder.CustomerOrderNumber, tblSalesOrder.DeliveryMode,
tblSalesInvoice.SalesInvoiceNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue,
tblDespatch.DespatchNumber
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine ON tblSalesOrder.SalesOrderNumber =
tblSalesOrderLine.SalesOrderNumber) ON tblVAT.VATRate = tblSalesOrderLine.
VATRate
WHERE (((tblDespatch.SalesOrderNumber)=[Forms]![frmChooseOrderNumber]!
[txtOrderNumberChoice]) AND ((tblDespatch.[Despatch Number])=(SELECT MAX
(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[Tom Ellison]

Dear Graeme:

You're a busy guy, eh?

I'm writing your query here for my reference:

SELECT DISTINCT A.AccountName, A.[Account Address 1],
A.[Account Address 2], A.[Account Address 3], A.[Account Town / City],
A.[Post Code], D.SalesOrderNumber, SO.CustomerOrderNumber,
tblSalesOrder.DeliveryMode,
SI.SalesInvoiceNumber, SI.InvoiceDate, SI.TotalNett, SOL.VATRate,
V.VATValue,
D.DespatchNumber
FROM tblVAT V
INNER JOIN (((tblAccount A
INNER JOIN tblSalesOrder SO
ON A.AccountIndex = SO.AccountIndex)
INNER JOIN (tblDespatch D
INNER JOINtblSalesInvoice SI
ON D.DespatchNumber = SI.[Despatch Number])
ON SO.SalesOrderNumber = D.SalesOrderNumber)
INNER JOIN tblSalesOrderLine SOL
ON SO.SalesOrderNumber = SOL.SalesOrderNumber)
ON V.VATRate = SOL.VATRate
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number]) =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)

When you write something a bit complex, start simple and get the simple
things working. For example:

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]

Is this working? Add a bit at a time and see when it breaks.

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number]) =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)

By the way, when you have two copies of a table (tblDespatch) in your query
and you do not reference them with distinct aliases, nasty things can
happen. It is just possible I fixed (or seriously broke) your query with
the alias in the subquery. Only you will be able to tell! Please make all
the references throughout your query explicit as to which table is used,
even if you think the Column Names are already unique. You'll be surprised
how seriously broken a query can become if you add a column to one of the
tables in the query, only to find later that it duplicates a column from
another table in that query, which you failed to explicitly reference
(table.column).

Tom Ellison

Hi could anybody please adapt the following query to get it to do what I
want
to do

The end result I want is to select the highest despatch number from tbl.
Despatch that has the Order number from a text box control. All the other
fields are just Items I need for my Invoice report.
Heres the query, currently it is selecting nothing, its running but
returning an empty recordset.
SELECT DISTINCT tblAccount.AccountName, tblAccount.[Account Address 1],
tblAccount.[Account Address 2], tblAccount.[Account Address 3],
tblAccount.
[Account Town / City], tblAccount.[Post Code],
tblDespatch.SalesOrderNumber,
tblSalesOrder.CustomerOrderNumber, tblSalesOrder.DeliveryMode,
tblSalesInvoice.SalesInvoiceNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue,
tblDespatch.DespatchNumber
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON
tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER
JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine ON tblSalesOrder.SalesOrderNumber =
tblSalesOrderLine.SalesOrderNumber) ON tblVAT.VATRate = tblSalesOrderLine.
VATRate
WHERE (((tblDespatch.SalesOrderNumber)=[Forms]![frmChooseOrderNumber]!
[txtOrderNumberChoice]) AND ((tblDespatch.[Despatch Number])=(SELECT MAX
(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[John Spencer]

SELECT DISTINCT tblAccount.AccountName, tblAccount.[Account Address 1],
tblAccount.[Account Address 2], tblAccount.[Account Address 3], tblAccount.
[Account Town / City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber,
tblSalesOrder.CustomerOrderNumber, tblSalesOrder.DeliveryMode,
tblSalesInvoice.SalesInvoiceNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue,
tblDespatch.DespatchNumber
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine
ON tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber)
ON tblVAT.VATRate = tblSalesOrderLine.VATRate
WHERE tblDespatch.[Despatch Number]=
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber = [Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

 

[graeme34 via AccessMonster.com]

Hi Tom
Thanks for your interest,
SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number] =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)
Is working fine, but after being prompted for the Forms!....OrderNumber...
which was expected..I am promted for the despatch number, which is not what I
want, although from looking at this less complex query I can see why because
of the where clause (I think).
What I require is after the query runs it takes the order number from the
input form and find the highest despatch number with that order number.
I have tried experimenting with the SELECT MAX but I acnt seem to get the
syntax right.
Thanks again Tom

Dear Graeme:

You're a busy guy, eh?

I'm writing your query here for my reference:

SELECT DISTINCT A.AccountName, A.[Account Address 1],
A.[Account Address 2], A.[Account Address 3], A.[Account Town / City],
A.[Post Code], D.SalesOrderNumber, SO.CustomerOrderNumber,
tblSalesOrder.DeliveryMode,
SI.SalesInvoiceNumber, SI.InvoiceDate, SI.TotalNett, SOL.VATRate,
V.VATValue,
D.DespatchNumber
FROM tblVAT V
INNER JOIN (((tblAccount A
INNER JOIN tblSalesOrder SO
ON A.AccountIndex = SO.AccountIndex)
INNER JOIN (tblDespatch D
INNER JOINtblSalesInvoice SI
ON D.DespatchNumber = SI.[Despatch Number])
ON SO.SalesOrderNumber = D.SalesOrderNumber)
INNER JOIN tblSalesOrderLine SOL
ON SO.SalesOrderNumber = SOL.SalesOrderNumber)
ON V.VATRate = SOL.VATRate
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number]) =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)

When you write something a bit complex, start simple and get the simple
things working. For example:

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]

Is this working? Add a bit at a time and see when it breaks.

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number]) =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)

By the way, when you have two copies of a table (tblDespatch) in your query
and you do not reference them with distinct aliases, nasty things can
happen. It is just possible I fixed (or seriously broke) your query with
the alias in the subquery. Only you will be able to tell! Please make all
the references throughout your query explicit as to which table is used,
even if you think the Column Names are already unique. You'll be surprised
how seriously broken a query can become if you add a column to one of the
tables in the query, only to find later that it duplicates a column from
another table in that query, which you failed to explicitly reference
(table.column).

Tom Ellison

Hi could anybody please adapt the following query to get it to do what I
want

[quoted text clipped - 27 lines]

(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[graeme34 via AccessMonster.com]

hi John
Thanks for your help, but I think my logic is wrong anyway, I am getting two
prompts, one for order number (expected as the form is closed) but then I'm
being prompted for the despatch number....but I want the query to find that
(the highest despatch number for the given order number)

SELECT DISTINCT tblAccount.AccountName, tblAccount.[Account Address 1],
tblAccount.[Account Address 2], tblAccount.[Account Address 3], tblAccount.
[Account Town / City], tblAccount.[Post Code], tblDespatch.SalesOrderNumber,
tblSalesOrder.CustomerOrderNumber, tblSalesOrder.DeliveryMode,
tblSalesInvoice.SalesInvoiceNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue,
tblDespatch.DespatchNumber
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine
ON tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber)
ON tblVAT.VATRate = tblSalesOrderLine.VATRate
WHERE tblDespatch.[Despatch Number]=
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber = [Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

Hi could anybody please adapt the following query to get it to do what I want
to do

[quoted text clipped - 22 lines]

(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[John Spencer]

Looks as if I may have made a "spelling" error in the where clause. I do
not use spaces in field and table names - it just leads to problems as far
as I am concerned. I left out a space in the revised WHERE clause. So if
you copied and pasted the query then that would be the problem.

When Access asks for a parameter that you don't believe you have in the
query, it is almost always a misspelling of a field name or table name.
Read the parameter prompt closely and you will probably see it asking for a
"misspelled" version of a field name.

Corrected (I hope) Where clause follows

SELECT ...
FROM ...
WHERE tblDespatch.[Despatch Number]=
(SELECT MAX(T.[Despatch Number])
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

hi John
Thanks for your help, but I think my logic is wrong anyway, I am getting
two
prompts, one for order number (expected as the form is closed) but then
I'm
being prompted for the despatch number....but I want the query to find
that
(the highest despatch number for the given order number)

SELECT DISTINCT tblAccount.AccountName, tblAccount.[Account Address 1],
tblAccount.[Account Address 2], tblAccount.[Account Address 3],
tblAccount.
[Account Town / City], tblAccount.[Post Code],
tblDespatch.SalesOrderNumber,
tblSalesOrder.CustomerOrderNumber, tblSalesOrder.DeliveryMode,
tblSalesInvoice.SalesInvoiceNumber, tblSalesInvoice.InvoiceDate,
tblSalesInvoice.TotalNett, tblSalesOrderLine.VATRate, tblVAT.VATValue,
tblDespatch.DespatchNumber
FROM tblVAT INNER JOIN (((tblAccount INNER JOIN tblSalesOrder ON
tblAccount.
AccountIndex = tblSalesOrder.AccountIndex) INNER JOIN (tblDespatch INNER
JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON tblSalesOrder.SalesOrderNumber = tblDespatch.SalesOrderNumber)
INNER JOIN tblSalesOrderLine
ON tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber)
ON tblVAT.VATRate = tblSalesOrderLine.VATRate
WHERE tblDespatch.[Despatch Number]=
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

Hi could anybody please adapt the following query to get it to do what I
want
to do

[quoted text clipped - 22 lines]

(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[graeme34 via AccessMonster.com]

Hi John

Still being prompted here is the slimmed down query :

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE tblDespatch.DespatchNumber=
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

DespatchNumber is the correct spelling for the field in tblDespatch , whereas
in the despatch detail it is [Despatch Number]. The query still promts for
two inputs asking for tblDespatch.DespatchNumber. Even when I manually enter
two inputs that I know relate, i.e OrderNumber 7 , Despatch number 81 . It is
just bring up the full recordset of tblDespatch.....Sales Order number only
of course.....

Looks as if I may have made a "spelling" error in the where clause. I do
not use spaces in field and table names - it just leads to problems as far
as I am concerned. I left out a space in the revised WHERE clause. So if
you copied and pasted the query then that would be the problem.

When Access asks for a parameter that you don't believe you have in the
query, it is almost always a misspelling of a field name or table name.
Read the parameter prompt closely and you will probably see it asking for a
"misspelled" version of a field name.

Corrected (I hope) Where clause follows

SELECT ...
FROM ...
WHERE tblDespatch.[Despatch Number]=
(SELECT MAX(T.[Despatch Number])
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

hi John
Thanks for your help, but I think my logic is wrong anyway, I am getting

[quoted text clipped - 35 lines]

 

[John Spencer]

Since you've used an alias for the table, you need to use that alias
elsewhere. Try this for your slimmed down query.

SELECT D.SalesOrderNumber
FROM tblDespatch as D
WHERE D.DespatchNumber =
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

Hi John

Still being prompted here is the slimmed down query :

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE tblDespatch.DespatchNumber=
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

DespatchNumber is the correct spelling for the field in tblDespatch ,
whereas
in the despatch detail it is [Despatch Number]. The query still promts for
two inputs asking for tblDespatch.DespatchNumber. Even when I manually
enter
two inputs that I know relate, i.e OrderNumber 7 , Despatch number 81 . It
is
just bring up the full recordset of tblDespatch.....Sales Order number
only
of course.....

Looks as if I may have made a "spelling" error in the where clause. I do
not use spaces in field and table names - it just leads to problems as far
as I am concerned. I left out a space in the revised WHERE clause. So if
you copied and pasted the query then that would be the problem.

When Access asks for a parameter that you don't believe you have in the
query, it is almost always a misspelling of a field name or table name.
Read the parameter prompt closely and you will probably see it asking for
a
"misspelled" version of a field name.

Corrected (I hope) Where clause follows

SELECT ...
FROM ...
WHERE tblDespatch.[Despatch Number]=
(SELECT MAX(T.[Despatch Number])
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

hi John
Thanks for your help, but I think my logic is wrong anyway, I am getting

[quoted text clipped - 35 lines]

(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[graeme34 via AccessMonster.com]

Thank you John that seems to be working. It is selecting the order number
entered, I have also added the Despatch Number to the SELECT statement and it
is retrieving the correct record i.e. the maxium despatch number. Once again
thank you very much, one last thing do you know why I am getting a runtime
error on this query.....(missing operator) in query expression..

strSQL = "SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, " _
& "GR.AnotherRecRequired, GRD.QuantityReceived, GRD.ProductCode, " _
& "POD.PartReceived , POD.FullyReceived " _
& "FROM (tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD "
_
& "ON GR.GoodsReceivedNumber = GRD.GoodsReceivedNumber)" _
& " INNER JOIN ((tblPurchaseOrderDetails POD" _
& " ON GRD.PurchaseOrderNumber = POD.PurchaseOrderNumber) " _
& "AND (GRD.Product Code = POD.ProductCode)) " _
& "WHERE GR.PurchaseOrderNumber = " & lngNum _
& " AND GR.GoodsReceived = False;"

Since you've used an alias for the table, you need to use that alias
elsewhere. Try this for your slimmed down query.

SELECT D.SalesOrderNumber
FROM tblDespatch as D
WHERE D.DespatchNumber =
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

Hi John

[quoted text clipped - 45 lines]

 

[John Spencer]

I don't see it right off. Have you tried using Debug.print StrSQL and then
copying the SQL to a new query and trying to run it.

I often use that technique to allow me to figure out what I've done wrong.
I find the error messages a little clearer and often Access will show me the
exact spot of my error. Wtach out for missing spaces and matching
parentheses (especially in the pesky FROM clause with the joins). I've
added somes spaces and changed the parens in the FROM clause in the query
below

strSQL = "SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, " _
& "GR.AnotherRecRequired, GRD.QuantityReceived, GRD.ProductCode, " _
& "POD.PartReceived , POD.FullyReceived " _
& " FROM (tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD " _
& " ON GR.GoodsReceivedNumber = GRD.GoodsReceivedNumber)" _
& " INNER JOIN tblPurchaseOrderDetails POD" _
& " ON GRD.PurchaseOrderNumber = POD.PurchaseOrderNumber " _
& " AND GRD.Product Code = POD.ProductCode " _
& " WHERE GR.PurchaseOrderNumber = " & lngNum _
& " AND GR.GoodsReceived = False;"

Debug.Print strSQL
STOP

Thank you John that seems to be working. It is selecting the order number
entered, I have also added the Despatch Number to the SELECT statement and
it
is retrieving the correct record i.e. the maxium despatch number. Once
again
thank you very much, one last thing do you know why I am getting a runtime
error on this query.....(missing operator) in query expression..

strSQL = "SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, " _
& "GR.AnotherRecRequired, GRD.QuantityReceived, GRD.ProductCode, "
_
& "POD.PartReceived , POD.FullyReceived " _
& "FROM (tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD
"
_
& "ON GR.GoodsReceivedNumber = GRD.GoodsReceivedNumber)" _
& " INNER JOIN ((tblPurchaseOrderDetails POD" _
& " ON GRD.PurchaseOrderNumber = POD.PurchaseOrderNumber) " _
& "AND (GRD.Product Code = POD.ProductCode)) " _
& "WHERE GR.PurchaseOrderNumber = " & lngNum _
& " AND GR.GoodsReceived = False;"

Since you've used an alias for the table, you need to use that alias
elsewhere. Try this for your slimmed down query.

SELECT D.SalesOrderNumber
FROM tblDespatch as D
WHERE D.DespatchNumber =
(SELECT MAX(T.DespatchNumber)
FROM tblDespatch As T
WHERE T.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice])

Hi John

[quoted text clipped - 45 lines]

(DespatchNumber) FROM tblDespatch WHERE DespatchNumber =
tblDespatch.
DespatchNumber)));

 

[Tom Ellison]

Dear Graeme:

It is probable, almost certain, that either the form reference or the
control reference is incorrect. You should test this, find the error, and
correct it.

Make a simple query:

SELECT [Forms]![frmChooseOrderNumber]![txtOrderNumberChoice] AS XX
FROM tblDespatch

I believe it will prompt you. That's because the reference is incorrect.
Change it to access some other control on the form. Now does it work? If
not, perhaps you have the form name wrong.

When in doubt, copy and past the names from properties.

Tom Ellison

Hi Tom
Thanks for your interest,
SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number] =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)
Is working fine, but after being prompted for the Forms!....OrderNumber...
which was expected..I am promted for the despatch number, which is not
what I
want, although from looking at this less complex query I can see why
because
of the where clause (I think).
What I require is after the query runs it takes the order number from the
input form and find the highest despatch number with that order number.
I have tried experimenting with the SELECT MAX but I acnt seem to get the
syntax right.
Thanks again Tom

Dear Graeme:

You're a busy guy, eh?

I'm writing your query here for my reference:

SELECT DISTINCT A.AccountName, A.[Account Address 1],
A.[Account Address 2], A.[Account Address 3], A.[Account Town / City],
A.[Post Code], D.SalesOrderNumber, SO.CustomerOrderNumber,
tblSalesOrder.DeliveryMode,
SI.SalesInvoiceNumber, SI.InvoiceDate, SI.TotalNett, SOL.VATRate,
V.VATValue,
D.DespatchNumber
FROM tblVAT V
INNER JOIN (((tblAccount A
INNER JOIN tblSalesOrder SO
ON A.AccountIndex = SO.AccountIndex)
INNER JOIN (tblDespatch D
INNER JOINtblSalesInvoice SI
ON D.DespatchNumber = SI.[Despatch Number])
ON SO.SalesOrderNumber = D.SalesOrderNumber)
INNER JOIN tblSalesOrderLine SOL
ON SO.SalesOrderNumber = SOL.SalesOrderNumber)
ON V.VATRate = SOL.VATRate
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number]) =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)

When you write something a bit complex, start simple and get the simple
things working. For example:

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]

Is this working? Add a bit at a time and see when it breaks.

SELECT D.SalesOrderNumber
FROM tblDespatch D
WHERE D.SalesOrderNumber =
[Forms]![frmChooseOrderNumber]![txtOrderNumberChoice]
AND D.[Despatch Number]) =
(SELECT MAX(DespatchNumber)
FROM tblDespatch D1
WHERE DespatchNumber = D1.DespatchNumber)

By the way, when you have two copies of a table (tblDespatch) in your
query
and you do not reference them with distinct aliases, nasty things can
happen. It is just possible I fixed (or seriously broke) your query with
the alias in the subquery. Only you will be able to tell! Please make
all
the references throughout your query explicit as to which table is used,
even if you think the Column Names are already unique. You'll be
surprised
how seriously broken a query can become if you add a column to one of the
tables in the query, only to find later that it duplicates a column from
another table in that query, which you failed to explicitly reference
(table.column).

Tom Ellison

Hi could anybody please adapt the following query to get it to do what I
want

[quoted text clipped - 27 lines]

(DespatchNumber) FROM tblDespatch WHERE DespatchNumber = tblDespatch.
DespatchNumber)));

 

[graeme34 via AccessMonster.com]

hi John I tried yuor technique, your right it is much easy than VBA error
message, but I still can't find the mistake. In the end I used the QBE and
tried adapting mine to suit.
The top query is the QBE which works...

SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, GR.AnotherRecRequired, GRD.
ProductCode, GRD.QuantityReceived, POD.PartReceived, POD.FullyReceived FROM
(tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD ON GR.
GoodsReceivedNumber = GRD.GoodsReceivedNumber)
INNER JOIN tblPurchaseOrderDetails POD ON (GRD.PurchaseOrderNumber = POD.
PurchaseOrderNumber) AND (GRD.ProductCode = POD.ProductCode) WHERE GR.
PurchaseOrderNumber = 5 AND GR.GoodsReceived = False;

The next query is mine which doesnt work?? can you see any difference, the
line it doesnt like is the join between GRD.PurchaseOrderNumber and POD.
PurchaseOrderNumber....here is the full query...

SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, GR.AnotherRecRequired, GRD.
QuantityReceived, GRD.ProductCode, POD.PartReceived , POD.FullyReceived FROM
(tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD ON GR.
GoodsReceivedNumber = GRD.GoodsReceivedNumber)
INNER JOIN tblPurchaseOrderDetails POD ON (GRD.PurchaseOrderNumber = POD.
PurchaseOrderNumber) AND (GRD.Product Code = POD.ProductCode) WHERE GR.
PurchaseOrderNumber = 5 AND GR.GoodsReceived = False;

I cant seem to see no difference yet the top one works and the bottom doesnt??

I don't see it right off. Have you tried using Debug.print StrSQL and then
copying the SQL to a new query and trying to run it.

I often use that technique to allow me to figure out what I've done wrong.
I find the error messages a little clearer and often Access will show me the
exact spot of my error. Wtach out for missing spaces and matching
parentheses (especially in the pesky FROM clause with the joins). I've
added somes spaces and changed the parens in the FROM clause in the query
below

strSQL = "SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, " _
& "GR.AnotherRecRequired, GRD.QuantityReceived, GRD.ProductCode, " _
& "POD.PartReceived , POD.FullyReceived " _
& " FROM (tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD " _
& " ON GR.GoodsReceivedNumber = GRD.GoodsReceivedNumber)" _
& " INNER JOIN tblPurchaseOrderDetails POD" _
& " ON GRD.PurchaseOrderNumber = POD.PurchaseOrderNumber " _
& " AND GRD.Product Code = POD.ProductCode " _
& " WHERE GR.PurchaseOrderNumber = " & lngNum _
& " AND GR.GoodsReceived = False;"

Debug.Print strSQL
STOP

Thank you John that seems to be working. It is selecting the order number
entered, I have also added the Despatch Number to the SELECT statement and

[quoted text clipped - 35 lines]

 

[graeme34 via AccessMonster.com]

By the way, if its not an obvious error, dont worry about it too much it was
only for future reference....I ended copying the working query into my code...

hi John I tried yuor technique, your right it is much easy than VBA error
message, but I still can't find the mistake. In the end I used the QBE and
tried adapting mine to suit.
The top query is the QBE which works...

SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, GR.AnotherRecRequired, GRD.
ProductCode, GRD.QuantityReceived, POD.PartReceived, POD.FullyReceived FROM
(tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD ON GR.
GoodsReceivedNumber = GRD.GoodsReceivedNumber)
INNER JOIN tblPurchaseOrderDetails POD ON (GRD.PurchaseOrderNumber = POD.
PurchaseOrderNumber) AND (GRD.ProductCode = POD.ProductCode) WHERE GR.
PurchaseOrderNumber = 5 AND GR.GoodsReceived = False;

The next query is mine which doesnt work?? can you see any difference, the
line it doesnt like is the join between GRD.PurchaseOrderNumber and POD.
PurchaseOrderNumber....here is the full query...

SELECT GR.PurchaseOrderNumber, GR.GoodsReceived, GR.AnotherRecRequired, GRD.
QuantityReceived, GRD.ProductCode, POD.PartReceived , POD.FullyReceived FROM
(tblGoodsReceived GR INNER JOIN tblGoodsReceivedDetail GRD ON GR.
GoodsReceivedNumber = GRD.GoodsReceivedNumber)
INNER JOIN tblPurchaseOrderDetails POD ON (GRD.PurchaseOrderNumber = POD.
PurchaseOrderNumber) AND (GRD.Product Code = POD.ProductCode) WHERE GR.
PurchaseOrderNumber = 5 AND GR.GoodsReceived = False;

I cant seem to see no difference yet the top one works and the bottom doesnt??

I don't see it right off. Have you tried using Debug.print StrSQL and then
copying the SQL to a new query and trying to run it.

[quoted text clipped - 25 lines]