Criteria in a crosstab query,problem....

  • Thread starter Thread starter danijela.simunovic
  • Start date Start date
D

danijela.simunovic

Hi!
I dont know how to get criteria in a crosstab query working!
If i write something in Criteria it's ok but if I put something like
this in the Criteria: [Forms]![Form1]![Text1] then it says "The
microsoft jet database engine does not recognize
[Forms]![Form1]![Text1] as a valid field name or expression! What do
i have to do to make it work!?

Danijela
 
And one more question.
In a Select query if the criteria is [Forms]![Form1]![Text1] and
in that text box(text1) i write "monday" it works but if i
write "monday Or tuesday" it doesn't work. But if I go to the
criteria and write "monday Or tuesday" it works. So my question is
how do i get operators in the text box or somehow so that the criteria
works?

Danijela
 
You can not put "operators" in the textbox. If you want to use more than one
and they are the days of the week I would suggest using a ListBox with
multi-select.

Another way would be to have two TextBox and in the criteria use --
[Forms]![Form1]![Text1] Or [Forms]![Form1]![Text2] Or is Null
 
Hi!
I dont know how to get criteria in a crosstab query working!
If i write something in Criteria it's ok but if I put something like
this in the Criteria: [Forms]![Form1]![Text1] then it says "The
microsoft jet database engine does not recognize
[Forms]![Form1]![Text1] as a valid field name or expression! What do
i have to do to make it work!?

Danijela
Click to expand...

1) The form "Form1" must be open when the query is run.
2) In a Crosstab query you MUST write the parameter name and datatype
in the Parameter dialog box (as well as on the criteria line).
In query Design View, click on Query + Parameter.
Write:
forms!Form1!Text1
in the left Parameter column and the expected datatype in the right
column.

Does this take care of the problem?
 
Hi!
I dont know how to get criteria in a crosstab query working!
If i write something in Criteria it's ok but if I put something like
this in the Criteria: [Forms]![Form1]![Text1] then it says "The
microsoft jet database engine does not recognize
[Forms]![Form1]![Text1] as a valid field name or expression! What do
i have to do to make it work!?

Danijela
Click to expand...

It's always *allowed* to specify the Parameters of a query - but for a
crosstab (and some other queries) it's *required*.

Open the query in design view; right click the background of the table
icons; and select Parameters. Put

[Forms]![Form1]![Text1]

and specify its datatype (number, date, whatever). They must match
exactly.

For your other question - no, you cannot pass operators such as OR,
IN, commas, etc. in a parameter, only actual values. If you need to
let the user type in full SQL WHERE clause syntax (and if you trust
them to do so accurately...!) you'll need to write VBA code to
construct the SQL string of the entire query on the fly; a parameter
query won't work.

John W. Vinson[MVP]
 
KARL DEWEY said:
You can not put "operators" in the textbox. If you want to use more than
one
and they are the days of the week I would suggest using a ListBox with
multi-select.

Another way would be to have two TextBox and in the criteria use --
[Forms]![Form1]![Text1] Or [Forms]![Form1]![Text2] Or is Null

[email protected] said:
And one more question.
In a Select query if the criteria is [Forms]![Form1]![Text1] and
in that text box(text1) i write "monday" it works but if i
write "monday Or tuesday" it doesn't work. But if I go to the
criteria and write "monday Or tuesday" it works. So my question is
how do i get operators in the text box or somehow so that the criteria
works?

Danijela
Click to expand...
Click to expand...
 
The parameters take care of the problem! Thanks!
The other part then i'll just have to use Vb.
 
Can you please tell me how can a query take values from a list box? I
created it and made it multi select and put it in the query parameters
but it wont work, the query is empty all the time. Can you help me
please?
Thanks!

Danijela
 
Back
Top