step through table and select data for reporting values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Looking for some help, I have a report with an unlimited amount or rows. Each
row has 9 text boxes. I need to somehow write a code or expression that will
do the following for each of the nine text boxes found on a report line.

textbox 1
Look at the corresponding table(datesandstatus) where the text boxes will
get their values(by record)...Look at field A from table datesandstatus and
determine if the value is "x","y" or "z" and place that value in the text
box. If none of these values are present, Look at Field C from table
datesandstatus and list it's date in text1 if it is not blank. If field C
is blank, then the value of the text box on the report is field B. Should
this field be blank "PENDING" should be placed.

A = status, B= Date1(projected) C=Date2(revised)

This process has to happen for each of the 9 textboxes in a record for an
unlimited amount of rows on the report until EOF.

ABC1 correspond to one textbox, the next would be ABC2,ABC3, etc. Three
fields are compared for each textbox until a value is picked. All 27 fields
are in one table datesandstatus. A job has 27 date/staus fields. The report
will gather this information based off the table’s primary key field which
relates to the main
table’s key(populates additional textboxes on report line).

Not sure how to start here... create expressions for each text box or can
this somehow be coded and stepped in VB? I've come a long way, but I'm still
a rookie when it comes to vB and access so any help or direction that can be
provided would be greatly appreciated.
 
Dave said:
Looking for some help, I have a report with an unlimited amount or rows. Each
row has 9 text boxes. I need to somehow write a code or expression that will
do the following for each of the nine text boxes found on a report line.

textbox 1
Look at the corresponding table(datesandstatus) where the text boxes will
get their values(by record)...Look at field A from table datesandstatus and
determine if the value is "x","y" or "z" and place that value in the text
box. If none of these values are present, Look at Field C from table
datesandstatus and list it's date in text1 if it is not blank. If field C
is blank, then the value of the text box on the report is field B. Should
this field be blank "PENDING" should be placed.

A = status, B= Date1(projected) C=Date2(revised)

This process has to happen for each of the 9 textboxes in a record for an
unlimited amount of rows on the report until EOF.

ABC1 correspond to one textbox, the next would be ABC2,ABC3, etc. Three
fields are compared for each textbox until a value is picked. All 27 fields
are in one table datesandstatus. A job has 27 date/staus fields. The report
will gather this information based off the table’s primary key field which
relates to the main
table’s key(populates additional textboxes on report line).

Not sure how to start here... create expressions for each text box or can
this somehow be coded and stepped in VB? I've come a long way, but I'm still
a rookie when it comes to vB and access so any help or direction that can be
provided would be greatly appreciated.
Click to expand...

If I understand, you have a "Jobs" table and a table "datesandstatus" in a 1:1
relationship.

The "datesandstatus" table has 27 "status/date" fields plus a primary key
(autonumber) field and a field that is a foreign key to the "Jobs" table.


If this is right, here is one way that might work:

1) Create a new query.
2) Add the table "datesandstatus".
3) Add the Primary key field and the "Jobs" table foreign key to the grid.

4) Add nine expressions to the grid, changing the field names as necessary. The
first three expressions should look like: (watch for line wrap)

ABC1: IIf([fieldA]="x" Or [fieldA]="y" Or [fieldA]="z",[fieldA],IIf(Not
IsNull([fieldC]) Or Not [fieldC]="",[fieldC],IIf(Not IsNull([fieldB]) Or Not
[fieldB]="",[fieldB],"Pending")))

ABC2: IIf([fieldD]="x" Or [fieldD]="y" Or [fieldD]="z",[fieldD],IIf(Not
IsNull([fieldF]) Or Not [fieldF]="",[fieldF],IIf(Not IsNull([fieldE]) Or Not
[fieldE]="",[fieldE],"Pending")))

ABC3: IIf([fieldG]="x" Or [fieldG]="y" Or [fieldG]="z",[fieldG],IIf(Not
IsNull([fieldI]) Or Not [fieldI]="",[fieldI],IIf(Not IsNull([fieldH]) Or Not
[fieldH]="",[fieldH],"Pending")))

..
..
..

ABC9: IIF......

5) Save the query as "qryDateStatus"

6) Now create another query and add the "Jobs" table and the query "qryDateStatus".

7) Create a link between the "Jobs" primary key and "qryDateStatus" foreign key
to "Jobs" table.

8) Add whatever fields you need from the "Jobs" table and the nine calculation
fields from "qryDateStatus".

9) Save this query as "qryJobsDateStatus" (or whatever you want).


Use this query as the record source for your report.

HTH
 
Steve,

I greatly appreciate the help. This should get me in the direction I need to
go.

Thanks again!

SteveS said:
Dave said:
Looking for some help, I have a report with an unlimited amount or rows. Each
row has 9 text boxes. I need to somehow write a code or expression that will
do the following for each of the nine text boxes found on a report line.

textbox 1
Look at the corresponding table(datesandstatus) where the text boxes will
get their values(by record)...Look at field A from table datesandstatus and
determine if the value is "x","y" or "z" and place that value in the text
box. If none of these values are present, Look at Field C from table
datesandstatus and list it's date in text1 if it is not blank. If field C
is blank, then the value of the text box on the report is field B. Should
this field be blank "PENDING" should be placed.

A = status, B= Date1(projected) C=Date2(revised)

This process has to happen for each of the 9 textboxes in a record for an
unlimited amount of rows on the report until EOF.

ABC1 correspond to one textbox, the next would be ABC2,ABC3, etc. Three
fields are compared for each textbox until a value is picked. All 27 fields
are in one table datesandstatus. A job has 27 date/staus fields. The report
will gather this information based off the table’s primary key field which
relates to the main
table’s key(populates additional textboxes on report line).

Not sure how to start here... create expressions for each text box or can
this somehow be coded and stepped in VB? I've come a long way, but I'm still
a rookie when it comes to vB and access so any help or direction that can be
provided would be greatly appreciated.
Click to expand...

If I understand, you have a "Jobs" table and a table "datesandstatus" in a 1:1
relationship.

The "datesandstatus" table has 27 "status/date" fields plus a primary key
(autonumber) field and a field that is a foreign key to the "Jobs" table.


If this is right, here is one way that might work:

1) Create a new query.
2) Add the table "datesandstatus".
3) Add the Primary key field and the "Jobs" table foreign key to the grid.

4) Add nine expressions to the grid, changing the field names as necessary. The
first three expressions should look like: (watch for line wrap)

ABC1: IIf([fieldA]="x" Or [fieldA]="y" Or [fieldA]="z",[fieldA],IIf(Not
IsNull([fieldC]) Or Not [fieldC]="",[fieldC],IIf(Not IsNull([fieldB]) Or Not
[fieldB]="",[fieldB],"Pending")))

ABC2: IIf([fieldD]="x" Or [fieldD]="y" Or [fieldD]="z",[fieldD],IIf(Not
IsNull([fieldF]) Or Not [fieldF]="",[fieldF],IIf(Not IsNull([fieldE]) Or Not
[fieldE]="",[fieldE],"Pending")))

ABC3: IIf([fieldG]="x" Or [fieldG]="y" Or [fieldG]="z",[fieldG],IIf(Not
IsNull([fieldI]) Or Not [fieldI]="",[fieldI],IIf(Not IsNull([fieldH]) Or Not
[fieldH]="",[fieldH],"Pending")))

..
..
..

ABC9: IIF......

5) Save the query as "qryDateStatus"

6) Now create another query and add the "Jobs" table and the query "qryDateStatus".

7) Create a link between the "Jobs" primary key and "qryDateStatus" foreign key
to "Jobs" table.

8) Add whatever fields you need from the "Jobs" table and the nine calculation
fields from "qryDateStatus".

9) Save this query as "qryJobsDateStatus" (or whatever you want).


Use this query as the record source for your report.

HTH
Click to expand...
 
Steve,

This method works great with the exception of the date formating. How would
I set the dates to "mmdd" vs the output the query is currenly giving me
05/04/2005? I set the text box format field to mmdd however this isn't
working. I've tried to include the format in the expression itself but this
is gving me errors.
I need to get all the dates to display in the four character format. Thanks
in advance.

format [FieldA],"mmdd"


SteveS said:
Dave said:
Looking for some help, I have a report with an unlimited amount or rows. Each
row has 9 text boxes. I need to somehow write a code or expression that will
do the following for each of the nine text boxes found on a report line.

textbox 1
Look at the corresponding table(datesandstatus) where the text boxes will
get their values(by record)...Look at field A from table datesandstatus and
determine if the value is "x","y" or "z" and place that value in the text
box. If none of these values are present, Look at Field C from table
datesandstatus and list it's date in text1 if it is not blank. If field C
is blank, then the value of the text box on the report is field B. Should
this field be blank "PENDING" should be placed.

A = status, B= Date1(projected) C=Date2(revised)

This process has to happen for each of the 9 textboxes in a record for an
unlimited amount of rows on the report until EOF.

ABC1 correspond to one textbox, the next would be ABC2,ABC3, etc. Three
fields are compared for each textbox until a value is picked. All 27 fields
are in one table datesandstatus. A job has 27 date/staus fields. The report
will gather this information based off the table’s primary key field which
relates to the main
table’s key(populates additional textboxes on report line).

Not sure how to start here... create expressions for each text box or can
this somehow be coded and stepped in VB? I've come a long way, but I'm still
a rookie when it comes to vB and access so any help or direction that can be
provided would be greatly appreciated.
Click to expand...

If I understand, you have a "Jobs" table and a table "datesandstatus" in a 1:1
relationship.

The "datesandstatus" table has 27 "status/date" fields plus a primary key
(autonumber) field and a field that is a foreign key to the "Jobs" table.


If this is right, here is one way that might work:

1) Create a new query.
2) Add the table "datesandstatus".
3) Add the Primary key field and the "Jobs" table foreign key to the grid.

4) Add nine expressions to the grid, changing the field names as necessary. The
first three expressions should look like: (watch for line wrap)

ABC1: IIf([fieldA]="x" Or [fieldA]="y" Or [fieldA]="z",[fieldA],IIf(Not
IsNull([fieldC]) Or Not [fieldC]="",[fieldC],IIf(Not IsNull([fieldB]) Or Not
[fieldB]="",[fieldB],"Pending")))

ABC2: IIf([fieldD]="x" Or [fieldD]="y" Or [fieldD]="z",[fieldD],IIf(Not
IsNull([fieldF]) Or Not [fieldF]="",[fieldF],IIf(Not IsNull([fieldE]) Or Not
[fieldE]="",[fieldE],"Pending")))

ABC3: IIf([fieldG]="x" Or [fieldG]="y" Or [fieldG]="z",[fieldG],IIf(Not
IsNull([fieldI]) Or Not [fieldI]="",[fieldI],IIf(Not IsNull([fieldH]) Or Not
[fieldH]="",[fieldH],"Pending")))

..
..
..

ABC9: IIF......

5) Save the query as "qryDateStatus"

6) Now create another query and add the "Jobs" table and the query "qryDateStatus".

7) Create a link between the "Jobs" primary key and "qryDateStatus" foreign key
to "Jobs" table.

8) Add whatever fields you need from the "Jobs" table and the nine calculation
fields from "qryDateStatus".

9) Save this query as "qryJobsDateStatus" (or whatever you want).


Use this query as the record source for your report.

HTH
Click to expand...
 
Dave said:
Steve,

This method works great with the exception of the date formating. How would
I set the dates to "mmdd" vs the output the query is currenly giving me
05/04/2005? I set the text box format field to mmdd however this isn't
working. I've tried to include the format in the expression itself but this
is gving me errors.
I need to get all the dates to display in the four character format. Thanks
in advance.

format [FieldA],"mmdd"
Click to expand...

You said: A = status, B= Date1(projected) C=Date2(revised)

If "fieldB" and "fieldC" are Date types, try the following:

ABC1: IIf([fieldA]="x" Or [fieldA]="y" Or [fieldA]="z",[fieldA],IIf(Not
IsNull([fieldC]) Or Not [fieldC]="",Format([fieldC],"mm/yy"),IIf(Not
IsNull([fieldB]) Or Not [fieldB]="",Format([fieldB],"mm/yy"),"Pending")))


Of course, you will have to change the other 8 formulas.

If "fieldB" or "fieldC" can be dates or text, let me know.
 
Steve, sorry should have been clearer. Field B and C can be either text or
dates. I'll try the suggestion below and see what happens. When populated as
dates the fields need to be in four character format. I really appreciate
your help.

Thanks again, Dave

SteveS said:
Dave said:
Steve,

This method works great with the exception of the date formating. How would
I set the dates to "mmdd" vs the output the query is currenly giving me
05/04/2005? I set the text box format field to mmdd however this isn't
working. I've tried to include the format in the expression itself but this
is gving me errors.
I need to get all the dates to display in the four character format. Thanks
in advance.

format [FieldA],"mmdd"
Click to expand...

You said: A = status, B= Date1(projected) C=Date2(revised)

If "fieldB" and "fieldC" are Date types, try the following:

ABC1: IIf([fieldA]="x" Or [fieldA]="y" Or [fieldA]="z",[fieldA],IIf(Not
IsNull([fieldC]) Or Not [fieldC]="",Format([fieldC],"mm/yy"),IIf(Not
IsNull([fieldB]) Or Not [fieldB]="",Format([fieldB],"mm/yy"),"Pending")))


Of course, you will have to change the other 8 formulas.

If "fieldB" or "fieldC" can be dates or text, let me know.
Click to expand...
 
The change appears to work with both text and dates. When a staus is present
it displays it correctly as well as if it is a date(in the four character
format). I should be good to go! Many thanks to you. This site and it's
people are an invaluable resource for us newbies!!

SteveS said:
Dave said:
Steve,

This method works great with the exception of the date formating. How would
I set the dates to "mmdd" vs the output the query is currenly giving me
05/04/2005? I set the text box format field to mmdd however this isn't
working. I've tried to include the format in the expression itself but this
is gving me errors.
I need to get all the dates to display in the four character format. Thanks
in advance.

format [FieldA],"mmdd"
Click to expand...

You said: A = status, B= Date1(projected) C=Date2(revised)

If "fieldB" and "fieldC" are Date types, try the following:

ABC1: IIf([fieldA]="x" Or [fieldA]="y" Or [fieldA]="z",[fieldA],IIf(Not
IsNull([fieldC]) Or Not [fieldC]="",Format([fieldC],"mm/yy"),IIf(Not
IsNull([fieldB]) Or Not [fieldB]="",Format([fieldB],"mm/yy"),"Pending")))


Of course, you will have to change the other 8 formulas.

If "fieldB" or "fieldC" can be dates or text, let me know.
Click to expand...
 
Back
Top