Query by Form

[ewong1]

I am attempting to query details of an order that has been entered based on multiple criteria that is entered on a form. The SQL for my query is below. Unfortunately, I have everything querying if the fields are left blank, but if I fill in Form Field IDPress it wont give me the specifics for just that one field.

Example: I select press 1 from my combo box for IDPress. The query returns press information for press 1, 10, 11, 12, 13, 14, 15 ...

Is there a way for my selection to be more selective? Please help! Thanks.

SELECT tblOrderDetail.IDCycle, tblOrderDetail.intProgram, tblOrderDetail.IDLot, tblOrderDetail.ysnFillMethod, tblOrderDetail.dtmStartFill, tblOrderDetail.dtmEndFill, tblOrderDetail.dtmStartPress, tblOrderDetail.dtmEndPress, tblOrderDetail.dtmStartDischarge, tblOrderDetail.dtmEndDischarge, tblOrderDetail.mmoRemarks, tblOrderDetail.dtmDate, tblOrderDetail.IDPress
FROM tblOrderDetail
WHERE (((tblOrderDetail.intProgram) Like [forms]![frmFindOrderDetail]![intProgram] & "*")
AND ((tblOrderDetail.IDLot) Like [forms]![frmFindOrderDetail]![IDLot] & "*")
AND ((tblOrderDetail.dtmDate) Like [forms]![frmFindOrderDetail]![dtmDate] & "*")
AND ((tblOrderDetail.IDPress) Like [forms]![frmFindOrderDetail]![IDPress] & "*"))
ORDER BY tblOrderDetail.IDCycle;

 

[neileg]

You have used the Like comparator. 1 is 'like' 11 and 12 and 15 and 1115672A, etc.

The trouble is you are allowing for matches on any combination of your text boxes and by concatenating the entry in the box with * and using 'Like' it allows empty text boxes to match with any value.

You may have to reconsider how you are allowing values in the other boxes.

 

[ewong1]

Is there any way that I can set the query up to return either the selection only or all of the values?

example: combo box selection 1 returns only press 1, combo box selection empty returns all presses?

 

[Jon K]

I have attached a sample database. See Query1 in the database.
.

 

[ewong1]

Is it possible to have the database converted to load in access 97? sorry.. i dont have a newer version.

 

[Jon K]

Here's the Access 97 version.
.

 

[ewong1]

Thanks Jon K! it worked!

 

[Iceman95]

Unfortunately, this does not work when the query is a crosstab query and the parameter has a Text format property.

It seems that Access 97 is not able to evaluate Text parameters with a Null value in crosstab query (the same if the criteria is set to something like : [Parameter]=[Forms]![MyForm]![MyParameter] OR =[Forms]![MyForm]![MyParameter]="").

I've struggled with this weird problem for weeks and come to the conclusion that I have to use only numeric parameters, which is not what I intended for my query.

Any idea on a possible solution ?

 

[neileg]

Have a look at the Nz() function. This converts nulls into anything you specify. This may help you.

 

[Jon K]

After changing the original Query1 as follows for the crosstab query:-

SELECT tblData.ID, tblData.FieldA, tblData.FieldB, tblData.FieldC
FROM tblData
WHERE
([FieldA]=[Forms]![MyForm]![cboFieldA] Or [Forms]![Myform]![cbofieldA] Is Null)=True AND
([FieldB]=[Forms]![MyForm]![cboFieldB] Or [Forms]![Myform]![cbofieldB]="")=True AND
([FieldC]=[Forms]![MyForm]![cboFieldC] Or [Forms]![Myform]![cbofieldC] Is Null)=True;

the crosstab query works on my Access 97 system. You can download the database, click on the button on the form to see if it works on yours.
.

 

[Iceman95]

Sorry, but I get the following message : "Unrecognized DataBase format 'D:\Search From Crosstab Access 97.mdb'".

Seems like your database is in Access 2000 format.

By the way, I must be more specific on my question : I do get the results I want through my Crosstab query ; the problem is when I want to view the dynamic report I've designed.

If I use Text parameters, it gives me a "No current record" message.

To design the report, I've recycled the "Solutions" DataBase example.

Maybe is there some code subtelties that are beyond my knowledge, but I cannot see what is wrong for the moment.

Have a look at the attached file (just added a Xtab query and the report to your sample DB) !

Thanks

Tom

 

[Jon K]

My Access 97 is a version capable of using double-byte characters.

Since a crosstab query requires Or [Forms]![Myform]![cbofieldB]="" whereas a report needs Or [Forms]![Myform]![cbofieldB] Is Null, I changed Query1 as follows and your report worked:-

SELECT tblData.ID, tblData.FieldA, tblData.FieldB, tblData.FieldC
FROM tblData
WHERE
([FieldA]=[Forms]![MyForm]![cboFieldA] Or [Forms]![Myform]![cbofieldA] Is Null)=True AND
([FieldB]=[Forms]![MyForm]![cboFieldB] Or [Forms]![Myform]![cbofieldB]=""
Or [Forms]![Myform]![cbofieldB] Is Null)=True AND
([FieldC]=[Forms]![MyForm]![cboFieldC] Or [Forms]![Myform]![cbofieldC] Is Null)=True;

I have also tested it in Access 2000 and 2003. When the dynamic report is run, the "no current record" error is gone.

You can try it out in your version of Access 97.
.

 

[Iceman95]

Man, you're simply an ABSOLUTE GENIUS !!! I can't believe it works, after days ans days of testing, hair-pulling, nearing nervous breakdown...

I was searching for such a tip since the Jurassic Age !

These are the sort of things that are so upsetting with Access : the solution is sometimes so simple it's unbelievable !

Anyway, thanks a thousand times, your help has been unevaluable !

Tom