Search fields...

[Guest]

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.
However, this requires all fields to be filled in to return any records. How
can the query parameters be blank for all empty fields on the form? For
example, if all fields are blank, all records are shown.

Currently, my query parameters are of the form:

[Forms]![FormName]![UnBoundControlName]

Thanks

Dave

 

[Guest]

I think what you are looking for is, in the criteria of the query

Where FieldName = [Forms]![FormName]![UnBoundControlName] Or
[Forms]![FormName]![UnBoundControlName] Is Null

 

[Guest]

Dave,
i think this is what you need,

Like [Forms]![FormName]![UnBoundControlName] & "*" Is Null

 

[Guest]

Good effort but not what I was looking for.

Ofer, your suggestion seems to still require all search fields to be
entered, but instead removes the "found" item from the record set. (I'm
pretty sure that's what happened, will double check)

What I need is for the query to return all records when the textbox is
blank. For example, I have two search fields, employeeID and equipmentID.
Required results are:

If both fields are empty all records are returned.
If employeeID = x and equipmentID is empty, all records with employeeID = x
are returned.
If employeeID is empty and equipmentID = x, all records with equipmentID are
returned.
If employeeID = x and equipmentID = y, all records with employeeID = x AND
equipmentID = y are returned.

Any more ideas?

Thanks

Dave

Dave,
i think this is what you need,

Like [Forms]![FormName]![UnBoundControlName] & "*" Is Null

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.
However, this requires all fields to be filled in to return any records. How
can the query parameters be blank for all empty fields on the form? For
example, if all fields are blank, all records are shown.

Currently, my query parameters are of the form:

[Forms]![FormName]![UnBoundControlName]

Thanks

Dave

 

[Guest]

It should work, and it should look like

Where (employeeID = X Or X Is Null) and (equipmentID = Y Or Y Is Null)

Or, try

Where employeeID Like IIf(X Is Null, "*", X) and equipmentID Like IIf(Y Is
Null, "*",Y)


--
\\// Live Long and Prosper \\//
BS"D

Good effort but not what I was looking for.

Ofer, your suggestion seems to still require all search fields to be
entered, but instead removes the "found" item from the record set. (I'm
pretty sure that's what happened, will double check)

What I need is for the query to return all records when the textbox is
blank. For example, I have two search fields, employeeID and equipmentID.
Required results are:

If both fields are empty all records are returned.
If employeeID = x and equipmentID is empty, all records with employeeID = x
are returned.
If employeeID is empty and equipmentID = x, all records with equipmentID are
returned.
If employeeID = x and equipmentID = y, all records with employeeID = x AND
equipmentID = y are returned.

Any more ideas?

Thanks

Dave

Dave,
i think this is what you need,

Like [Forms]![FormName]![UnBoundControlName] & "*" Is Null

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.
However, this requires all fields to be filled in to return any records. How
can the query parameters be blank for all empty fields on the form? For
example, if all fields are blank, all records are shown.

Currently, my query parameters are of the form:

[Forms]![FormName]![UnBoundControlName]

Thanks

Dave

 

[Guest]

Thanks, works exactly as needed.

Dave

It should work, and it should look like

Where (employeeID = X Or X Is Null) and (equipmentID = Y Or Y Is Null)

Or, try

Where employeeID Like IIf(X Is Null, "*", X) and equipmentID Like IIf(Y Is
Null, "*",Y)


--
\\// Live Long and Prosper \\//
BS"D

Good effort but not what I was looking for.

Ofer, your suggestion seems to still require all search fields to be
entered, but instead removes the "found" item from the record set. (I'm
pretty sure that's what happened, will double check)

What I need is for the query to return all records when the textbox is
blank. For example, I have two search fields, employeeID and equipmentID.
Required results are:

If both fields are empty all records are returned.
If employeeID = x and equipmentID is empty, all records with employeeID = x
are returned.
If employeeID is empty and equipmentID = x, all records with equipmentID are
returned.
If employeeID = x and equipmentID = y, all records with employeeID = x AND
equipmentID = y are returned.

Any more ideas?

Thanks

Dave

Dave,
i think this is what you need,

Like [Forms]![FormName]![UnBoundControlName] & "*" Is Null

:

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.
However, this requires all fields to be filled in to return any records. How
can the query parameters be blank for all empty fields on the form? For
example, if all fields are blank, all records are shown.

Currently, my query parameters are of the form:

[Forms]![FormName]![UnBoundControlName]

Thanks

Dave

 

[Guest]

Here's my final SQL for thise that may need it:

SELECT PlantHireDetails.TransactionID, PlantHireDetails.PlantID,
PlantHireDetails.HireDate, PlantHireDetails.EmployeeID,
PlantHireDetails.JobNumber, PlantHireDetails.ExpectedReturnDate,
PlantHireDetails.ReturnDate
FROM PlantHireDetails
WHERE (EmployeeID = [Forms]![PlantHireDetailsSearch]![EmployeeIDFind] Or
[Forms]![PlantHireDetailsSearch]![EmployeeIDFind] Is Null) and (PlantID =
[Forms]![PlantHireDetailsSearch]![PlantIdFind] Or
[Forms]![PlantHireDetailsSearch]![PlantIDFind] Is Null);

It should work, and it should look like

Where (employeeID = X Or X Is Null) and (equipmentID = Y Or Y Is Null)

Or, try

Where employeeID Like IIf(X Is Null, "*", X) and equipmentID Like IIf(Y Is
Null, "*",Y)


--
\\// Live Long and Prosper \\//
BS"D

Good effort but not what I was looking for.

Ofer, your suggestion seems to still require all search fields to be
entered, but instead removes the "found" item from the record set. (I'm
pretty sure that's what happened, will double check)

What I need is for the query to return all records when the textbox is
blank. For example, I have two search fields, employeeID and equipmentID.
Required results are:

If both fields are empty all records are returned.
If employeeID = x and equipmentID is empty, all records with employeeID = x
are returned.
If employeeID is empty and equipmentID = x, all records with equipmentID are
returned.
If employeeID = x and equipmentID = y, all records with employeeID = x AND
equipmentID = y are returned.

Any more ideas?

Thanks

Dave

Dave,
i think this is what you need,

Like [Forms]![FormName]![UnBoundControlName] & "*" Is Null

:

I have a continuous form based on a query. In the form header, I have unbound
textboxes relating to the controls in the continuous form. The parameters for
the query are the values in the unbound controls. This all works as expected.
However, this requires all fields to be filled in to return any records. How
can the query parameters be blank for all empty fields on the form? For
example, if all fields are blank, all records are shown.

Currently, my query parameters are of the form:

[Forms]![FormName]![UnBoundControlName]

Thanks

Dave