Cross Tab Query doesn't recognise textbox value as valid field name or expression (1 Viewer)

[tcneo]

Good afternoon,

In the attached test planner, I created a crosstab query.

I wanted the query to return the results from a range of dates from 2 textboxes located in the Main Menu Form.

In the criteria for the crosstab query, I placed the following:

Between [Forms]![Main Menu]![txt_cross_from] And [Forms]![Main Menu]![txt_cross_till]

When I click on the Green command button in my Main Menu, the query will not run but gives the following error:

The Microsoft Access Engine does not recognise '[Forms]![Main Menu]![txt_cross_from]' as a valid field name or expression.

Is this particular to cross tab query?

I have able to use 2 textboxes for users to select the range of dates for select queries previously.

 

[theDBguy]

Hi. Try adding the parameters in the PARAMETERS header of your crosstab query.

 

[arnelgp]

see your form.

 

[tcneo]

see your form.

thanks!

I note that you added subroutines to the On Change event to both textbooks to save the value to 2 variables (dte1 and dte2).

Then the macro of the command button not only opens the query, it also SetTempVar to create 2 Tempvars which is used by the query.

Could the SetTempVar have taken the value directly from the textboxes (Me!txt_cross_from.Text & Me!txt_cross_till.Text) thereby bypassing the need to create dte1 and dte2?

 

[arnelgp]

i am not sure but i think you can only use .Text on Object that has Focus.

 

[tcneo]

Hi. Try adding the parameters in the PARAMETERS header of your crosstab query.

I'm not sure what you mean by that. I have textboxes for users to key in the From and Till date.

If I add parameters to the Parameters header, when I run a query, there will be an inputbox that pops up asking me to key in the value.

 

[theDBguy]

I'm not sure what you mean by that. I have textboxes for users to key in the From and Till date.

If I add parameters to the Parameters header, when I run a query, there will be an inputbox that pops up asking me to key in the value.

Not necessarily. It really depends on how you declare the parameters.

 

[tcneo]

Not necessarily. It really depends on how you declare the parameters.

i have no idea how to declare the parameters to achieve what i wanted to achieve. hence, my original post.

 

[isladogs]

@arnelgp has shown you how to use parameters to create the tempvars
Suggest the same method but using AfterUpdate rather than Change events
Change events get triggered after each action e.g. keyboard press. AfterUpdate only when action completed

 

[theDBguy]

i have no idea how to declare the parameters to achieve what i wanted to achieve. hence, my original post.

Ah, looks like somebody already showed you how. Cheers!

Sent from phone...