Dynmaic Query

[zambrtp]

I am trying to create a dynamic Query....
I create a query with this criteria [Forms]![Formname]![txtboxname] OR LIKE [Forms]![Formname]![txtboxname]iS NULL, I set the parameters to match the query.
BUT it wont work correctly....
A command button to calls the form with the txtboxes then the a cmd button calls the query...then when I try to view the SQL it says its to large
HELP NEED urgently

 

[jdegeorge]

When you run your query the related form has to be open. It can be minimized for not visible, but it has to be open. If it's not your query won't work.

Jim DeGeorge

 

[zambrtp]

Jim

I think I am not clear....When I select Run Dynamic query the form pops up with 10 selections text boxes, then the user can enter information then select OK and that calls the query that should get the parameters show the data.
But sometimes the parameters seem to be ignored.
And it shows all data or NO data..
Help

 

[WayneRyan]

zambrtp,

When you use the Like operator, you must use the "*" with it.

In the following, the first part is right, but there is no
such syntax as "Like [Forms]![Formname]![txtboxname]iS NULL.

Maybe you need this for EACH criteria:

Like "'*" & [Forms]![Formname]![txtboxname] & "*' OR
[Forms]![Formname]![txtboxname]Is NULL

Wayne

 

[zambrtp]

Wayne:

THanks SOOO much that seems to work right.... I thought it was weird to work once or twice then quit...funny it would work at all...

Now one more questions....When I open my form ( has no sub forms) Why does it start off with a record I want it to open empty.

Tereza

 

[zambrtp]

AGGHHHHHH!!!! Wayne I just opened the application again and it doesnt work .....it does work if I dont use the form with the text boxes but when I pass the parameters through there it does not work....

Should I NOT set parameters in the Query or am I suppose to set them like this " like "*" [Forms]....
right now I just put [Forms]![]![]....

Do I need to set something else in the form Im forgetting?
HELP....need to have this as a prototype for customer soon..

 

[jdegeorge]

If you have a query and you want the user to enter paramters, you don't need a form to do that. Instead of putting

[Forms]![Formname]![txtboxname]

in the query criteria box, just put something like this

[Enter WHATEVER]

in each criteria box for each field that you want the user to filter. That way you can get rid of the form you're trying to use as a filter.

Hope this helps.

Jim DeGeorge

 

[zambrtp]

Jim:

The user doesnt want to have to hit enter 10 times if they are only searching on One of those values...I showed them that option and the form option..unfortunately the first time I showed them the form way it worked....

HELP!!!

 

[WayneRyan]

zambrtp,

If you just put:

Like "*" & [Forms]![Formname]![txtboxname] & "*"

it should work. The form must be open and you have to
substitute your real form and field names. If the field
has the possibility of being null in your table, then
you can put ANOTHER line below that in the criteria
section (Like won't work with Nulls):

Is Null

Wayne

 

[zambrtp]

Wayne:

Yes there is the option of the user only inserting 2 fields to query on and not all 10...what do I need to do...?

1. Drop LIKE
2. "*" [Forms]![DynamicForm]![txtbox] "*"
3. "*" [Forms]![DynamicForm]![txtbox] "*" IS NULL

I have tried this

.[Column]=
[Forms]![DynamicForm]![txtbox]OR [Forms]![DynamicForm]![txtbox] IS NULL

This doesnt work after Access changes it on save...

 

[WayneRyan]

zam,

No, the Is Null goes on the line below. In words it means:
If the field is like the criteria or the field is null.
This has no bearing on whether they enter the criteria
textbox or not, it is strictly for the tables data.

Wayne

 

[zambrtp]

Wayne:

In the criteria of the query..I put [Forms]![DynamicForm]![txtbox]OR [Forms]![DynamicForm]![txtbox] IS NULL
then it turned into a HUGE query when I saved it..but didnt work...
what did I do wrong?

 

[jdegeorge]

Open your query in SQL mode and paste the code here. It would make it VERY clear as to exactly what you're doing!

Jim DeGeorge

 

[zambrtp]

Jim:

When I try to open the query in SQL mode it blows up...locks the computer...sometimes it says not enough memory ..I switch to other computers and it says its to large to view...BUT literally I enter one line in the citeria field and when I look at that again it has 50 lines for my one on each table...

 

[jdegeorge]

I have an MDB with a sign-on screen that users must select their name from a dropdown box and also select a conversion project from another dropdown box. The OK button on this form hides the sign-on form so that I can refer to these fields later on, and then opens the main menu.

Many forms in my MDB are based on queries with 1 of the query's fields being based on a value on the hidden sign-on form. In the CRITERIA row for the field I have this code:

[forms]![frmSignOn]![ctlConversion]

As long as the referring form is active (whether hidden or not) the query will run.

It's quite simple and it seems that you're on the right track but I can't understand why it's not working consistently for you.

Is this query a SELECT query or is it some other type of action query like MAKE TABLE, etc.?

Jim DeGeorge

 

[philde]

I'm using Access 2000 and I'm having the same issue using
[Forms]![FormName]![TextBoxName]

 

[zambrtp]

JDG:

The form stays active ( if active means visible and not closed), The weird thing is the select statement is just a oneliner...[]![]![]! Or []![]![] IS NULL but when I reopen the query after a save...it is HUGE to large to open...and doesnt work..

 

[philde]

There is something weird about this in my case. I left work one day and it was not working. I came in the next day and it was working. I added the remainding parameters to the form and query to be past and they didn't work. I tested them by placing the * in the form, leaving it active I ran the query and got no results. I remove the parameter call from the query and just placed an * in the query criteria and it returned records. The other parameter calls in the query work fine. It's almost like a bug in stead of not doing someting correctly in my case.

Philde

 

[zambrtp]

Phil:

I agree...if someone could show me a simple 10 parameter dynamic query on a form....that works consistently...I would be eternally grateful...
Allowing NULL values...

Once again....I have a form with 10 txt fields...the form has its txt fields associated with a query ( each txt field is a parameter of that query..)I hope Im clear..
I have 3 things associated with this ...a form, a query with parameters, and a form that displays the query results

 

[mgmslate]

I have a form where I choose the parameters and pass them to another form in the form open where condition.

Dim strcriteria As String

If chklengthID Then
strcriteria = strcriteria & " AND lengthID = " &_ cbolengthID
End If

If chksupplierID Then
strcriteria = strcriteria & " AND supplierID = " &_ cbosupplierID
End If

If chkcolorID Then
strcriteria = strcriteria & " AND colorID = " &_ cbocolorID
End If

If chkthicknessid Then
strcriteria = strcriteria & " AND thicknessID = " &_ cbothicknessid
End If

If chkstatus Then
strcriteria = strcriteria & " AND status = """ &_ cbostatus & """"
End If
If chkheavy Then
strcriteria = strcriteria & &quot; AND thicknessid <> 1&quot;
End If
strcriteria = Mid$(strcriteria, 6)
DoCmd.OpenForm &quot;frmsales&quot;, , , strcriteria