how to use the AS keyword

  • Thread starter Thread starter graeme34 via AccessMonster.com
  • Start date Start date
G

graeme34 via AccessMonster.com

Hi I am currently attempting to you SQL in my VBA code, im getting there but
finding it a bit long winded with all the typing I am aware of the AS
operator but not too sure how to use it, I have had no joy in the Access help
files, could anybody here help??
Here is my SQL statement:

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

As you can see quite a lot of repetitive typing such as tbl.Names.....
 
Dear Graeme:

After you build the string, have the code stop and you can look at it to see
if it looks right. You have made errors that will prevent it from working.
Use the debug capabilities to see the string you have generated so you can
see to fix it.

One good thing to do would be to use Aliases for the long table names. That
would save much of the typing.

Some samples of errors in your code below:

graeme34 via AccessMonster.com said:
Hi I am currently attempting to you SQL in my VBA code, im getting there
but
finding it a bit long winded with all the typing I am aware of the AS
operator but not too sure how to use it, I have had no joy in the Access
help
files, could anybody here help??
Here is my SQL statement:

strSQL = "SELECT tblGoodsReceived.PurchaseOrderNumber, " _
& "tblGoodsReceived.GoodsReceived, tblGoodsReceived. " _
Click to expand...
you have put a space at the end here. no-no.
& "AnotherRecRequired, tblGoodsReceivedDetail.QuantityReceived, " _
& "tblPurchaseOrderDetails.PartReceived,tblPurchaseOrderDetails." _
Click to expand...
see, here you do not put a space in the middle of a field spec. this is
correct.
& "FullyReceived " _
& "FROM (tblGoodsReceived INNER JOIN tblGoodsReceivedDetail " _
& "ON tblGoodsReceived.GoodsReceivedNumber = " _
& "tblGoodsReceivedDetail.GoodsReceivedNumber) INNER JOIN " _
& "tblPurchaseOrderDetails ON (tblGoodsReceivedDetail." _
& "PurchaseOrderNumber = tblPurchaseOrderDetails.PurchaseOrderNumber) "
_
& "AND (tblGoodsReceivedDetail.ProductCode = " _
& "tblPurchaseOrderDetails.ProductCode) " _
& "WHERE tblGoodsReceived.PurchaseOrderNumber = " _
& lngOrderNum & " AND GoodsReceived = False ;"

As you can see quite a lot of repetitive typing such as tbl.Names.....
Click to expand...

Tom Ellison
 
hi Tom thanks for pointing the error out, I hadnt actually ran the code. It
is the alias syntax I require not too sure how to use it.
Any pointers would be appreciated :)

Tom said:
Dear Graeme:

After you build the string, have the code stop and you can look at it to see
if it looks right. You have made errors that will prevent it from working.
Use the debug capabilities to see the string you have generated so you can
see to fix it.

One good thing to do would be to use Aliases for the long table names. That
would save much of the typing.

Some samples of errors in your code below:
Hi I am currently attempting to you SQL in my VBA code, im getting there
but
Click to expand...
[quoted text clipped - 6 lines]
strSQL = "SELECT tblGoodsReceived.PurchaseOrderNumber, " _
& "tblGoodsReceived.GoodsReceived, tblGoodsReceived. " _
Click to expand...
you have put a space at the end here. no-no.
& "AnotherRecRequired, tblGoodsReceivedDetail.QuantityReceived, " _
& "tblPurchaseOrderDetails.PartReceived,tblPurchaseOrderDetails." _
Click to expand...
see, here you do not put a space in the middle of a field spec. this is
correct.
& "FullyReceived " _
& "FROM (tblGoodsReceived INNER JOIN tblGoodsReceivedDetail " _
Click to expand...
[quoted text clipped - 9 lines]
As you can see quite a lot of repetitive typing such as tbl.Names.....
Click to expand...

Tom Ellison
Click to expand...
 
Let's work with the query first, then work to make it code:

Aliasing goes like this:

SELECT GR.PurchaseOrderNumber, GR.GoodsReceived,
GR.AnotherRecRequired, 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 = lngOrderNum
AND GoodsReceived = False

The acronyms I have chosen are arbitrary, but perhaps useful.

It would be a good idea to fully specify the table.column names in all
places, not just some. There will come a day when you add a column to one
of these tables and the query will become broken, because the name of the
added column is already in one of the other tables (or queries). This can
be very surprising, especially if testing doesn't catch it and it is
installed for users to crash into.

Tom Ellison


graeme34 via AccessMonster.com said:
hi Tom thanks for pointing the error out, I hadnt actually ran the code.
It
is the alias syntax I require not too sure how to use it.
Any pointers would be appreciated :)

Tom said:
Dear Graeme:

After you build the string, have the code stop and you can look at it to
see
if it looks right. You have made errors that will prevent it from
working.
Use the debug capabilities to see the string you have generated so you can
see to fix it.

One good thing to do would be to use Aliases for the long table names.
That
would save much of the typing.

Some samples of errors in your code below:
Hi I am currently attempting to you SQL in my VBA code, im getting there
but
Click to expand...
[quoted text clipped - 6 lines]
strSQL = "SELECT tblGoodsReceived.PurchaseOrderNumber, " _
& "tblGoodsReceived.GoodsReceived, tblGoodsReceived. " _
Click to expand...
you have put a space at the end here. no-no.
& "AnotherRecRequired, tblGoodsReceivedDetail.QuantityReceived, " _
& "tblPurchaseOrderDetails.PartReceived,tblPurchaseOrderDetails." _
Click to expand...
see, here you do not put a space in the middle of a field spec. this is
correct.
& "FullyReceived " _
& "FROM (tblGoodsReceived INNER JOIN tblGoodsReceivedDetail " _
Click to expand...
[quoted text clipped - 9 lines]
As you can see quite a lot of repetitive typing such as tbl.Names.....
Click to expand...

Tom Ellison
Click to expand...
Click to expand...
 
Dear Graeme:

Oh, yeah, you asked about AS. It is optional. The same thing with AS would
be:

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

Tom Ellison


Tom Ellison said:
Let's work with the query first, then work to make it code:

Aliasing goes like this:

SELECT GR.PurchaseOrderNumber, GR.GoodsReceived,
GR.AnotherRecRequired, 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 = lngOrderNum
AND GoodsReceived = False

The acronyms I have chosen are arbitrary, but perhaps useful.

It would be a good idea to fully specify the table.column names in all
places, not just some. There will come a day when you add a column to one
of these tables and the query will become broken, because the name of the
added column is already in one of the other tables (or queries). This can
be very surprising, especially if testing doesn't catch it and it is
installed for users to crash into.

Tom Ellison


graeme34 via AccessMonster.com said:
hi Tom thanks for pointing the error out, I hadnt actually ran the code.
It
is the alias syntax I require not too sure how to use it.
Any pointers would be appreciated :)

Tom said:
Dear Graeme:

After you build the string, have the code stop and you can look at it to
see
if it looks right. You have made errors that will prevent it from
working.
Use the debug capabilities to see the string you have generated so you
can
see to fix it.

One good thing to do would be to use Aliases for the long table names.
That
would save much of the typing.

Some samples of errors in your code below:

Hi I am currently attempting to you SQL in my VBA code, im getting
there
but
[quoted text clipped - 6 lines]
strSQL = "SELECT tblGoodsReceived.PurchaseOrderNumber, " _
& "tblGoodsReceived.GoodsReceived, tblGoodsReceived. " _
you have put a space at the end here. no-no.
& "AnotherRecRequired, tblGoodsReceivedDetail.QuantityReceived, " _
& "tblPurchaseOrderDetails.PartReceived,tblPurchaseOrderDetails." _
see, here you do not put a space in the middle of a field spec. this is
correct.
& "FullyReceived " _
& "FROM (tblGoodsReceived INNER JOIN tblGoodsReceivedDetail " _
[quoted text clipped - 9 lines]

As you can see quite a lot of repetitive typing such as tbl.Names.....

Tom Ellison
Click to expand...
Click to expand...
Click to expand...
 
Back
Top