Help with generating report for table with many fields

[mrbboy]

I have a table that stores analyst names, instrument names and calibration data, tblCalibration. The instrument names and calibration data are all text fields. The calibration data are actually weight measurements, i.e. 0.2g, 1g, 5g, 10g, 150g and 200g. Each instrument can use any combination of the weights. I created a form that allows me specify search criteria by isntrument, analyst names and dates. The table fields are:

tblCalibration

Cal ID
Analyst
Instrument
0.2g
1g
5g
10g
150g
200g

I placed this code on the On Load event of frmMenu.

Dim stDocName As String
stDocName = "2006 daily calibration"

Dim Whereclause As String

If Not IsNull(cboEmployee) Then
Whereclause = "[Employee Name] = '" & cboEmployee & "'"

If Not IsNull(cboInstrument) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Instrument] = '" & cboInstrument & "'"
End If

If Not IsNull(cboSingleDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Date] = #" & Format(cboSingleDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cboStartDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Date] >= #" & Format(cboStartDate, "mm/dd/yyyy") & "#"
End If

If Not IsNull(cboEndDate) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Date] <= #" & Format(cboEndDate, "mm/dd/yyyy") & "#"
End If

DoCmd.Close
DoCmd.OpenReport stDocName, acPreview, , Whereclause

WHen I choose to run an instrument report, I get all the weights in the table, even the ones that are null. How do I do this so only the weight fields that are not null will be shown? Keep in mind that each instrument does not use the same weights.

THanks.

 

[PHV]

I think you really have to normalize your DB:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

Have you confirmed your Whereclause string is really being built? Perhaps all or some controls are being evaluated as Null?

By the way, "Date" is usually not a good idea for a field name, even with the square brackets around it.