Crosstab/Parameter Query

  • Thread starter Thread starter AH
  • Start date Start date
A

AH

Hi,

I had some help from the wonderfull people here at the
Microsoft Newsgroup for creating a Crosstab Query from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one thing: I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or expression.

Thanks for the help
 
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or expression.
Click to expand...

Is the colon supposed to be within the square brackets?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

AH said:
Hi,

I had some help from the wonderfull people here at the
Microsoft Newsgroup for creating a Crosstab Query from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one thing: I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or expression.

Thanks for the help
Click to expand...
 
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Yes it is! At least that's how I have done a Parameter
Query before. I think the problem is I am trying to
combine a Crosstab Query with a Parameter Query. I am not
a vetran at this, that's why I am confused.
-----Original Message-----
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
Click to expand...
expression.

Is the colon supposed to be within the square brackets?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

AH said:
Hi,

I had some help from the wonderfull people here at the
Microsoft Newsgroup for creating a Crosstab Query from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one thing: I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or expression.

Thanks for the help
Click to expand...


.
Click to expand...
Click to expand...
 
I have declared "name" as the parameter with "text" as
the type. I get the pop up menu asking for input. I put
in "user1" in the below example, but I get records for
everyone else too! Its like the parameter dialouge does
not do anything!
-----Original Message-----
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Yes it is! At least that's how I have done a Parameter
Query before. I think the problem is I am trying to
combine a Crosstab Query with a Parameter Query. I am not
a vetran at this, that's why I am confused.
-----Original Message-----
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or expression.

Is the colon supposed to be within the square brackets?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

I had some help from the wonderfull people here at the
Microsoft Newsgroup for creating a Crosstab Query from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one
Click to expand...
thing:
I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or expression.

Thanks for the help



.
Click to expand...
Click to expand...
.
Click to expand...
 
You can't have a parameter with the same name as a field name. Set your
parameter to [Enter Name] and update your query parameters. If this doesn't
work, copy your entire SQL into a message.

--
Duane Hookom
MS Access MVP


AH said:
I have declared "name" as the parameter with "text" as
the type. I get the pop up menu asking for input. I put
in "user1" in the below example, but I get records for
everyone else too! Its like the parameter dialouge does
not do anything!
-----Original Message-----
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Yes it is! At least that's how I have done a Parameter
Query before. I think the problem is I am trying to
combine a Crosstab Query with a Parameter Query. I am not
a vetran at this, that's why I am confused.

-----Original Message-----
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
expression.

Is the colon supposed to be within the square brackets?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

I had some help from the wonderfull people here at the
Microsoft Newsgroup for creating a Crosstab Query from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one thing:
I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I
get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
expression.

Thanks for the help



.
Click to expand...
.
Click to expand...
Click to expand...
 
I don't know. This should be a real simple task, but it
is still not working. Here is what I did step by step:
1. Created the Crosstab query and it displays the
following correctly:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

2. So far so good. Now all I want is to have the query
prompt me for a name and in turn it would list the
records for that entry:

Pop up box would say: "User Name:"
I enter "user1"
The query lists the user1 record

3. So I have tried all of the following under
Query>Parameters to test the parameter I need and none of
it works:
a) name as Parameter & text as Data Type
b) [name] as Parameter & text as Data Type
c) [name:] as Parameter & text as Data Type
d) [Enter Name:] as Parameter & text as Data Type

As you can see, I have tried all possiblities and they
all return all the records instead of just the records I
want!! This is begining to drive me CRAZYYYY!

Back to the most recent reply; How do I update my query
parameters? And what do you mean by "copy your entire SQL
into a message" ? Thanks a lot for the support everyone.
-----Original Message-----
You can't have a parameter with the same name as a field name. Set your
parameter to [Enter Name] and update your query parameters. If this doesn't
work, copy your entire SQL into a message.

--
Duane Hookom
MS Access MVP


AH said:
I have declared "name" as the parameter with "text" as
the type. I get the pop up menu asking for input. I put
in "user1" in the below example, but I get records for
everyone else too! Its like the parameter dialouge does
not do anything!
-----Original Message-----
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their
Click to expand...
parameters
must also be declared.
Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

AH wrote:

Yes it is! At least that's how I have done a Parameter
Query before. I think the problem is I am trying to
combine a Crosstab Query with a Parameter Query. I
Click to expand...
am
not
a vetran at this, that's why I am confused.

-----Original Message-----
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
expression.

Is the colon supposed to be within the square brackets?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

I had some help from the wonderfull people here
Click to expand...
at
the
Microsoft Newsgroup for creating a Crosstab Query from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one thing:
I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I
get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
expression.

Thanks for the help



.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Yeeehaawww, Yes, I knew it had a simple solution!!! Thank
you very much Duane. I really appreciate everyone's
input. thanks a lot.
-----Original Message-----
Do you have [User Name:] as a criteria under the Name column? Your parameter
name is the same as the prompt
[User Name:] Text

--
Duane Hookom
MS Access MVP


AH said:
I don't know. This should be a real simple task, but it
is still not working. Here is what I did step by step:
1. Created the Crosstab query and it displays the
following correctly:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

2. So far so good. Now all I want is to have the query
prompt me for a name and in turn it would list the
records for that entry:

Pop up box would say: "User Name:"
I enter "user1"
The query lists the user1 record

3. So I have tried all of the following under
Query>Parameters to test the parameter I need and none of
it works:
a) name as Parameter & text as Data Type
b) [name] as Parameter & text as Data Type
c) [name:] as Parameter & text as Data Type
d) [Enter Name:] as Parameter & text as Data Type

As you can see, I have tried all possiblities and they
all return all the records instead of just the records I
want!! This is begining to drive me CRAZYYYY!

Back to the most recent reply; How do I update my query
parameters? And what do you mean by "copy your entire SQL
into a message" ? Thanks a lot for the support everyone.
-----Original Message-----
You can't have a parameter with the same name as a
Click to expand...
field
name. Set your
parameter to [Enter Name] and update your query parameters. If this doesn't
work, copy your entire SQL into a message.

--
Duane Hookom
MS Access MVP


I have declared "name" as the parameter with "text" as
the type. I get the pop up menu asking for input. I put
in "user1" in the below example, but I get records for
everyone else too! Its like the parameter dialouge does
not do anything!

-----Original Message-----
With a crosstab query you MUST declare your parameters
and if any
other queries are used in the crosstab their parameters
must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

AH wrote:

Yes it is! At least that's how I have done a Parameter
Query before. I think the problem is I am trying to
combine a Crosstab Query with a Parameter Query.
Click to expand...
I
am
not
a vetran at this, that's why I am confused.

-----Original Message-----
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
expression.

Is the colon supposed to be within the square
brackets?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

I had some help from the wonderfull people
Click to expand...
here
at
the
Microsoft Newsgroup for creating a Crosstab Query
from
this table:

name, date, time, direction
----------------------------
user1, 8/8/03, 8:00, In
user1, 8/8/03, 18:00 Out
user2, 8/8/03, 8:05, In
user2, 8/8/03, 18:01, Out

The result of the Crosstab Query was like this:

name, date, In, Out
---------------------------
user1, 8/8/03, 8:00, 18:00
user2, 8/8/03, 8:05, 18:01

Which works out great with the exception of one
thing:
I
cannot use a parameter to search this query. If I
put "[Name:]" under Criteria in the "name" column, I
get
the following error:
The Microsoft Jet database engine does not
recognize "[Name:]" as a valid field name or
expression.

Thanks for the help



.

.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top