Query problem - not all the results are found.

[Richard Horne]

I have a form based around various combo boxes and text boxes, the query
takes the results of these fields to display various quotations. But the
results are inconsistent and some results are missed out but I can't
understand why?

Any help would be gratefully received.

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like
Nz([cboCompanyName],"*")) AND ((Quotations.[Your Reference]) Like
Nz([QuoteRef],"*")) AND ((Quotations.QuotationBy) Like Nz([cboQuoteBy],"*"))
AND ((Quotation_Details.Product_lookup) Like Nz([cboProduct],"*")) AND
((Quotation_Details.[Full Description]) Like Nz([txtDescription],"*")) AND
((Quotation_Details.[Type/Colour/Size]) Like Nz([txtTypeColourSize],"*")))
ORDER BY Quotations.QuotationID DESC;

 

[Allen Browne]

Firstly, any record in the Quotation table that has no matching records in
Quotation_Details won't be returned.

That's probably the minor issue, though. The real issue probably relates to
Access not understanding the data types correctly.

Presumably you have a form where these results are shown, and the form also
has unbound controls named cboCompanyName, QuoteRef, cboQuoteBy, cboProduct,
txtDescription, and txtTypeColourSize. You are using the Like operator and
Nz() so you end up with the wildcard when these unbound controls are Null.

There's always a chance that the unbound controls contain a zero-length
string instead of a Null. That won't happen unless you assign a zls to the
control, but it's important not to do that. For example, if you are
programmatically clearing these controls, assign Null, not "".

The Like operator forces a string comparision. I'm guessing that some of
these are Number type fields (not Text type fields) - e.g. those ending in
Lookup.

The best solution would be to add a command button beside the unbound
controls. You can leave the criteria out of the query completely. Then in
the Click event of the command button, build the filter string and apply it
to the form like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "(Quotation_Customer_Lookup = " & _
Me.cboCompanyName & ") AND "
End If

If Not IsNull(Me.QuoteRef) Then
strWhere = strWhere & "([Your Reference] = """ & Me.QuoteRef & """)
AND "

End If
'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

(Note: Text type fields need extra quotes as the delimiters. The first
example above assumes a Number type field, and the 2nd one has the extra
quotes for a text type field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based around various combo boxes and text boxes, the query
takes the results of these fields to display various quotations. But the
results are inconsistent and some results are missed out but I can't
understand why?

Any help would be gratefully received.

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like
Nz([cboCompanyName],"*")) AND ((Quotations.[Your Reference]) Like
Nz([QuoteRef],"*")) AND ((Quotations.QuotationBy) Like
Nz([cboQuoteBy],"*"))
AND ((Quotation_Details.Product_lookup) Like Nz([cboProduct],"*")) AND
((Quotation_Details.[Full Description]) Like Nz([txtDescription],"*")) AND
((Quotation_Details.[Type/Colour/Size]) Like Nz([txtTypeColourSize],"*")))
ORDER BY Quotations.QuotationID DESC;

 

[Richard Horne]

Hi Allen thanks for your reply. It made sense but I'm struggling to adapt and
modify your code. I'm not sure which bits I should be replacing and with
what.

To help, here are my filter fields:

cboQueriedCustomer
cboQueriedContact
QueriedCustRef
cboOrder_Complete
cboProduct
txtDescription
txtTypeColourSize

I would much appreciate if you could apply these field names to the code you
sent me. I'm really not much kop with visual basic as my head is too clogged
up with javascript and PHP coding conventions :S

Firstly, any record in the Quotation table that has no matching records in
Quotation_Details won't be returned.

That's probably the minor issue, though. The real issue probably relates to
Access not understanding the data types correctly.

Presumably you have a form where these results are shown, and the form also
has unbound controls named cboCompanyName, QuoteRef, cboQuoteBy, cboProduct,
txtDescription, and txtTypeColourSize. You are using the Like operator and
Nz() so you end up with the wildcard when these unbound controls are Null.

There's always a chance that the unbound controls contain a zero-length
string instead of a Null. That won't happen unless you assign a zls to the
control, but it's important not to do that. For example, if you are
programmatically clearing these controls, assign Null, not "".

The Like operator forces a string comparision. I'm guessing that some of
these are Number type fields (not Text type fields) - e.g. those ending in
Lookup.

The best solution would be to add a command button beside the unbound
controls. You can leave the criteria out of the query completely. Then in
the Click event of the command button, build the filter string and apply it
to the form like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "(Quotation_Customer_Lookup = " & _
Me.cboCompanyName & ") AND "
End If

If Not IsNull(Me.QuoteRef) Then
strWhere = strWhere & "([Your Reference] = """ & Me.QuoteRef & """)
AND "

End If
'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

(Note: Text type fields need extra quotes as the delimiters. The first
example above assumes a Number type field, and the 2nd one has the extra
quotes for a text type field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based around various combo boxes and text boxes, the query
takes the results of these fields to display various quotations. But the
results are inconsistent and some results are missed out but I can't
understand why?

Any help would be gratefully received.

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like
Nz([cboCompanyName],"*")) AND ((Quotations.[Your Reference]) Like
Nz([QuoteRef],"*")) AND ((Quotations.QuotationBy) Like
Nz([cboQuoteBy],"*"))
AND ((Quotation_Details.Product_lookup) Like Nz([cboProduct],"*")) AND
((Quotation_Details.[Full Description]) Like Nz([txtDescription],"*")) AND
((Quotation_Details.[Type/Colour/Size]) Like Nz([txtTypeColourSize],"*")))
ORDER BY Quotations.QuotationID DESC;

 

[Allen Browne]

Lets see if we can help you understand how to build up this string.
The goal is to build something that looks like the WHERE clause in a query.

To get an example target string, you could mock up a query into this table,
type any old value (such as 3 or "Jones", or #1/1/2005#) into the Criteria
row beneath the fields that you want to filter on. Then switch it to SQL
View (View menu), and just look at the WHERE clause of the query. That's
what the target string looks like.

Now you know what you are aiming for, you build it a section at a time,
based on the non-blank filter boxes. We use IsNull() to test if they have a
value. If so we tack some more onto the strWhere string.

What we tack on consists of these parts:
- Opening bracket: (
- Field name, e.g. [OrderID]
- Operator: usually =.
- Value to match, e.g. 3, "Jones", #1/1/2005#, True
- Closing bracket: )
- Conjunction to the next phrase: AND

Notes:
1. Use square brackets around the field name if the name contains a
non-alphanumeric character (such as a space.)

2. The brackets are often optional, and you will see Access putting in lots
of unnecessary brackets when you look in SQL View of a query.

3. The Value must must be delimited with quotes if it is applied to a Text
field, or delimited with # if applied to a Date/Time type field. Number
fields have no delimiter.

4. If you have quotes inside quotes, you need to double them up. For
example, to get the result:
This string has a "word" in quotes
you would enter:
"This string has a ""word"" in quotes."

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen thanks for your reply. It made sense but I'm struggling to adapt
and
modify your code. I'm not sure which bits I should be replacing and with
what.

To help, here are my filter fields:

cboQueriedCustomer
cboQueriedContact
QueriedCustRef
cboOrder_Complete
cboProduct
txtDescription
txtTypeColourSize

I would much appreciate if you could apply these field names to the code
you
sent me. I'm really not much kop with visual basic as my head is too
clogged
up with javascript and PHP coding conventions :S

Firstly, any record in the Quotation table that has no matching records
in
Quotation_Details won't be returned.

That's probably the minor issue, though. The real issue probably relates
to
Access not understanding the data types correctly.

Presumably you have a form where these results are shown, and the form
also
has unbound controls named cboCompanyName, QuoteRef, cboQuoteBy,
cboProduct,
txtDescription, and txtTypeColourSize. You are using the Like operator
and
Nz() so you end up with the wildcard when these unbound controls are
Null.

There's always a chance that the unbound controls contain a zero-length
string instead of a Null. That won't happen unless you assign a zls to
the
control, but it's important not to do that. For example, if you are
programmatically clearing these controls, assign Null, not "".

The Like operator forces a string comparision. I'm guessing that some of
these are Number type fields (not Text type fields) - e.g. those ending
in
Lookup.

The best solution would be to add a command button beside the unbound
controls. You can leave the criteria out of the query completely. Then in
the Click event of the command button, build the filter string and apply
it
to the form like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "(Quotation_Customer_Lookup = " & _
Me.cboCompanyName & ") AND "
End If

If Not IsNull(Me.QuoteRef) Then
strWhere = strWhere & "([Your Reference] = """ & Me.QuoteRef &
""")
AND "

End If
'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

(Note: Text type fields need extra quotes as the delimiters. The first
example above assumes a Number type field, and the 2nd one has the extra
quotes for a text type field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based around various combo boxes and text boxes, the query
takes the results of these fields to display various quotations. But
the
results are inconsistent and some results are missed out but I can't
understand why?

Any help would be gratefully received.

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID
=
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like
Nz([cboCompanyName],"*")) AND ((Quotations.[Your Reference]) Like
Nz([QuoteRef],"*")) AND ((Quotations.QuotationBy) Like
Nz([cboQuoteBy],"*"))
AND ((Quotation_Details.Product_lookup) Like Nz([cboProduct],"*")) AND
((Quotation_Details.[Full Description]) Like Nz([txtDescription],"*"))
AND
((Quotation_Details.[Type/Colour/Size]) Like
Nz([txtTypeColourSize],"*")))
ORDER BY Quotations.QuotationID DESC;

 

[Richard Horne]

Thanks Allen that's a great help, I will plod on and try and write this myself.

One thing I'd better explain as I think we might have been on a different
wavelength. The form I had before, updated as soon as any criteria was
entered. The way you've suggested seems to be different - did you have in
mind that upon the click of my 'Search' button the query is opened separately
with the query based on my criteria?

If so then I need to create the form/query separately and get my button to
open that as well as generate my query string?

Right?

Oh and thanks again Allen, I've lost track the number of times you've helped
me out on this massive project. Little over a year ago I'd never used Access
but chiefly because of yours and other on here's help, I've come a long way.
Thank you.

Lets see if we can help you understand how to build up this string.
The goal is to build something that looks like the WHERE clause in a query.

To get an example target string, you could mock up a query into this table,
type any old value (such as 3 or "Jones", or #1/1/2005#) into the Criteria
row beneath the fields that you want to filter on. Then switch it to SQL
View (View menu), and just look at the WHERE clause of the query. That's
what the target string looks like.

Now you know what you are aiming for, you build it a section at a time,
based on the non-blank filter boxes. We use IsNull() to test if they have a
value. If so we tack some more onto the strWhere string.

What we tack on consists of these parts:
- Opening bracket: (
- Field name, e.g. [OrderID]
- Operator: usually =.
- Value to match, e.g. 3, "Jones", #1/1/2005#, True
- Closing bracket: )
- Conjunction to the next phrase: AND

Notes:
1. Use square brackets around the field name if the name contains a
non-alphanumeric character (such as a space.)

2. The brackets are often optional, and you will see Access putting in lots
of unnecessary brackets when you look in SQL View of a query.

3. The Value must must be delimited with quotes if it is applied to a Text
field, or delimited with # if applied to a Date/Time type field. Number
fields have no delimiter.

4. If you have quotes inside quotes, you need to double them up. For
example, to get the result:
This string has a "word" in quotes
you would enter:
"This string has a ""word"" in quotes."

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen thanks for your reply. It made sense but I'm struggling to adapt
and
modify your code. I'm not sure which bits I should be replacing and with
what.

To help, here are my filter fields:

cboQueriedCustomer
cboQueriedContact
QueriedCustRef
cboOrder_Complete
cboProduct
txtDescription
txtTypeColourSize

I would much appreciate if you could apply these field names to the code
you
sent me. I'm really not much kop with visual basic as my head is too
clogged
up with javascript and PHP coding conventions :S

Firstly, any record in the Quotation table that has no matching records
in
Quotation_Details won't be returned.

That's probably the minor issue, though. The real issue probably relates
to
Access not understanding the data types correctly.

Presumably you have a form where these results are shown, and the form
also
has unbound controls named cboCompanyName, QuoteRef, cboQuoteBy,
cboProduct,
txtDescription, and txtTypeColourSize. You are using the Like operator
and
Nz() so you end up with the wildcard when these unbound controls are
Null.

There's always a chance that the unbound controls contain a zero-length
string instead of a Null. That won't happen unless you assign a zls to
the
control, but it's important not to do that. For example, if you are
programmatically clearing these controls, assign Null, not "".

The Like operator forces a string comparision. I'm guessing that some of
these are Number type fields (not Text type fields) - e.g. those ending
in
Lookup.

The best solution would be to add a command button beside the unbound
controls. You can leave the criteria out of the query completely. Then in
the Click event of the command button, build the filter string and apply
it
to the form like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "(Quotation_Customer_Lookup = " & _
Me.cboCompanyName & ") AND "
End If

If Not IsNull(Me.QuoteRef) Then
strWhere = strWhere & "([Your Reference] = """ & Me.QuoteRef &
""")
AND "

End If
'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

(Note: Text type fields need extra quotes as the delimiters. The first
example above assumes a Number type field, and the 2nd one has the extra
quotes for a text type field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based around various combo boxes and text boxes, the query
takes the results of these fields to display various quotations. But
the
results are inconsistent and some results are missed out but I can't
understand why?

Any help would be gratefully received.

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID
=
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like
Nz([cboCompanyName],"*")) AND ((Quotations.[Your Reference]) Like
Nz([QuoteRef],"*")) AND ((Quotations.QuotationBy) Like
Nz([cboQuoteBy],"*"))
AND ((Quotation_Details.Product_lookup) Like Nz([cboProduct],"*")) AND
((Quotation_Details.[Full Description]) Like Nz([txtDescription],"*"))
AND
((Quotation_Details.[Type/Colour/Size]) Like
Nz([txtTypeColourSize],"*")))
ORDER BY Quotations.QuotationID DESC;

 

[Allen Browne]

Yes, I did assume you would click a button to achieve this.
Guess you could use the AfterUpdate event of all the controls if you wanted
to go that way.

Glad we have helped you progess towards your goal.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen that's a great help, I will plod on and try and write this
myself.

One thing I'd better explain as I think we might have been on a different
wavelength. The form I had before, updated as soon as any criteria was
entered. The way you've suggested seems to be different - did you have in
mind that upon the click of my 'Search' button the query is opened
separately
with the query based on my criteria?

If so then I need to create the form/query separately and get my button to
open that as well as generate my query string?

Right?

Oh and thanks again Allen, I've lost track the number of times you've
helped
me out on this massive project. Little over a year ago I'd never used
Access
but chiefly because of yours and other on here's help, I've come a long
way.
Thank you.

Lets see if we can help you understand how to build up this string.
The goal is to build something that looks like the WHERE clause in a
query.

To get an example target string, you could mock up a query into this
table,
type any old value (such as 3 or "Jones", or #1/1/2005#) into the
Criteria
row beneath the fields that you want to filter on. Then switch it to SQL
View (View menu), and just look at the WHERE clause of the query. That's
what the target string looks like.

Now you know what you are aiming for, you build it a section at a time,
based on the non-blank filter boxes. We use IsNull() to test if they have
a
value. If so we tack some more onto the strWhere string.

What we tack on consists of these parts:
- Opening bracket: (
- Field name, e.g. [OrderID]
- Operator: usually =.
- Value to match, e.g. 3, "Jones", #1/1/2005#, True
- Closing bracket: )
- Conjunction to the next phrase: AND

Notes:
1. Use square brackets around the field name if the name contains a
non-alphanumeric character (such as a space.)

2. The brackets are often optional, and you will see Access putting in
lots
of unnecessary brackets when you look in SQL View of a query.

3. The Value must must be delimited with quotes if it is applied to a
Text
field, or delimited with # if applied to a Date/Time type field. Number
fields have no delimiter.

4. If you have quotes inside quotes, you need to double them up. For
example, to get the result:
This string has a "word" in quotes
you would enter:
"This string has a ""word"" in quotes."

Hi Allen thanks for your reply. It made sense but I'm struggling to
adapt
and
modify your code. I'm not sure which bits I should be replacing and
with
what.

To help, here are my filter fields:

cboQueriedCustomer
cboQueriedContact
QueriedCustRef
cboOrder_Complete
cboProduct
txtDescription
txtTypeColourSize

I would much appreciate if you could apply these field names to the
code
you
sent me. I'm really not much kop with visual basic as my head is too
clogged
up with javascript and PHP coding conventions :S


:

Firstly, any record in the Quotation table that has no matching
records
in
Quotation_Details won't be returned.

That's probably the minor issue, though. The real issue probably
relates
to
Access not understanding the data types correctly.

Presumably you have a form where these results are shown, and the form
also
has unbound controls named cboCompanyName, QuoteRef, cboQuoteBy,
cboProduct,
txtDescription, and txtTypeColourSize. You are using the Like operator
and
Nz() so you end up with the wildcard when these unbound controls are
Null.

There's always a chance that the unbound controls contain a
zero-length
string instead of a Null. That won't happen unless you assign a zls to
the
control, but it's important not to do that. For example, if you are
programmatically clearing these controls, assign Null, not "".

The Like operator forces a string comparision. I'm guessing that some
of
these are Number type fields (not Text type fields) - e.g. those
ending
in
Lookup.

The best solution would be to add a command button beside the unbound
controls. You can leave the criteria out of the query completely. Then
in
the Click event of the command button, build the filter string and
apply
it
to the form like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "(Quotation_Customer_Lookup = " & _
Me.cboCompanyName & ") AND "
End If

If Not IsNull(Me.QuoteRef) Then
strWhere = strWhere & "([Your Reference] = """ & Me.QuoteRef &
""")
AND "

End If
'etc for other controls.

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first.
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

(Note: Text type fields need extra quotes as the delimiters. The first
example above assumes a Number type field, and the 2nd one has the
extra
quotes for a text type field.)

message
I have a form based around various combo boxes and text boxes, the
query
takes the results of these fields to display various quotations. But
the
results are inconsistent and some results are missed out but I can't
understand why?

Any help would be gratefully received.

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full
Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON
Quotations.QuotationID
=
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like
Nz([cboCompanyName],"*")) AND ((Quotations.[Your Reference]) Like
Nz([QuoteRef],"*")) AND ((Quotations.QuotationBy) Like
Nz([cboQuoteBy],"*"))
AND ((Quotation_Details.Product_lookup) Like Nz([cboProduct],"*"))
AND
((Quotation_Details.[Full Description]) Like
Nz([txtDescription],"*"))
AND
((Quotation_Details.[Type/Colour/Size]) Like
Nz([txtTypeColourSize],"*")))
ORDER BY Quotations.QuotationID DESC;