IF Statements... Where to Begin?

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

Guest

Hello all.

I am currently working on a project and am stuck. I know how to do it in
Excel, but now wish to do it in access, and there's trouble.

I want to continually (once per day at XX:XX time) check our records for
those that match certain criteria, those being that the date is older than 14
days AND that a field value is "Awaiting Pickup". Now, in Excel I can turn
the row red, make it stand out. I wish to do the same in Access on Reports,
and print notices.

I don't know the best way of attacking this, but IF() statements seem to be
it. So here's my questions:

 What's the best way to attack this? IF() statements, Switch() statements,
Macros what?
 Where would I code such functions?
 Is it possible to create one function to check for criteria, generate a
report, print notices, and print the report?

Thanks for the help. I know it's not good to post multiple questions in the
same post, but they're all along the same line.

~Brett Patterson
 
I want to continually (once per day at XX:XX time) check our records for
those that match certain criteria, those being that the date is older than 14
days AND that a field value is "Awaiting Pickup". Now, in Excel I can turn
the row red, make it stand out. I wish to do the same in Access on Reports,
and print notices.

I don't know the best way of attacking this, but IF() statements seem to be
it. So here's my questions:
Click to expand...

Access and Excel are VERY DIFFERENT PROGRAMS. Assuming that techniques
that are routine in Excel will work the same in Access, or vice versa,
is just going to get you in hot water!

Instead, create a Form (for onscreen use) or Report (for printing)
based on the table. Select the textboxes or other controls that you
want to highlight, in design view. On the Menu select Format...
Conditional Formatting.

You can then specify a criterion indicating which records to
highlight; e.g. use Expression and an expression like

DateDiff("d", [datefield], Date()) > 14 AND [otherfield] = "Awaiting
Pickup")

and specify bold, color, or whatever technique you like for records
which match that criterion.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Assuming Access 2000 or later, conditional formatting would be the easiest
approach.

Open the report in design view.
Add a text box the size of the detail section, and Send To Back (Format
menu).
Choose Conditional Formatting on the Format menu, and set it to:
Expression Is... [SomeDateTimeField] < Now()
or some such expression that meets the condition you have in mind.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Ok, thanks for the help with the formatting.

Any idea as to about the continuous checking & creation of Reports?

Pointing in the right direction of tutorials would be great. Thanks.

~Brett

Allen Browne said:
Assuming Access 2000 or later, conditional formatting would be the easiest
approach.

Open the report in design view.
Add a text box the size of the detail section, and Send To Back (Format
menu).
Choose Conditional Formatting on the Format menu, and set it to:
Expression Is... [SomeDateTimeField] < Now()
or some such expression that meets the condition you have in mind.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hello all.

I am currently working on a project and am stuck. I know how to do it in
Excel, but now wish to do it in access, and there's trouble.

I want to continually (once per day at XX:XX time) check our records for
those that match certain criteria, those being that the date is older than
14
days AND that a field value is "Awaiting Pickup". Now, in Excel I can
turn
the row red, make it stand out. I wish to do the same in Access on
Reports,
and print notices.

I don't know the best way of attacking this, but IF() statements seem to
be
it. So here's my questions:

 What's the best way to attack this? IF() statements, Switch()
statements,
Macros what?
 Where would I code such functions?
 Is it possible to create one function to check for criteria, generate a
report, print notices, and print the report?

Thanks for the help. I know it's not good to post multiple questions in
the
same post, but they're all along the same line.

~Brett Patterson
Click to expand...
Click to expand...
 
You can use the Timer event of a (hidden?) form to perform a task at a
regular interval.

Presumably you only want to open the report if there is something to
display, so a DLookup() to see if there is a matching value might be useful.
Details on how to use that:
http://members.iinet.net.au/~allenbrowne/casu-07.html

If something is returned, use OpenReport to display the information.
OpenReport with acViewPreview shows it on screen, or with acViewNormal sends
it to the printer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Ok, thanks for the help with the formatting.

Any idea as to about the continuous checking & creation of Reports?

Pointing in the right direction of tutorials would be great. Thanks.

~Brett

Allen Browne said:
Assuming Access 2000 or later, conditional formatting would be the
easiest
approach.

Open the report in design view.
Add a text box the size of the detail section, and Send To Back (Format
menu).
Choose Conditional Formatting on the Format menu, and set it to:
Expression Is... [SomeDateTimeField] < Now()
or some such expression that meets the condition you have in mind.


message
Hello all.

I am currently working on a project and am stuck. I know how to do it
in
Excel, but now wish to do it in access, and there's trouble.

I want to continually (once per day at XX:XX time) check our records
for
those that match certain criteria, those being that the date is older
than
14
days AND that a field value is "Awaiting Pickup". Now, in Excel I can
turn
the row red, make it stand out. I wish to do the same in Access on
Reports,
and print notices.

I don't know the best way of attacking this, but IF() statements seem
to
be
it. So here's my questions:

What's the best way to attack this? IF() statements, Switch()
statements,
Macros what?
Where would I code such functions?
Is it possible to create one function to check for criteria, generate
a
report, print notices, and print the report?

Thanks for the help. I know it's not good to post multiple questions
in
the
same post, but they're all along the same line.

~Brett Patterson
Click to expand...
Click to expand...
Click to expand...
 
Back
Top