Create a report

T

Tony Girgenti

Can anybody create me a report in Access XP based on this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]![Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]![Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]![Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]![Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]![StrSalAmt] & " " & _
"OR " & [Forms]![Form1]![StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]![EndSalAmt] & " " & _
"OR " & [Forms]![Form1]![EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]![CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk] & " = true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"
 
J

James C

If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C
-----Original Message-----
Can anybody create me a report in Access XP based on this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]! [Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]! [Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]! [Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]! [Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]! [StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]! [EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]! [CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk] & " = true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.
Click to expand...
 
T

Tony Girgenti

Hello James.

I tried doing that and i am not having any luck. I posted numerous requests
here and other newsgroups and never got an answer as to how to do it.

What you see is what i use to create a record set from the tables and that
record set is used in another part of the program. I can't use that same
query to get a report to print out. I'm at the point now where the program
needs to be ready for use and the only thing left is to create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If you can tell me how
to do it, i would gladly try.

Thanks,
Tony

James C said:
If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C
-----Original Message-----
Can anybody create me a report in Access XP based on this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]! [Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]! [Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]! [Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]! [Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]! [StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]! [EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]! [CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk] & " = true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.
Click to expand...
Click to expand...
 
J

James

Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I posted numerous requests
here and other newsgroups and never got an answer as to how to do it.

What you see is what i use to create a record set from the tables and that
record set is used in another part of the program. I can't use that same
query to get a report to print out. I'm at the point now where the program
needs to be ready for use and the only thing left is to create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If you can tell me how
to do it, i would gladly try.

Thanks,
Tony

If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C
-----Original Message-----
Can anybody create me a report in Access XP based on this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]! [Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]! [Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]! [Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]! [Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]! [StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]! [EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]! [CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk]
Click to expand...
& "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.
Click to expand...
Click to expand...


.
Click to expand...
 
J

James

Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I posted numerous requests
here and other newsgroups and never got an answer as to how to do it.

What you see is what i use to create a record set from the tables and that
record set is used in another part of the program. I can't use that same
query to get a report to print out. I'm at the point now where the program
needs to be ready for use and the only thing left is to create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If you can tell me how
to do it, i would gladly try.

Thanks,
Tony

If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C
-----Original Message-----
Can anybody create me a report in Access XP based on this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]! [Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]! [Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]! [Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]! [Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]! [StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]! [EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]! [CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk]
Click to expand...
& "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.
Click to expand...
Click to expand...


.
Click to expand...
 
J

James

Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I posted numerous requests
here and other newsgroups and never got an answer as to how to do it.

What you see is what i use to create a record set from the tables and that
record set is used in another part of the program. I can't use that same
query to get a report to print out. I'm at the point now where the program
needs to be ready for use and the only thing left is to create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If you can tell me how
to do it, i would gladly try.

Thanks,
Tony

If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C
-----Original Message-----
Can anybody create me a report in Access XP based on this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]! [Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]! [Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]! [Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]! [Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]! [StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]! [EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
Click to expand...
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]! [CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk] & " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk]
Click to expand...
& "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.
Click to expand...
Click to expand...


.
Click to expand...
 
T

Tony Girgenti

Hi James.

As to your first question. That's exactly the rpoblem. How do you create a
report using the query that i have ? If you can tell me that, i would
gladly try it.

Tony

James said:
Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I posted numerous requests
here and other newsgroups and never got an answer as to how to do it.

What you see is what i use to create a record set from the tables and that
record set is used in another part of the program. I can't use that same
query to get a report to print out. I'm at the point now where the program
needs to be ready for use and the only thing left is to create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If you can tell me how
to do it, i would gladly try.

Thanks,
Tony

If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C

-----Original Message-----
Can anybody create me a report in Access XP based on
this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no =
sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]!
[Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk]
& " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]!
[Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk]
& " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk]
& " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk]
& " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]!
[Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk]
& " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]!
[Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk]
& " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]!
[StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]!
[EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]!
[CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk]
& " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk]
& " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk] & "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.
Click to expand...


.
Click to expand...
Click to expand...
 
A

Adrian Jansen

Have you looked at using the report wizard to guide you through the process
? It will at least produce something, which you may have to rearrange a
bit, but its a good start.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
Tony Girgenti said:
Hi James.

As to your first question. That's exactly the rpoblem. How do you create a
report using the query that i have ? If you can tell me that, i would
gladly try it.

Tony

James said:
Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I posted numerous requests
here and other newsgroups and never got an answer as to how to do it.

What you see is what i use to create a record set from the tables and that
record set is used in another part of the program. I can't use that same
query to get a report to print out. I'm at the point now where the program
needs to be ready for use and the only thing left is to create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If you can tell me how
to do it, i would gladly try.

Thanks,
Tony

If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to help
each other with problems not to get people to do things
for you.

HTH

James C

-----Original Message-----
Can anybody create me a report in Access XP based on
this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no =
sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]!
[Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk]
& " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]!
[Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk]
& " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk]
& " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk]
& " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]!
[Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk]
& " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]!
[Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk]
& " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]!
[StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]!
[EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]!
[CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk]
& " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk]
& " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk] & "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.



.
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Girgenti

Yes i did. How do you tell it to do what is indicated in the query that i
have posted ?

Thanks,
Tony

Adrian Jansen said:
Have you looked at using the report wizard to guide you through the process
? It will at least produce something, which you may have to rearrange a
bit, but its a good start.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
Tony Girgenti said:
Hi James.

As to your first question. That's exactly the rpoblem. How do you
Click to expand...
create
a
report using the query that i have ? If you can tell me that, i would
gladly try it.

Tony

James said:
Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I
posted numerous requests
here and other newsgroups and never got an answer as to
how to do it.

What you see is what i use to create a record set from
the tables and that
record set is used in another part of the program. I
can't use that same
query to get a report to print out. I'm at the point now
where the program
needs to be ready for use and the only thing left is to
create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If
you can tell me how
to do it, i would gladly try.

Thanks,
Tony

message
If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to
help
each other with problems not to get people to do things
for you.

HTH

James C

-----Original Message-----
Can anybody create me a report in Access XP based on
this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no =
sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]!
[Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk]
& " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]!
[Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk]
& " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk]
& " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk]
& " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]!
[Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk]
& " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]!
[Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk]
& " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]!
[StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]!
[EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]!
[CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk]
& " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk]
& " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk]
& "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.



.
Click to expand...
Click to expand...
Click to expand...
 
A

Adrian Jansen

Save that query in the queries list, then use it as the basis for the
Report. The first thing the Report wizard does is ask what table/query to
base the report on.

Mind you, I would not include the WHERE clause in the saved query, because
it contains references to several forms which must be open at the time the
query is run. While you can do this, it can be a pain to maintain. The
better way is to set all the criteria for the WHERE clause on a form (
preferably only one ), and then build the clause in code. Pass that clause
to the report in the openreport command.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
Tony Girgenti said:
Yes i did. How do you tell it to do what is indicated in the query that i
have posted ?

Thanks,
Tony

Adrian Jansen said:
Have you looked at using the report wizard to guide you through the process
? It will at least produce something, which you may have to rearrange a
bit, but its a good start.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
Tony Girgenti said:
Hi James.

As to your first question. That's exactly the rpoblem. How do you
Click to expand...
create
a
report using the query that i have ? If you can tell me that, i would
gladly try it.

Tony

Could you not create a report based on your current query
and then design your report? Then just get a command
button to open the report in print preview or something?

HTH

James
-----Original Message-----
Hello James.

I tried doing that and i am not having any luck. I
posted numerous requests
here and other newsgroups and never got an answer as to
how to do it.

What you see is what i use to create a record set from
the tables and that
record set is used in another part of the program. I
can't use that same
query to get a report to print out. I'm at the point now
where the program
needs to be ready for use and the only thing left is to
create the report
using the same record set as that query creates.

I want to do it myself, i'm willing to do it myself. If
you can tell me how
to do it, i would gladly try.

Thanks,
Tony

message
If you have the time to type all that out then would it
not be simple and cost effective to do it yourself. I am
not being rude but these newsgroups are for people to
help
each other with problems not to get people to do things
for you.

HTH

James C

-----Original Message-----
Can anybody create me a report in Access XP based on
this ?
I would gladly pay you for it.

"SELECT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no =
sa_lin.hdr_ticket_no " & _
"WHERE (sa_lin.item_no > ' ') " & _
"AND (sa_lin.item_no >= '" & [Forms]!
[Form1]![StrItm] & "' "
& _
"OR " & [Forms]![Form1]![StrItmChk]
& " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]!
[Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk]
& " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk]
& " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk]
& " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]!
[Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk]
& " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]!
[Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk]
& " = True) " & _
"AND (cust.bal >= " & [Forms]![Form1]!
[StrSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[StrSalAmtChk] & " = True) " & _
"AND (cust.bal <= " & [Forms]![Form1]!
[EndSalAmt] & " " & _
"OR " & [Forms]![Form1]!
[EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]!
[CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk]
& " = True) " & _
"AND ((" & [Forms]![Form1]![ExcCustChk]
& " = false) " & _
"OR (" & [Forms]![Form1]![ExcCustChk]
& "
= true and
cust.email_adrs > ' ')) " & _
"GROUP BY cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no " & _
"ORDER BY cust.nam;"


.



.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Distinct help 3
Can't get data on report using where condition 12

Top