Printing address on a report based on location code

[Jarekb]

I'm not sure which forum to post this on since it overlaps several topics, but hopefully someone here can point me to the best solution to this problem.

Here's the situation. I've got two tables: empl_info and location. Within the info table lies a field called location option which notifies where a person gets sent their check. It can either be set to home, primary work location, other work location.

I need to print a letter and send it to the persons chosen location option. In a form I have a button that will first append the employee's ID to a process table, then print letters for employees meeting criteria, and finally update the process table with the date the letter was created. I've got all the SQL within the code for all inserts and updates.

I'll post the code I use below, but first here's what I'm asking. How or where would I add a check to see which location option is chosen and have that info show on the letter? I'm not sure if I can put it in the code before it's sent to the report (the report just has a query that shows all fields without any criteria) and I'm not sure how I would write the SQL with the If Then part in it.

The check would have to work something like this:

If Location_Option = 'J'
Letter's address field looks at location field in empl_info table
elseif Location_Option = 'O'
Letter's address field looks at Other_loc field in empl_info table
elseif Location_Option = 'H'
Letter's address fields are blank

The letter's address section has two parts to it. Top portion that contains unit information : location code, name, mail run. The bottom portion always contains the employees name and home address info. What I want the above code to do is modify the top portion that would either be filled with location information or be blank if home is the location option.


Here's all the SQL that I have in a function that's called by each button that needs it.

Select Case category
Case "Notice"
    If queryType = "Append" Then
        GetSQL = "INSERT INTO PROCESS ( EMPLID ) SELECT EMPLID FROM EMPL_INFO WHERE "
    ElseIf queryType = "Update" Then
        GetSQL = "UPDATE Results SET Results.DT_NOTICE_LETTER = Date() WHERE"
    End If
    
    If queryType = "Update" Or queryType = "Filter" Then
            GetSQL = GetSQL + "[DT_REQUEST] is null and [DT_Notice_Letter] is null and "
    End If
    GetSQL = GetSQL + "([HIRE_DT] > #5/31/2006# and [EFF_STATUS] = 'I'"
    GetSQL = GetSQL + " and (DateDiff('d',[HIRE_DT],Date()))>30) "

And here is how the button works

Private Sub NoticeLetter_Click()
    Dim stDocName As String
    Select Case MsgBox("Print Notice Letters?", vbYesNo)
    Case vbYes
        CurrentDb.Execute GetSQL("Notice", "Append")
        stDocName = "NOTICE_LETTER_REPORT"
        DoCmd.OpenReport stDocName, acPreview, , GetSQL("Notice", "Filter")
           
        CurrentDb.Execute GetSQL("Notice", "Update")
        MsgBox ("Noitice Letters Successfuly Printed")
    End Select
End Sub

Thanks for any help.

 

[Remou]

It should be possible to use DlookUp. There are only two options, 'J' and 'O', as 'H' is left blank. Therefore, the control source of a textbox can be set to:

[tt]=IIf(Location_Option = 'J',DLookUp("location","empl_info","emplid='" & [emplid] & "'"),IIf(Location_Option = 'O',DLookUp("other_loc","empl_info","emplid='" & [emplid] & "'"),""))[/tt]

If emplid is a text field, and to:

[tt]=IIf(Location_Option = 'J',DLookUp("location","empl_info","emplid=" & [emplid]),IIf(Location_Option = 'O',DLookUp("other_loc","empl_info","emplid=" & [emplid]),""))[/tt]

If emplid is a number field.

 

[Jarekb]

I'm having a problem making that work. My report looks at three tables, one to get employee information, another to get location information, and the last to get status information. If I use the code as is, I get an error saying it doesn't know which emplid to look for. So I tried to change your code to empl_info!emplid and it didn't work. All I get is a msg box asking for an input. I tried a period instead of a "!", I tried putting brackets around each part, but still the same problem.

 

[Jarekb]

Forgot to add that if I use the code directly in a query designer it works fine, but when I add it to the control source the brackets are dropped from the code.

 

[Remou]

What you need is, say:

... ","emplid=" & [empl_info.emplid] ...

 

[Jarekb]

Thank you very much, works perfectly.