Invalid bracketing of name in a crosstab parameter query

[roonierella]

Hello.

I am working in Access 2010 and have setup a crosstab query. I want to use parameters and have setup a form from which the parameters will reference. Instead of the parameters being prompted through pop-ups, I want the query to use the open form as a reference. When in the query, I I key in the parameters and attempt to run the query, I receive the following message:

Invalid bracketing of name 'Between [Forms]![frmContactsBySSS]![StartDate] And [Forms]![frmContactsBySSS]![EndDate]'.

Any suggestions on what I am doing wrong?

Thanks.

[offie]

Are [StartDate] and [EndDate] text boxes or combos? If they are it should be:
Between [Forms]![frmContactsBySSS].[StartDate] And [Forms]![frmContactsBySSS].[EndDate]

[roonierella]

They are text boxes. If I key what you suggested (Between [Forms]![frmContactsBySSS].[StartDate] And [Forms]![frmContactsBySSS].[EndDate]) I get the same message.

[offie]

Well that's right so post your SQL, it case the error is actually somewhere else.

[roonierella]

PARAMETERS [Forms]![frmContactsBySSS]![SSSName] Text ( 255 ), [Between [Forms]]![frmContactsBySSS].[StartDate] And [Forms]![frmContactsBySSS].[[EndDate] ] DateTime;
TRANSFORM Count(tblContactLog.ContactID) AS CountOfContactID
SELECT tblContactLog.StudentNameID, Count(tblContactLog.ContactID) AS [Total Contacts]
FROM tblContactLog
GROUP BY tblContactLog.StudentNameID, tblContactLog.ContactDate
ORDER BY tblContactLog.StudentNameID
PIVOT tblContactLog.ContactType;

[offie]

Oh my god I'm so sorry you were right before(about the ! not .), I'm a little tired...

So I haven't really used cross tab tables, but I just make a quick simple one, adding criteria, and it came up as usual under WHERE, not PARAMETERS, how did you get that?
What I did was to select what I want to add my criteria to, total as where, and then add the criteria to that column
From what little I got from making a sample one... Why do you have count(contractID) in both the value and the row heading?

I'm thinking I might not know the answer to your question... Sorry!

[roonierella]

I appreciate your help.

Let me explain this a different way (I just re-evaluated my query).

I have a table to track contacts that staff members make with students. I want a crosstab query with the the student's name in the row heading [StudentNameID] and the type of contact made [ContactType] in the column headings. I want the query to count the number of records under each contact type (count of [ContactID]) to give the the number of contact types per student. Each student record in the contact log can only have one contact type, which is why I am counting the ContactID (my primary key value). I want one version of this query to have parameters set to only show data within a date range (between [StartDate] AND [EndDate]). I want a second version of the query that does the same thing, but with the name of the person who made the contacts [SSSName] as the first parameter and the dates as the second parameter.

The key is that I want to have the parameter portion executed through the form instead of the standard pop-up windows. It will be easier on the people who will be using the database if I do it this way.

[offie]

So this is what I came up with:
Obviouly you'll have to make a form

For the query, something like this, the last 2 fields can be for their own qeury

I don't have your data so I can't test it out but I think it should work, obviouly ignore the Exp1.. things
For the Where with SSSName, I don't know if you're using a combo or not but I added the wildcards so you can search for any part of the name

Make sure on your form to format the enter date text boxes, so that the dates are in the same format as the data

[roonierella]

That is very close to what I did. The SSSName is not a visible field; the entire database itself is for a school, and then a person(s) are assigned to that school. The form I set up had a combo box/list linking to the SSSNames already keyed in the database.

[roonierella]

OH! I don't know what I did but I got it to work. Thank you SO MUCH FOR YOUR HELP!!!! The query works when I access it through the form. I have a feeling once I dig deeper I may have another question. LOL

[roonierella]

Actually, I spoke too soon. I took out the SSSName and focusing just on the dates.

[offie]

How are you typing in the names? If it pops up automatically then there should be no reason for it to no be recognized. This may sound silly but did you name the text boxes?

Also, did this same query work before?

[roonierella]

They names come in up a combo list - they select the names in the form prior to keying in the dates.

I thought it worked because when I clicked "OK" on the form. But, to test, I added another record with someone elses name and I realized the query wasn't applying the parameters. I had to key back in and when I did, I got the error message.

Are you talking about the text boxes on the form? Yes, I named them; StartDate and EndDate.

[offie]

So what exactly is happening? The records are not being filtered? Is the format of the query correct?
I re-read the error message is is says "field or expression" both of which it is not supposed to be, it's not even in that row...
Try entering real dates (Between #1/1/2000# And #12/12/2013#) play around with that to see if that works. Maybe it has something to do with the form properties.

When you say added another record, does that mean you don't have data? If not I would make a quick set of sample data so that you can actually test the db.

[roonierella]

Actually, it will not let me run a parameter in the criteria field for ContactDate, period. *sigh*