How to sort using radio buttons?

[PoorCadaver]

Yey! Almost there!

One little glinch though...

When I search for i.e. in use, it forgets the columns that are based from other tables. Like cathegory. I have cathegorys like Pc, Camera, Speakers in a table and in the main table I can chose from these categorys to save to a specific equipment. Now it just shows #Name? or show the ID of the post in the linked table... This worked before.

[rpeare]

So modify the SQL statement to link in that secondary field

Instead of "SELECT * FROM TableName WHERE InUse = -1"

You'd have to use a SQL statement that included the secondary table. If you're not comfortable building the SQL statement, write a query that shows you what you want, then copy that SQL statement into your code.

[PoorCadaver]

I already have a query (qrySearch) that is called by the search button and it does what it's supposed to. Do you suggest that instead of using the query I should use SQL in the OnClick for the button?

[rpeare]

No, your qrySearch doesn't do what you want. It's a generic query that shows everything that would work for your "SHOW ALL" option, but you have to modify that SQL statement for your SHOW IN USE and SHOW NOT IN USE options

[PoorCadaver]

Ok, I will try that in a couple of days. Now we are having easter holydays in Sweden so I'm back at work in 4 days and will try this.
I'll see you then! Thanks for now!

[PoorCadaver]

Hi again!

How do I transfer the query code into SQL? I have a statement

LIKE [Forms]![frmSearch].[qProductID1] & "*"

in qrySearch that is called when I click the Search button.

[rpeare]

"SELECT * FROM TableName WHERE [FieldName] Like '" & pforms!frmsearch!qproductid1 & "*'"

[PoorCadaver]

And do I need to do this for every text field I have in the search form?

[rpeare]

You can build your SQL statement to look at whatever fields you want based on what a user has checked off, typed in or selected from combo boxes/list boxes. You just have to apply a little logic. So if you have given them 5 search boxes 4 of which are text fields 1 of which is a number field you have to test each field to see if it's null or blank and if it's not add an appropriate statement to your WHERE string.

[PoorCadaver]

I typed this:

"SELECT * FROM tblUnit WHERE ProductID LIKE '& forms!frmSearch!qProductID1 & '*'"

but I get a compilation error saying sometihing like expected row number, row label, string or end of string.

[rpeare]

well that's not the syntax I gave you

If your qProductID1 is a text field you can use the LIKE statement, if it's a number you can't.

if it is in fact text the syntax is:

"SELECT * FROM tblUnit WHERE ProductID LIKE '" & forms!frmSearch!qProductID1 & "*'"

[PoorCadaver]

It's a number, but in the query I can use like. What should I write then?

[rpeare]

"SELECT * FROM tblUnit WHERE ProductID = " & forms!frmSearch!qProductID1

Assuming that your field qPRODUCTID1 contains (if it's a combo box the bound column is..) a number.

[PoorCadaver]

Me!frmSearchSub.Form.RecordSource = "SELECT * FROM tblUnit WHERE ProductID = " & Forms!frmSearch!qProductID1

gives me runtime error 3463 data type mismatch... But ProductID are numbers...

[rpeare]

use your debug.print messages to see if it's building the SQL statement you want


dim sSQL as string

sSQL = "SELECT * FROM tblUnit WHERE ProductID = " & forms!frmSearch!qProductID1
debug.print sSQL

if your qPRODUCTID field is a combo box or list box it may not be looking at the column you think it is.