populating forms from querie

[bolson7117 via AccessMonster.com]

I have two queries that I would like to make forms from.

777’s
900’s

The only difference between the two is a Criteria that shows one cycle or the
other. The problem im having is the addresses between the two. Here is what
the fields look like:

NAME – John Smith
ADDR1 – PO BOX Whatever
ADDR2 – Town, ST
ADDR3 - BLANK
ZIP

the problem is that some of my records show this:
NAME – John Smith
ADDR1 – C/O NAME (In care of) basically
ADDR2 – PO BOX Whatever
ADDR3 – Town, ST
ZIP

So when I make the forms I have to have one two for each 777’s and 900’s
because I have to change the ADDR3 field to line up with the ZIP. If I could
only show just the populated filed in the quire then I would have 4 different
forms or would it be possible to have only 2 forms and have it show the
correct ADDR3 when populated?

Thanks Guys

 

[KARL DEWEY]

You need to fix your data so that town and state are in their own fields.

Post the SQL for the two queries.

Why would you need 4 different forms at all? One form should do it.

Try this --
SELECT Name, ADDR1, IIF([ADDR3] Is Null, Null, [ADDR2]) AS ADDR_X, ADDR3, ZIP
FROM YourTable;

 

[bolson7117 via AccessMonster.com]

777's SQL


SELECT Last(CAV_BILLHISTDETL.MBRSEP) AS LastOfMBRSEP, CAV_MBRSEPMSTR.NAME,
CAV_MBRHISTDETL.BILLDATE, CAV_MBRSEPMSTR.ADDR1, CAV_MBRSEPMSTR.ADDR2,
CAV_MBRSEPMSTR.ADDR3, Val([ZIP]) AS [ZIP#], CAV_MEMBERDETL.CYCLE,
[CAV_MBRHISTDETl.ENERGY]*0.01 AS ENERGY, [CAV_MBRHISTDETl.ARREARS]*0.01 AS
ARREARS, [CAV_MBRHISTDETl.CONSDEP]*0.01 AS CONSDEP, [CAV_MEMBERDETL.AMOUNT]*0.
01 AS AMOUNT, Val([METER]) AS [METER#], CAV_MBRHISTDETL.NBRDAYSSVC,
CAV_MBRHISTDETL.METERREAD, CAV_MBRHISTDETL.KWH, [CAV_MBRHISTDETl.PENALTY]*0.
01 AS PENALTY, [CAV_MBRHISTDETl.TAX]*0.01 AS TAX, [CAV_MBRHISTDETl.SLCHG]*0.
01 AS SLCHG, [CAV_MBRHISTDETl.MISCCHG]*0.01 AS MISCCHG, [CAV_MBRHISTDETl.
ROUNDUP]*0.01 AS ROUNDUP, CAV_BILLHISTDETL.DEMANDREAD, CAV_BILLHISTDETL.
DEMANDUSAGE, CAV_BILLHISTDETL.BILLEDDEMAND, CAV_MEMBERDETL.RATE,
CAV_MBRHISTDETL.READDATE
FROM CAV_BILLHISTDETL INNER JOIN (CAV_MBRSEPMSTR INNER JOIN (CAV_MEMBERDETL
INNER JOIN CAV_MBRHISTDETL ON CAV_MEMBERDETL.MBRSEP = CAV_MBRHISTDETL.MBRSEP)
ON CAV_MBRSEPMSTR.MBRSEP = CAV_MBRHISTDETL.MBRSEP) ON CAV_BILLHISTDETL.MBRSEP
= CAV_MBRSEPMSTR.MBRSEP
GROUP BY CAV_MBRSEPMSTR.NAME, CAV_MBRHISTDETL.BILLDATE, CAV_MBRSEPMSTR.ADDR1,
CAV_MBRSEPMSTR.ADDR2, CAV_MBRSEPMSTR.ADDR3, Val([ZIP]), CAV_MEMBERDETL.CYCLE,
[CAV_MBRHISTDETl.ENERGY]*0.01, [CAV_MBRHISTDETl.ARREARS]*0.01,
[CAV_MBRHISTDETl.CONSDEP]*0.01, [CAV_MEMBERDETL.AMOUNT]*0.01, Val([METER]),
CAV_MBRHISTDETL.NBRDAYSSVC, CAV_MBRHISTDETL.METERREAD, CAV_MBRHISTDETL.KWH,
[CAV_MBRHISTDETl.PENALTY]*0.01, [CAV_MBRHISTDETl.TAX]*0.01, [CAV_MBRHISTDETl.
SLCHG]*0.01, [CAV_MBRHISTDETl.MISCCHG]*0.01, [CAV_MBRHISTDETl.ROUNDUP]*0.01,
CAV_BILLHISTDETL.DEMANDREAD, CAV_BILLHISTDETL.DEMANDUSAGE, CAV_BILLHISTDETL.
BILLEDDEMAND, CAV_MEMBERDETL.RATE, CAV_MBRHISTDETL.READDATE, CAV_MBRSEPMSTR.
ADDR1
HAVING (((CAV_MBRHISTDETL.BILLDATE)=[Enter BILLDATE (yymmdd)]) AND (
(CAV_MEMBERDETL.CYCLE)="0777"))
ORDER BY Last(CAV_BILLHISTDETL.MBRSEP);

 

[bolson7117 via AccessMonster.com]

900's SQL

SELECT Last(CAV_BILLHISTDETL.MBRSEP) AS LastOfMBRSEP, CAV_MBRSEPMSTR.NAME,
CAV_MBRHISTDETL.BILLDATE, CAV_MBRSEPMSTR.ADDR1, CAV_MBRSEPMSTR.ADDR2,
CAV_MBRSEPMSTR.ADDR3, Val([ZIP]) AS [ZIP#], CAV_MEMBERDETL.CYCLE,
[CAV_MBRHISTDETl.ENERGY]*0.01 AS ENERGY, [CAV_MBRHISTDETl.ARREARS]*0.01 AS
ARREARS, [CAV_MBRHISTDETl.CONSDEP]*0.01 AS CONSDEP, [CAV_MEMBERDETL.AMOUNT]*0.
01 AS AMOUNT, Val([METER]) AS [METER#], CAV_MBRHISTDETL.NBRDAYSSVC,
CAV_MBRHISTDETL.METERREAD, CAV_MBRHISTDETL.KWH, [CAV_MBRHISTDETl.PENALTY]*0.
01 AS PENALTY, [CAV_MBRHISTDETl.TAX]*0.01 AS TAX, [CAV_MBRHISTDETl.SLCHG]*0.
01 AS SLCHG, [CAV_MBRHISTDETl.MISCCHG]*0.01 AS MISCCHG, [CAV_MBRHISTDETl.
ROUNDUP]*0.01 AS ROUNDUP, CAV_BILLHISTDETL.DEMANDREAD, CAV_BILLHISTDETL.
DEMANDUSAGE, CAV_BILLHISTDETL.BILLEDDEMAND, CAV_MEMBERDETL.RATE,
CAV_MBRHISTDETL.READDATE
FROM CAV_BILLHISTDETL INNER JOIN (CAV_MBRSEPMSTR INNER JOIN (CAV_MEMBERDETL
INNER JOIN CAV_MBRHISTDETL ON CAV_MEMBERDETL.MBRSEP = CAV_MBRHISTDETL.MBRSEP)
ON CAV_MBRSEPMSTR.MBRSEP = CAV_MBRHISTDETL.MBRSEP) ON CAV_BILLHISTDETL.MBRSEP
= CAV_MBRSEPMSTR.MBRSEP
GROUP BY CAV_MBRSEPMSTR.NAME, CAV_MBRHISTDETL.BILLDATE, CAV_MBRSEPMSTR.ADDR1,
CAV_MBRSEPMSTR.ADDR2, CAV_MBRSEPMSTR.ADDR3, Val([ZIP]), CAV_MEMBERDETL.CYCLE,
[CAV_MBRHISTDETl.ENERGY]*0.01, [CAV_MBRHISTDETl.ARREARS]*0.01,
[CAV_MBRHISTDETl.CONSDEP]*0.01, [CAV_MEMBERDETL.AMOUNT]*0.01, Val([METER]),
CAV_MBRHISTDETL.NBRDAYSSVC, CAV_MBRHISTDETL.METERREAD, CAV_MBRHISTDETL.KWH,
[CAV_MBRHISTDETl.PENALTY]*0.01, [CAV_MBRHISTDETl.TAX]*0.01, [CAV_MBRHISTDETl.
SLCHG]*0.01, [CAV_MBRHISTDETl.MISCCHG]*0.01, [CAV_MBRHISTDETl.ROUNDUP]*0.01,
CAV_BILLHISTDETL.DEMANDREAD, CAV_BILLHISTDETL.DEMANDUSAGE, CAV_BILLHISTDETL.
BILLEDDEMAND, CAV_MEMBERDETL.RATE, CAV_MBRHISTDETL.READDATE
HAVING (((CAV_MBRHISTDETL.BILLDATE)="090205") AND ((CAV_MEMBERDETL.CYCLE)
="0777" Or (CAV_MEMBERDETL.CYCLE)="0900"))
ORDER BY Last(CAV_BILLHISTDETL.MBRSEP);

 

[bolson7117 via AccessMonster.com]

yea it would have been nice to originally have a ST field on the program but
its been like that for some time now. Lots of data entry errors from our
billing department. The reason i had 4 different forms was because i had 2
for each one that corrected the address mistake one that was right and one
that was wrong. I also have to have a text box that says "Finnal Bill" for
the 777's and "Pre Bill" for the 900s. I would need a way to tell the form
the its 777 and 900 plus the ability to correct the added ADDR3 situation.

thank you for your help