Query Functions

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I am an avid Excel user and I know my way around Excel
functions. I've used Access before, but on a very basic
level. I need to create some reports, and thought that
Access would better handle my needs.

My problem is that I'm not sure how to get Access Queries
to do what I'd like them to do.

For instance.

Column A; which is titled Project Number (PRJ-0001, etc.)
Column B; which is titled Prospective Project Number (PPN-
0001, etc.)
Column C; which is titled Project Status (Cancel, Proceed)

Projects retain their Prospective Project Number, so
Column B should always be full and Column A would be
filled once a Prospective Project is approved to be a
Project.

I need a list of Current Projects and Current Prospective
Projects on one report.

In Excel, I'd use this function:
=IF(C2<>"Cancel",(IF(A2<>"",A2,B2)),"")

This would be the Results:

Column A Column B Column C Result
Blank PPN-0001 Cancel Blank
PRJ-0001 PPN-0002 Proceed PRJ-0001
Blank PPN-0003 Proceed PPN-0003
PRJ-0003 PPN-0004 Cancel Blank
PRJ-0002 PPN-0005 Proceed PRJ-0002

The Access Report should then list the non-blank items
from the Results column along with the Project Titles and
other information for the particular record.

How could you use this in an Access Query? If I have
other functions, how would they be added to an Access
Query? Is there a tutorial somewhere that might explain
this?

Thank you,

Jennifer
 
Hi,


SELECT Nz(ColumnA, ColumnB)
FROM myTable
WHERE ColumnC <> 'Cancel'




I assume ColumnA can be NULL (not blank ( one character, the space)
, not empty string (no character) ) and if so, then we take ColumnB, else,
we pick what is in ColumnA. That is what Nz( arg1, arg2) does: it takes
arg1, unless arg1 is null, then it takes arg2.





Hoping it may help,
Vanderghast, Access MVP
 
Thank you Michel,

It worked great. I just have one other question. Column
A and Column B both have Input Masks ("PRJ-"0000 and "PPN-
"0000). Is there a way to have them work as well? Or
should I just get rid of the masks altogether?

Jennifer
 
HI,


I never work with mask myself, but there is an option, I think, that
allows you to store, or NOT, the mask in the "data" (at least, the input
mask wizard ask a question about it if the field data type is Text). So, if
possible, don't store the mask... within the data.

Vanderghast, Access MVP
 
Back
Top