how to query multiple fields

[mrbboy]

I have a form that has four fields, cboName, cboCompany, cboDepartment and cboDate.I built a query that queried all these fields. The query qorks fine if all the fields have something in it. However, if any of the fields is empty, the query bombs. Can someone help here?

 

[mrbboy]

Here is the actual sql for this. I need to be able to query all the fields or any combination of the fields.

SELECT Log.Date, Log.Department, Log.Name, Log.[Outside Service], Log.[Service Engineer], Log.Company, Log.Instrument, Log.[Channel A], Log.[Channel B], Log.Trap, Log.Septa, Log.Liner, Log.[Column Clip], Log.Filament, Log.[Ion Trap Cleaning], Log.[Transfer Line], Log.[Y Connector], Log.[Guard Column], Log.[Guard Column Serial Number], Log.[Guard Column Length m], Log.[Analytical Column], Log.[Column Serial Number], Log.[Length m], Log.[Inside Diameter mm], Log.[Film Thickness um], Log.[Syringe and/or Needle], Log.[Replace ECD], Log.[ECD Serial Number], Log.Prosep, Log.[Prosep ID], Log.[Prosep Cool Valve], Log.[Prosep Split Valve], Log.FID, Log.[Replace TurboVap Sensors], Log.[TV Sensor 1], Log.[TV Sensor 2], Log.[TV Sensor 3], Log.[TV Sensor 4], Log.[TV Sensor 5], Log.[TV Sensor 6], Log.[TV Sensor 7], Log.Other
FROM Log
WHERE (((Log.Date)=[Forms]![test]![date]) AND ((Log.Name)=[Forms]![test]![name]) AND ((Log.Company)=[Forms]![test]![company]) AND ((Log.department)=[Forms]![test]![department]));

Thanks for any assistance.

 

[PHV]

You may try this:
SELECT ...
FROM Log
WHERE (Log.Date=[Forms]![test]![date] OR [Forms]![test]![date] Is Null)
AND (Log.Name=[Forms]![test]![name] OR [Forms]![test]![name] Is Null)
AND (Log.Company=[Forms]![test]![company] OR [Forms]![test]![company] Is Null)
AND (Log.department=[Forms]![test]![department] OR [Forms]![test]![department] Is Null);

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

 

[PaulF]

Or something along this line

dim strWhere as String
strWhere = ""

If Len(Trim(Nz(cboName,"")))>0 then
strWhere = "[Test].[Name] = '" & cboName & "'"
End If
If Len(Trim(Nz(cboCompany,"")))>0 then
If strWhere <> "" Then
strWhere = "[Test].[Company] = '" & cboCompany & "'"
Else
strWhere = strWhere & " And [Test].[Company] = '" & cboCompany & "'"
End If
End If
If Len(Trim(Nz(cboDepartment,"")))>0 then
strWhere = "[Test].[Department] = '" & cboDepartment & "'"
Else
strWhere = strWhere & " And [Test].[Department] = '" & cboDepartment & "'"
End If
End If
If Len(Trim(Nz(cboDate,"")))>0 then
If strWhere <> "" Then
strWhere = "[Test].[Date] = #" & cboDate & "#"
Else
strWhere = strWhere & " And [Test].[Date] = #" & cboDate & "#"
End if
End If


SELECT ...
FROM Log
WHERE & strWhere


PaulF

 

[mrbboy]

PHV,

I changed the sql using your suggestion and it works even if a field is null. However, I now get all the entries in the table. Any suggestions?

Dim sql As String

sql = "SELECT [Log].Date, [Log].Department, [Log].Name, Log.[Outside Service], [Log].[Service Engineer], [Log].Company, [Log].Instrument, [Log].[Channel A], [Log].[Channel B], [Log].Trap, [Log].Septa, [Log].Liner, [Log].[Column Clip], [Log].Filament, [Log].[Ion Trap Cleaning], [Log].[Transfer Line], [Log].[Y Connector], [Log].[Guard Column], [Log].[Guard Column Serial Number], [Log].[Guard Column Length m], [Log].[Analytical Column], [Log].[Column Serial Number], [Log].[Length m], [Log].[Inside Diameter mm], [Log].[Film Thickness um], [Log].[Syringe and/or Needle], [Log].[Replace ECD], [Log].[ECD Serial Number], [Log].Prosep, [Log].[Prosep ID], [Log].[Prosep Cool Valve], [Log].[Prosep Split Valve], Log.FID, [Log].[Replace TurboVap Sensors], [Log].[TV Sensor 1], [Log].[TV Sensor 2], [Log].[TV Sensor 3], [Log].[TV Sensor 4], [Log].[TV Sensor 5], [Log].[TV Sensor 6], [Log].[TV Sensor 7], [Log].Other"
sql = "sql & FROM [Log]"
sql = "sql & where (Log.Date = [Forms]![test]![StartDate] Or [Forms]![test]![StartDate] Is Null) And (Log.Name = [Forms]![test]![Employee] Or [Forms]![test]![employee] Is Null) And (Log.Company = [Forms]![test]![Company] Or [Forms]![test]![Company] Is Null)"

 

[mrbboy]

PaulF,

I tried your code as well and it also returns all the records in the Table, Log. I placed the code in the On Load event of form frmLogReport.