syntax help on adding filter to qry in report_open()

[Guest]

At run-time, I need to pass a parameter (INVOICE_NO) to the query (qry1) that
is the base of my report (rptInvoice). The actual Record Source for my
rptInvoice is qry2, which joins qry1 with another 6 tables and has 4 where
conditions join by “ORâ€s.
I think Report_Open event is the right place to run my code, but I am having
difficulty to get the syntax right.

Under design time, I have been manually typed in the Invoice into qry1, then
previewed the report to get all the details. Now I am stuck when I want to
move it to run time.

I have tried:
1)
Me![queries]!qry1.FilterOn = True
queries.qry1.Filter = [INVOICE_NO] = "535706"
2)
Dim db As Database
Dim qrydef As QueryDef

Set db = currentdb()
Set qrydef = db.QueryDefs("qry1")
qrydef("INVOICE_NO") = "535706"

By the way, my calling code is as follow, where strTmp =†INVOICE_NO" =
"535706"

DoCmd.OpenReport GetDBReportName(), acViewPreview, , strTmp

How do I get the strTmp value in Report_Open?

Thanks!

 

[Guest]

On qrydef("INVOICE_NO") = "535706"
I got "item not found in this collection run-time error 3265"
however, I can see the property in debug view window.

By the way, I am running ac97.

 

[Guest]

Hi Homer,

Try this:
Add a command button to your form. Name the command button
"cmdPreviewInvoice". Then add the following code as the click event
procedure for this command button:

Option Compare Database
Option Explicit

Private Sub cmdPreviewInvoice_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = " & Me.INVOICE_NO


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPreviewInvoice_Click event procedure..."
Resume ExitProc
End Sub


This assumes that a field named INVOICE_NO is available in the form's
recordsource.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

At run-time, I need to pass a parameter (INVOICE_NO) to the query (qry1) that
is the base of my report (rptInvoice). The actual Record Source for my
rptInvoice is qry2, which joins qry1 with another 6 tables and has 4 where
conditions join by “ORâ€s.
I think Report_Open event is the right place to run my code, but I am having
difficulty to get the syntax right.

Under design time, I have been manually typed in the Invoice into qry1, then
previewed the report to get all the details. Now I am stuck when I want to
move it to run time.

I have tried:
1)
Me![queries]!qry1.FilterOn = True
queries.qry1.Filter = [INVOICE_NO] = "535706"
2)
Dim db As Database
Dim qrydef As QueryDef

Set db = currentdb()
Set qrydef = db.QueryDefs("qry1")
qrydef("INVOICE_NO") = "535706"

By the way, my calling code is as follow, where strTmp =†INVOICE_NO" =
"535706"

DoCmd.OpenReport GetDBReportName(), acViewPreview, , strTmp

How do I get the strTmp value in Report_Open?

Thanks!

 

[Guest]

PS. The solution I proposed in my first reply is valid only if the
INVOICE_NO field is numeric. If the data type is text, then use the following
instead:

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = '" & Me.INVOICE_NO & "'"

For clarity, that's a single quote inserted after the second = sign, and
doublequote-single quote-doublequote concatenated onto the end.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Homer,

Try this:
Add a command button to your form. Name the command button
"cmdPreviewInvoice". Then add the following code as the click event
procedure for this command button:

Option Compare Database
Option Explicit

Private Sub cmdPreviewInvoice_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = " & Me.INVOICE_NO


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPreviewInvoice_Click event procedure..."
Resume ExitProc
End Sub


This assumes that a field named INVOICE_NO is available in the form's
recordsource.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

At run-time, I need to pass a parameter (INVOICE_NO) to the query (qry1) that
is the base of my report (rptInvoice). The actual Record Source for my
rptInvoice is qry2, which joins qry1 with another 6 tables and has 4 where
conditions join by “ORâ€s.
I think Report_Open event is the right place to run my code, but I am having
difficulty to get the syntax right.

Under design time, I have been manually typed in the Invoice into qry1, then
previewed the report to get all the details. Now I am stuck when I want to
move it to run time.

I have tried:
1)
Me![queries]!qry1.FilterOn = True
queries.qry1.Filter = [INVOICE_NO] = "535706"
2)
Dim db As Database
Dim qrydef As QueryDef

Set db = currentdb()
Set qrydef = db.QueryDefs("qry1")
qrydef("INVOICE_NO") = "535706"

By the way, my calling code is as follow, where strTmp =†INVOICE_NO" =
"535706"

DoCmd.OpenReport GetDBReportName(), acViewPreview, , strTmp

How do I get the strTmp value in Report_Open?

Thanks!

 

[Guest]

Thank you for your reply, Tom!
I was hoping to get syntax help, and you gave me a total solution. I guess
that is because I have left out some of the complexity from my posts.
I need to apply the filter to qry1 before qry2 run, then my report is base
off the qry2 as record source.

You may ask why we have forced ourselves into this kind of awkward corner.
INVOICE_NO is a user input value at the form.
I cannot apply it to qry2, because it would not take it. When I added the
filter in the property window in design view, the query runs itself into not
responding. However, if I added the filter in qry1, then run qry2, I got
exactly what I want.
The reason that the qry2 did not take the filter, I think, is that it has a
very complex where clause, and the filter value did not get into the flow
multiple times as needed. By applying it earlier in qry1, qry2 will face a
lot smaller subset of data to do its job.

PS. The solution I proposed in my first reply is valid only if the
INVOICE_NO field is numeric. If the data type is text, then use the following
instead:

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = '" & Me.INVOICE_NO & "'"

For clarity, that's a single quote inserted after the second = sign, and
doublequote-single quote-doublequote concatenated onto the end.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Homer,

Try this:
Add a command button to your form. Name the command button
"cmdPreviewInvoice". Then add the following code as the click event
procedure for this command button:

Option Compare Database
Option Explicit

Private Sub cmdPreviewInvoice_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = " & Me.INVOICE_NO


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPreviewInvoice_Click event procedure..."
Resume ExitProc
End Sub


This assumes that a field named INVOICE_NO is available in the form's
recordsource.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

At run-time, I need to pass a parameter (INVOICE_NO) to the query (qry1) that
is the base of my report (rptInvoice). The actual Record Source for my
rptInvoice is qry2, which joins qry1 with another 6 tables and has 4 where
conditions join by “ORâ€s.
I think Report_Open event is the right place to run my code, but I am having
difficulty to get the syntax right.

Under design time, I have been manually typed in the Invoice into qry1, then
previewed the report to get all the details. Now I am stuck when I want to
move it to run time.

I have tried:
1)
Me![queries]!qry1.FilterOn = True
queries.qry1.Filter = [INVOICE_NO] = "535706"
2)
Dim db As Database
Dim qrydef As QueryDef

Set db = currentdb()
Set qrydef = db.QueryDefs("qry1")
qrydef("INVOICE_NO") = "535706"

By the way, my calling code is as follow, where strTmp =†INVOICE_NO" =
"535706"

DoCmd.OpenReport GetDBReportName(), acViewPreview, , strTmp

How do I get the strTmp value in Report_Open?

Thanks!

 

[Guest]

Hi Homer,

It sounds like you got what you needed?

I cannot apply it to qry2, because it would not take it. When I added the
filter in the property window in design view, the query runs itself into not
responding.

If you post the SQL statements for qry1 and qry2, we might be able to help
further.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thank you for your reply, Tom!
I was hoping to get syntax help, and you gave me a total solution. I guess
that is because I have left out some of the complexity from my posts.
I need to apply the filter to qry1 before qry2 run, then my report is base
off the qry2 as record source.

You may ask why we have forced ourselves into this kind of awkward corner.
INVOICE_NO is a user input value at the form.
I cannot apply it to qry2, because it would not take it. When I added the
filter in the property window in design view, the query runs itself into not
responding. However, if I added the filter in qry1, then run qry2, I got
exactly what I want.
The reason that the qry2 did not take the filter, I think, is that it has a
very complex where clause, and the filter value did not get into the flow
multiple times as needed. By applying it earlier in qry1, qry2 will face a
lot smaller subset of data to do its job.
__________________________________________

:

PS. The solution I proposed in my first reply is valid only if the
INVOICE_NO field is numeric. If the data type is text, then use the following
instead:

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = '" & Me.INVOICE_NO & "'"

For clarity, that's a single quote inserted after the second = sign, and
doublequote-single quote-doublequote concatenated onto the end.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Homer,

Try this:
Add a command button to your form. Name the command button
"cmdPreviewInvoice". Then add the following code as the click event
procedure for this command button:

Option Compare Database
Option Explicit

Private Sub cmdPreviewInvoice_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = " & Me.INVOICE_NO


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPreviewInvoice_Click event procedure..."
Resume ExitProc
End Sub


This assumes that a field named INVOICE_NO is available in the form's
recordsource.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

At run-time, I need to pass a parameter (INVOICE_NO) to the query (qry1) that
is the base of my report (rptInvoice). The actual Record Source for my
rptInvoice is qry2, which joins qry1 with another 6 tables and has 4 where
conditions join by “ORâ€s.
I think Report_Open event is the right place to run my code, but I am having
difficulty to get the syntax right.

Under design time, I have been manually typed in the Invoice into qry1, then
previewed the report to get all the details. Now I am stuck when I want to
move it to run time.

I have tried:
1)
Me![queries]!qry1.FilterOn = True
queries.qry1.Filter = [INVOICE_NO] = "535706"
2)
Dim db As Database
Dim qrydef As QueryDef

Set db = currentdb()
Set qrydef = db.QueryDefs("qry1")
qrydef("INVOICE_NO") = "535706"

By the way, my calling code is as follow, where strTmp =†INVOICE_NO" =
"535706"

DoCmd.OpenReport GetDBReportName(), acViewPreview, , strTmp

How do I get the strTmp value in Report_Open?

Thanks!

 

[Guest]

Here they are: the ---- line seperates qry1 to qry2
SELECT
IIf([AR_POSTPRINTINVCDTL].[BO_QTY]<=0.05*[AR_POSTPRINTINVCDTL].[SHIP_QTY],0,[AR_POSTPRINTINVCDTL].[BO_QTY])
AS BO_QTY, DBA_account.contact_acct_name, AR_POSTPRINTINVCDTL.SHIP_REF,
JobTracking.ProductFamily, AR_POSTPRINTINVCDTL.CO_NUMBER AS CONum,
AR_POSTPRINTINVCDTL.SHIP_STATE, AR_POSTPRINTINVCDTL.SHIP_CNTRY,
AR_POSTPRINTINVCDTL.SHIP_ZIP, AR_POSTPRINTINVCDTL.SHIP_CITY,
AR_POSTPRINTINVCDTL.CUST_PO_NO, AR_POSTPRINTINVCDTL.ITEM,
AR_POSTPRINTINVCDTL.ADD_DESC AS ITEM_DESC, AR_POSTPRINTINVCDTL.LN_NO,
AR_POSTPRINTINVCDTL.DUE_DATE AS NET_DATE, AR_POSTPRINTINVCDTL.ORDER_QTY,
AR_POSTPRINTINVCDTL.PAY_TERM, DBA_account.phone,
SemiUnique([AR_POSTPRINTINVCDTL].[AR_IVC_NO]) AS SEQN,
AR_POSTPRINTINVCDTL.SHIP_TO_NM, AR_POSTPRINTINVCDTL.SHIP_ADDR1,
AR_POSTPRINTINVCDTL.SHIP_ADDR2, AR_POSTPRINTINVCDTL.SHIP_VIA,
JobTracking.CreditCard, JobTracking.NumColors, JobTracking.ndim1,
JobTracking.ndim2, AR_POSTPRINTINVCDTL.UNIT_PRICE,
AR_POSTPRINTINVCDTL.CUST_ID, AR_POSTPRINTINVCDTL.SHIP_DATE,
AR_POSTPRINTINVCDTL.AR_IVC_NO AS INVOICE_NO, AR_POSTPRINTINVCDTL.SHPMNT_NO,
AR_POSTPRINTINVCDTL.TOTALLESS AS TOT_LESSX,
[AR_POSTPRINTINVCDTL].[TOT_PYMNTS]+[DISC_ALLWD] AS TOT_PYMNTS,
AR_POSTPRINTINVCDTL.TOTALSALES AS TOT_SALESX, InvoiceTax([AR_IVC_NO]) AS
TOT_TAXAMX, AR_POSTPRINTINVCDTL.TOTALOTHER AS TOT_OTHERX,
[TOTALSALES]+[TOTALFRGHT]+[TOTALLESS]+[TOTALOTHER]+InvoiceTax([AR_IVC_NO]) AS
TOT_IVCX, AR_POSTPRINTINVCDTL.TOTALFRGHT AS TOT_FRGHTX, JobTracking.Copy,
DBA_account.fax, [SHIP_QTY]*[UNIT_PRICE] AS EXTD_PRICE,
JobTracking.ReturnDisk, JobTracking.ReturnArt, JobTracking.SendSamples,
AR_POSTPRINTINVCDTL.IVC_DATE, AR_POSTPRINTINVCDTL.SHIP_QTY AS SHIPD_QTY
FROM DBA_account RIGHT JOIN (AR_POSTPRINTINVCDTL INNER JOIN JobTracking ON
AR_POSTPRINTINVCDTL.CO_NUMBER = JobTracking.CO_Number) ON
DBA_account.ext_acctid2 = AR_POSTPRINTINVCDTL.CUST_ID
WHERE (((AR_POSTPRINTINVCDTL.IVC_TYPE)="I"));
-------------------------------------------

SELECT DISTINCT DBA_part.user_code_2, DBA_part.user_code_1,
DBA_line_dtl.line_status, DBA_order_header.head_lead_time, qryCustInvoice.*,
DBA_rw_property.value, ShipMast.COD_Amount, MaterialCodes.MatlDesc,
DBA_rw_property.item_id
FROM ((ShipMast RIGHT JOIN (((qryCustInvoice INNER JOIN DBA_order_header ON
qryCustInvoice.CONum = DBA_order_header.head_order_nbr) INNER JOIN DBA_part
ON qryCustInvoice.ITEM = DBA_part.item) INNER JOIN DBA_rw_property ON
DBA_order_header.head_order_nbr = DBA_rw_property.head_order_nbr) ON
ShipMast.CO_Number = DBA_order_header.head_order_nbr) LEFT JOIN MaterialCodes
ON DBA_rw_property.value = MaterialCodes.MatlRef) INNER JOIN DBA_line_dtl ON
(DBA_line_dtl.line_number = qryCustInvoice.LN_NO) AND
(DBA_order_header.head_order_nbr = DBA_line_dtl.head_order_nbr)
WHERE (((DBA_part.user_code_1)<>"I" And (DBA_part.user_code_1)<>"T" And
(DBA_part.user_code_1)<>"O") AND ((DBA_rw_property.item_id)="Cut_Type") AND
((qryCustInvoice.UNIT_PRICE)=0)) OR (((DBA_part.user_code_1)<>"I" And
(DBA_part.user_code_1)<>"O") AND ((DBA_rw_property.item_id)="Cut_Type") AND
((qryCustInvoice.UNIT_PRICE)>0)) OR (((DBA_part.user_code_2)="N") AND
((DBA_part.user_code_1)="O") AND ((DBA_rw_property.item_id)="Cut_Type") AND
((qryCustInvoice.UNIT_PRICE)>0)) OR (((DBA_part.user_code_2) Is Null) AND
((DBA_part.user_code_1)="O") AND ((DBA_rw_property.item_id)="Cut_Type"));

Hi Homer,

It sounds like you got what you needed?

I cannot apply it to qry2, because it would not take it. When I added the
filter in the property window in design view, the query runs itself into not
responding.

If you post the SQL statements for qry1 and qry2, we might be able to help
further.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thank you for your reply, Tom!
I was hoping to get syntax help, and you gave me a total solution. I guess
that is because I have left out some of the complexity from my posts.
I need to apply the filter to qry1 before qry2 run, then my report is base
off the qry2 as record source.

You may ask why we have forced ourselves into this kind of awkward corner.
INVOICE_NO is a user input value at the form.
I cannot apply it to qry2, because it would not take it. When I added the
filter in the property window in design view, the query runs itself into not
responding. However, if I added the filter in qry1, then run qry2, I got
exactly what I want.
The reason that the qry2 did not take the filter, I think, is that it has a
very complex where clause, and the filter value did not get into the flow
multiple times as needed. By applying it earlier in qry1, qry2 will face a
lot smaller subset of data to do its job.
__________________________________________

:

PS. The solution I proposed in my first reply is valid only if the
INVOICE_NO field is numeric. If the data type is text, then use the following
instead:

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = '" & Me.INVOICE_NO & "'"

For clarity, that's a single quote inserted after the second = sign, and
doublequote-single quote-doublequote concatenated onto the end.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Homer,

Try this:
Add a command button to your form. Name the command button
"cmdPreviewInvoice". Then add the following code as the click event
procedure for this command button:

Option Compare Database
Option Explicit

Private Sub cmdPreviewInvoice_Click()
On Error GoTo ProcError

DoCmd.OpenReport ReportName:="rptInvoice", View:=acPreview, _
WhereCondition:="INVOICE_NO = " & Me.INVOICE_NO


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPreviewInvoice_Click event procedure..."
Resume ExitProc
End Sub


This assumes that a field named INVOICE_NO is available in the form's
recordsource.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

At run-time, I need to pass a parameter (INVOICE_NO) to the query (qry1) that
is the base of my report (rptInvoice). The actual Record Source for my
rptInvoice is qry2, which joins qry1 with another 6 tables and has 4 where
conditions join by “ORâ€s.
I think Report_Open event is the right place to run my code, but I am having
difficulty to get the syntax right.

Under design time, I have been manually typed in the Invoice into qry1, then
previewed the report to get all the details. Now I am stuck when I want to
move it to run time.

I have tried:
1)
Me![queries]!qry1.FilterOn = True
queries.qry1.Filter = [INVOICE_NO] = "535706"
2)
Dim db As Database
Dim qrydef As QueryDef

Set db = currentdb()
Set qrydef = db.QueryDefs("qry1")
qrydef("INVOICE_NO") = "535706"

By the way, my calling code is as follow, where strTmp =†INVOICE_NO" =
"535706"

DoCmd.OpenReport GetDBReportName(), acViewPreview, , strTmp

How do I get the strTmp value in Report_Open?

Thanks!

 

[Guest]

Hi Homer,

If you are willing to send me a compacted and zipped copy of your database,
I will take a look at it for you. My e-mail address is available on the
contributors page shown in my signature. Please do not post your e-mail
address (or mine) in a reply. This only invites the unwanted attention of
spammers.

I can't do too much with the SQL statements that you supplied, since I don't
know what the procedures SemiUnique and InvoiceTax are all about (referenced
in qry1), and qryCustInvoice looks like (referenced in qry2).

I see a field with a name that is considered a reserved word. While this may
not be the cause of any problems, my advice is to always avoid the use of
reserved words for anything that you assign a name to in Access:

DBA_rw_property.value

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

I also see evidence of improper database design. For example, it appears as
if you are storing several totals in the table AR_POSTPRINTINVCDTL. If these
fields represent values that can be calculated from other fields in your
database, then the design is not correct. In general, you do not want to
store values that can be calculated. If AR_POSTPRINTINVCDTL is a query
instead of a table, then this does not apply.

In any case, I can provide more assistance if I actually have a copy of your
database to work with.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Here they are: the ---- line seperates qry1 to qry2
SELECT
IIf([AR_POSTPRINTINVCDTL].[BO_QTY]<=0.05*[AR_POSTPRINTINVCDTL].[SHIP_QTY],0,[AR_POSTPRINTINVCDTL].[BO_QTY])
AS BO_QTY, DBA_account.contact_acct_name, AR_POSTPRINTINVCDTL.SHIP_REF,
JobTracking.ProductFamily, AR_POSTPRINTINVCDTL.CO_NUMBER AS CONum,
AR_POSTPRINTINVCDTL.SHIP_STATE, AR_POSTPRINTINVCDTL.SHIP_CNTRY,
AR_POSTPRINTINVCDTL.SHIP_ZIP, AR_POSTPRINTINVCDTL.SHIP_CITY,
AR_POSTPRINTINVCDTL.CUST_PO_NO, AR_POSTPRINTINVCDTL.ITEM,
AR_POSTPRINTINVCDTL.ADD_DESC AS ITEM_DESC, AR_POSTPRINTINVCDTL.LN_NO,
AR_POSTPRINTINVCDTL.DUE_DATE AS NET_DATE, AR_POSTPRINTINVCDTL.ORDER_QTY,
AR_POSTPRINTINVCDTL.PAY_TERM, DBA_account.phone,
SemiUnique([AR_POSTPRINTINVCDTL].[AR_IVC_NO]) AS SEQN,
AR_POSTPRINTINVCDTL.SHIP_TO_NM, AR_POSTPRINTINVCDTL.SHIP_ADDR1,
AR_POSTPRINTINVCDTL.SHIP_ADDR2, AR_POSTPRINTINVCDTL.SHIP_VIA,
JobTracking.CreditCard, JobTracking.NumColors, JobTracking.ndim1,
JobTracking.ndim2, AR_POSTPRINTINVCDTL.UNIT_PRICE,
AR_POSTPRINTINVCDTL.CUST_ID, AR_POSTPRINTINVCDTL.SHIP_DATE,
AR_POSTPRINTINVCDTL.AR_IVC_NO AS INVOICE_NO, AR_POSTPRINTINVCDTL.SHPMNT_NO,
AR_POSTPRINTINVCDTL.TOTALLESS AS TOT_LESSX,
[AR_POSTPRINTINVCDTL].[TOT_PYMNTS]+[DISC_ALLWD] AS TOT_PYMNTS,
AR_POSTPRINTINVCDTL.TOTALSALES AS TOT_SALESX, InvoiceTax([AR_IVC_NO]) AS
TOT_TAXAMX, AR_POSTPRINTINVCDTL.TOTALOTHER AS TOT_OTHERX,
[TOTALSALES]+[TOTALFRGHT]+[TOTALLESS]+[TOTALOTHER]+InvoiceTax([AR_IVC_NO]) AS
TOT_IVCX, AR_POSTPRINTINVCDTL.TOTALFRGHT AS TOT_FRGHTX, JobTracking.Copy,
DBA_account.fax, [SHIP_QTY]*[UNIT_PRICE] AS EXTD_PRICE,
JobTracking.ReturnDisk, JobTracking.ReturnArt, JobTracking.SendSamples,
AR_POSTPRINTINVCDTL.IVC_DATE, AR_POSTPRINTINVCDTL.SHIP_QTY AS SHIPD_QTY
FROM DBA_account RIGHT JOIN (AR_POSTPRINTINVCDTL INNER JOIN JobTracking ON
AR_POSTPRINTINVCDTL.CO_NUMBER = JobTracking.CO_Number) ON
DBA_account.ext_acctid2 = AR_POSTPRINTINVCDTL.CUST_ID
WHERE (((AR_POSTPRINTINVCDTL.IVC_TYPE)="I"));
-------------------------------------------

SELECT DISTINCT DBA_part.user_code_2, DBA_part.user_code_1,
DBA_line_dtl.line_status, DBA_order_header.head_lead_time, qryCustInvoice.*,
DBA_rw_property.value, ShipMast.COD_Amount, MaterialCodes.MatlDesc,
DBA_rw_property.item_id
FROM ((ShipMast RIGHT JOIN (((qryCustInvoice INNER JOIN DBA_order_header ON
qryCustInvoice.CONum = DBA_order_header.head_order_nbr) INNER JOIN DBA_part
ON qryCustInvoice.ITEM = DBA_part.item) INNER JOIN DBA_rw_property ON
DBA_order_header.head_order_nbr = DBA_rw_property.head_order_nbr) ON
ShipMast.CO_Number = DBA_order_header.head_order_nbr) LEFT JOIN MaterialCodes
ON DBA_rw_property.value = MaterialCodes.MatlRef) INNER JOIN DBA_line_dtl ON
(DBA_line_dtl.line_number = qryCustInvoice.LN_NO) AND
(DBA_order_header.head_order_nbr = DBA_line_dtl.head_order_nbr)
WHERE (((DBA_part.user_code_1)<>"I" And (DBA_part.user_code_1)<>"T" And
(DBA_part.user_code_1)<>"O") AND ((DBA_rw_property.item_id)="Cut_Type") AND
((qryCustInvoice.UNIT_PRICE)=0)) OR (((DBA_part.user_code_1)<>"I" And
(DBA_part.user_code_1)<>"O") AND ((DBA_rw_property.item_id)="Cut_Type") AND
((qryCustInvoice.UNIT_PRICE)>0)) OR (((DBA_part.user_code_2)="N") AND
((DBA_part.user_code_1)="O") AND ((DBA_rw_property.item_id)="Cut_Type") AND
((qryCustInvoice.UNIT_PRICE)>0)) OR (((DBA_part.user_code_2) Is Null) AND
((DBA_part.user_code_1)="O") AND ((DBA_rw_property.item_id)="Cut_Type"));