report based on critera

[dan231]

I am trying to get a report for copying and pasting text into a different program. The data is all in a query already and I need it filtered based on class name. I also need a very specific format:

<firstname> <lastname>@<companyname>@1<faxnumber>; [repeat]

Is there a way to do this and just get a display on the same report/form page? At most there would be less than 50 records.

I am at a lost of where to even begin.

Any help is appreciated.

 

[Dennisk]

Firstly you would include the criteria in you query although you do not need to show it.

As for you format, again this can be done using concatenation in the query.

i.e. in the control grid of the QBE you could enter:-

FullName; "<" & [firstname] & "><" & [Lastname] & >@<" etc

(I assume the angle brackets are part of the output)

 

[dan231]

The query as it stands now has all of our class data in it (1900+ records). I would need a user selection to filter for a specific class: eg: LA-196-1001 (38 records)

What is the QBE?

EDIT:

I have the expression working! Thank you.

so basically I need the user selection entry. What I have now is a combo box search, but each student has the class listed in thier record. I can't get it to show all records for class name "xxx". I am getting closer though.

 

[dan231]

Not sure if I'm on the right track here, but I have a form that only has a combobox for searching the classname. Then a subform to show the expression.

I have 2 problems:
(1) the combobox shows 1 class name for each record. The record is a student record, so for each student in that class I get the same class listed. Is there a way to limit to no duplicates?

(2) I can only get the subform to list 1 student in that class and it lists that person more than once?!?!?

Should I not be doing this off a form?

 

[dan231]

I changed this to a report and have the following code in my combobox:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String

'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![ClassFaxForm]![ComboClass]) Then
strWhere = "([ClassName] = [Forms]![ClassFaxForm]![ComboClass]) And "
End If


'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5

'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "ClassName_Fax"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End If

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

Edited: I now only get a blank report.

 

[dan231]

I managed to work this out.
Created it in a report and had to tweak my qry.

 

[Dennisk]

QBE - Query By Example