I need Help with Case Statement (1 Viewer)

[humphreya]

I have a database that I track employees. I have a table that has their personal information – Name, address, ect. I have a table that stores their computer access information – Access A, Access B, date trained, training due date ect.

I have created a form for the supervisor to nominate an individual for accesses and a report that goes to the IT department for approval.

The Form lets the user choose the access and then print the report for signature. The problem I have to create a different report for each type of access and dates for the training – rpt1=Access A with training Due, rpt2=Access A with out training due, rpt3=Access B with training due, and rpt4=Access B with out training Due.
I have combined the fields in the report query to provide ease of reading. The problem with this is if there is no training due, the separators still print on the report and it will confuse the user

With all field populated:
Type of Access: Access A; Training for A, Date – Training due date

With some Fields not populated:
Type of Access: Access A; Training for A, [blank field] – [blankfield]

I have attached a flow chart for the command button and need to write the case statement for this is there any help?

 

[TedMartin]

Not sure if this the answer you are seeking but maybe create a variable and give it a value for each version of the report. Then call the report by giving the variable a value and then use that to determine which of the Select Case options is chosen so that the appropriate report is opened.

If this doesn't help - then you need to explain the problem more clearly.

 

[humphreya]

Unsure how to write the code

I do not know how to even start the code

 

[modest]

You can do this many ways. You can manipulate the report's recordsource to be bound to a new query.

You can create a report that covers all posibilities and just filter down which "form" to show

You could pass values through the OpenArgs

Also you can make fields "invisible" when the report opens, and you can change their settings to auto-shrink or auto-expand. It's kind of hard to help you further when we do not know what the differences are in the forms. This may be a case where you do not even need many different types of forms, just better filtering of data.

 

[modest]

You can do this many ways. You can manipulate the report's recordsource to be bound to a new query.

You can create a report that covers all posibilities and just filter down which "form" to show

You could pass values through the OpenArgs

Also you can make fields "invisible" when the report opens, and you can change their settings to auto-shrink or auto-expand. It's kind of hard to help you further when we do not know what the differences are in the forms. This may be a case where you do not even need many different types of forms, just better filtering of data.

 

[Pat Hartman]

I have combined the fields in the report query to provide ease of reading. The problem with this is if there is no training due, the separators still print on the report and it will confuse the user

examine the use of the + as a concatenation character. It may solve your problem. Be sure to read the help entries for both the + and & characters so you understand when to use which.

BTW to answer your question, you need to nest the Case statement since you have two variables involved:

Select Case var1
   Case Is Null
      Select Case var2
         Case Is Null
            "both null"
         Case Else
             "var1 null, var2 not null"
         End Select
    Case Else
      Select Case var2
         Case Is Null
            "var1 not null, var2 null"
         Case Else
             "var1 not null, var2 not null"
         End Select
End Select