TempVars not recognised in CrossTab Query

[David S]

Hi . I am trying to filter a crosstab query using tempvars! e.g.From a table 'Paid' Field "PayDate" where criteria "between [TempVars]![SeasonStart] AND [TempVars]![SeasonEnd]" i.e. [TempVars]![SeasonStart] = #1/9/2011# and [TempVars]![SeasonEnd] = #31/8/2012#.

The engine states [TempVars]![SeasonStart] not recognised, except when I use exactly same syntax in a Select query there is no problem. e.g. I have tried creating a sub query 'qPaidSub' using a Select query (using the above filter) and then referencing the sub query in the CrossTab, but still receive the same 'not recognised' problem.

I guess crosstab queries are sensitive to using variables, but as new starter to Access I am unable to find a workround (after 3 days ). Many thanks in anticipation.

NB I would prefer a non VBA solution if possible as I have a longer term hope to publish results on the web!

The SQL looks like the following:
TRANSFORM Sum(Paid.Amount) AS SumOfAmount
SELECT Paid.ClubRef, Sum(Paid.Amount) AS [Total Of Amount]
FROM Paid
WHERE (((Paid.PayDate) Between [TempVars]![SeasonStart] And [TempVars]![SeasonEnd]))
GROUP BY Paid.ClubRef
PIVOT DatePart("m",[PayDate]) & DatePart("yyyy",[PayDate]);

 

[vbaInet]

Crosstabs are ok with Functions:

TRANSFORM Sum(Paid.Amount) AS SumOfAmount
SELECT Paid.ClubRef, Sum(Paid.Amount) AS [Total Of Amount]
FROM Paid
WHERE (((Paid.PayDate) Between [COLOR=Blue]GetSeasonStart()[/COLOR] And [COLOR=Blue]GetSeasonEnd()[/COLOR]))
GROUP BY Paid.ClubRef
PIVOT DatePart("m",[PayDate]) & DatePart("yyyy",[PayDate]);

So create two functions (or one) to get the start and end values from the tempvars.

 

[Uncle Gizmo]

I think you have inadvertently answered your own question! If you see this line here:

.e. [TempVars]![SeasonStart] = #1/9/2011# and [TempVars]![SeasonEnd] = #31/8/2012#.

Note that the date string is enclosed in hash tags....# ---- Like: #31/8/2012#

So you need to duplicate this in your text string, in other words put hash tags around the TempVar,

 

[vbaInet]

So you need to duplicate this in your text string, in other words put hash tags around the TempVar,

Not quite Uncle Gizmo.

References to TempVars or controls on forms are not recognised in Crosstab queries. Functions are.

However you could use Eval():

TRANSFORM Sum(Paid.Amount) AS SumOfAmount
SELECT Paid.ClubRef, Sum(Paid.Amount) AS [Total Of Amount]
FROM Paid
WHERE (((Paid.PayDate) Between [COLOR=Blue]Eval("[/COLOR][TempVars]![SeasonStart][COLOR=Blue]")[/COLOR] And [COLOR=Blue]Eval("[/COLOR][TempVars]![SeasonEnd][COLOR=Blue]")[/COLOR]))
GROUP BY Paid.ClubRef
PIVOT DatePart("m",[PayDate]) & DatePart("yyyy",[PayDate]);

... but I'm not a fan of that.

 

[apr pillai]

1. Create a temporary table (TEMP) with two date fields: SeasonStart and SeasonEnd and with a single record.
   
2. Enter start, end dates into it.
   
3. Define the criteria as shown below:

BETWEEN (SELECT SeasonStart FROM temp) AND (SELECT SeasonEnd FROM temp)

 

[vbaInet]

apr pillai,

You may have not read the suggestions posed in posts #2 and #4. Why create a temp table for such a simple problem?

 

[Uncle Gizmo]

Not quite Uncle Gizmo.

References to TempVars or controls on forms are not recognised in Crosstab queries. Functions are.

Well I'm blowed! As you can tell, I didn't know that! I thought all SQL statements were simply strings? Is there any explanation for this difference? The difference between ordinary queries and crosstab queries?

Or is it the same for ordinary queries?

Forget my ramblings, the correct question is are tempvariables available to ordinary SQL string statements?

 

[David S]

Hi again. This was my first post and I am really impressed with speed and knowledge content of your replies. Many thanks indeed to you all.
Particular thanks vbaInet, I thought I was going mad … “References to TempVars or controls on forms are not recognised in Crosstab queries.”.
Why Microsoft do not publish these fact in a more obvious way is beyond me! {or do they? }To my untrained eye it appears they do not want to admit [a very small] failing in an otherwise excellent product!
Uncle Gizmo – Yes, I had tried wrapping the tempvars in '#' [in various guises]. As you have probably guessed, I am very new to this and self-taught! Am however a bit confused with your last post? I think that tempvariables do work in ordinary ‘select queries ………… and therefore SQL? Can you elaborate a little more for me as it helps my learning curve?
vbaInet – The Eval() solution works perfectly. Brilliant! Thanks Although am a little worried why you say “….. not a fan”?
Your first post is very interesting to me, but I do not want to use functions [in VBA] as I think they are not allowed in Sharepoint for the web – or, again, have I confused? If so, please could you elaborate further on how to write the function/s [compatible with sharepoint]?
Regards David

 

[Uncle Gizmo]

Uncle Gizmo – Am however a bit confused with your last post? I think that tempvariables do work in ordinary ‘select queries ………… and therefore SQL? Can you elaborate a little more for me as it helps my learning curve?

I am trying to move along my own learning curve as it were! I haven't used tempvars, myself as I don't really see the need. There are already many ways of providing such things which are much safer in my opinion, in particular I'm thinking of a Class.

I am interested in your observation that tempvars appear to work in ordinary select queries, I can see no reason why they shouldn't work in a crosstab query? The point is the answer may very well reveal some significance about the nature of MS Access which is what I am interested in.

The other thing I noticed about temporary variables when I first had a look at them was that they don't appear to convert correctly when you convert a macro to VBA? They only appear in the conversion as a string identifying the temporary variable name. I can't quite recall the details, and I would suggest your own investigation if this is of significance to you.

 

[vbaInet]

In more complicated subqueries and crosstab queries references to controls or tempvars prove difficult. In some cases tempvars would work but in others it would. The first thing I do is to Eval() the parameter and if that works I would create a function for handling it. Just preference

Now the usual workaround, which I should have mentioned earlier, is to add the TempVars in the Parameters list of your crosstab query and that normally solves the issue (without even using Eval or a function). So basically, a parameter is not recognised in a simple crosstab query if the parameters are not explicitly defined in the Parameters list which defines the Data Type of the parameter.

 

[David S]

That's it! I needed to add the TempVars in the Parameters list of my crosstab query. I would never have got there alone. Many thanks indeed.

 

[vbaInet]

Glad we could help!

 

[RenaG]

Hi vbaInet,

This answers a question I posted to you on another thread

http://www.access-programmers.co.uk/forums/showthread.php?t=221191

I got the Eval() to work. But you said that isn't the best way. I tried creating a Parameters list by just putting:

[Forms]![frmRaceEthnicityDateParm]![BegDate]

But it didn't like it.

The other thing you recommended is using a formula. I don't know how to create a formula.

Could you give more directions on how to do these two things?

Thanks so much!
~RLG

 

[vbaInet]

Hi vbaInet,

This answers a question I posted to you on another thread

I got the Eval() to work. But you said that isn't the best way. I tried creating a Parameters list by just putting:

[Forms]![frmRaceEthnicityDateParm]![BegDate]

But it didn't like it.

The other thing you recommended is using a formula. I don't know how to create a formula.

Could you give more directions on how to do these two things?

Thanks so much!
~RLG

It would make perfect sense to continue on your own thread. I'll answer on that.