Field not recognized

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
I'm using a field on a form to drive criteria for a query but I get the following message when I run the query. What causes this?

"The Microsoft Jet database engine does not recognize "my field" as a valid field name or expression."
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
I know what the problem is I just can't figure out how to fix it. The query is based on a cross tab query. According to Access help I need to set the query parameter values. However the criteria I'm using comes from a combo box in a form... I'm stumped.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows
I think you will probably need to post your code so that we can see what you are doing. If you are using code to build the SQL behind the scenes and runt he query, it should work.
 

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
scal1 is a pivot table. I'm assuming that's where the problem is coming from.

SELECT scal1.IBLITM, scal.IBSRP4, scal1.IBMCU, scal1.IMDSC1, scal1.SSTOCK, scal1.IBSTKT, scal1.MFDRQJ, static_calendar.[calendar_month/year], scal1.UCOST, scal3.ICLEAD, IIf(IsNull([AD]),0,[AD]) AS demand, IIf(IsNull([CF]),0,[CF]) AS forecast, ([demand]-[forecast]) AS var, Abs([demand]-[forecast]) AS abs_var, IIf((scal1!IBSTKT='S' Or scal1!IBSTKT='M'),28,[ICLEAD]) AS ltime, Round(Sqr([LTIME]),1) AS sqr_ltime
FROM (scal1 LEFT JOIN scal3 ON scal1.IBLITM = scal3.ICPROD) LEFT JOIN static_calendar ON scal1.MFDRQJ = static_calendar.julian_date
WHERE (((static_calendar.[calendar_month/year])=[Forms]![scal]![scalvalue]));
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows
Yes, I don't think you can reference Forms values like that in a query. You actually need to build the literal SQL code in VBA.

Here is a real simple example. Let’s say you have a combo box named “cboName” and you wanted to select all fields from “Table1” where the “strName” field was equal to the name you have selected in your combo box. Then the VBA code for that would look something like:
Code:
Dim strSQL as String
strSQL = “SELECT [Table1].* From [Table1] Where [Table1].strName=” & Chr(34) & cboName & Chr(34)
Note: The Chr(34) expression just puts quotes around your cboName field

If you want to see what you just built, you could use the line:
Code:
MsgBox strSQL
(I use this a lot when testing to make sure I am building the string correctly. I will often work backwards by building the query I want manually abd viewing the SQL code I need to build. Then I will try to replicate it in my VBA and use the MsgBox to verify I am building it correctly).

Now you need to assign the SQL string you built to query. This code will apply it to “Query1”:
Code:
CurrentDb.QueryDefs("Query1").SQL = strSQL

Now if you open this query (or any Form/Report based on this query), it will show you your new results based on the criteria you entered on your form.
 

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
I've referenced values in forms to be used as criteria before. What would be the difference this time? The cross tab query?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,074
Office Version
  1. 365
Platform
  1. Windows
I've referenced values in forms to be used as criteria before. What would be the difference this time? The cross tab query?
Not sure, I have never done it. Cross Tab Queries are a bit different, so they may have some other limitations.

In order to reference forms in your query, does your Form have to be open when you run the query? Just curious as to how that method works...
 

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
That's the stipulation. The form does need to be up to work. But for what I'm doing, I need to have the form up anyway. Usually what I'm doing is exporting data to Excel through a button (executing a macro) on said form. I can use a combo box on the form to select criteria, whether it be a month/year or a location.
 

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
It took me a while but I figured it out.

I had to set the query parameters to date/time for the field in the form [Forms]![scal]![scalvalue].
 

Forum statistics

Threads
1,181,055
Messages
5,927,862
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top