Field not recognized

[engelwood]

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."

 

[engelwood]

Just figured it out.... I think.

 

[engelwood]

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]

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]

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]

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:

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:

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”:

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]

I've referenced values in forms to be used as criteria before. What would be the difference this time? The cross tab query?

 

[Joe4]

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]

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]

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].