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

tcneo

Member
Local time
Tomorrow, 06:45
Joined
Dec 7, 2021
Messages
68
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:

Code:
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.
 

Attachments

  • Test Planner 2022-01-20 CT.accdb
    2.8 MB · Views: 180

theDBguy

I’m here to help
Staff member
Local time
Today, 15:45
Joined
Oct 29, 2018
Messages
18,972
Hi. Try adding the parameters in the PARAMETERS header of your crosstab query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:45
Joined
May 7, 2009
Messages
16,394
see your form.
 

Attachments

  • Test Planner 2022-01-20 CT.accdb
    3.4 MB · Views: 166

tcneo

Member
Local time
Tomorrow, 06:45
Joined
Dec 7, 2021
Messages
68
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:45
Joined
May 7, 2009
Messages
16,394
i am not sure but i think you can only use .Text on Object that has Focus.
 

tcneo

Member
Local time
Tomorrow, 06:45
Joined
Dec 7, 2021
Messages
68
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 here to help
Staff member
Local time
Today, 15:45
Joined
Oct 29, 2018
Messages
18,972
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

Member
Local time
Tomorrow, 06:45
Joined
Dec 7, 2021
Messages
68
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

CID VIP
Local time
Today, 23:45
Joined
Jan 14, 2017
Messages
16,219
@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’m here to help
Staff member
Local time
Today, 15:45
Joined
Oct 29, 2018
Messages
18,972
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...
 

Users who are viewing this thread

Top Bottom