run time error 3075 Syntax error

[ramprat]

Hi everyone,

This one is stumping me really good. The query entered in my filter below will work as a stand alone query in Access but I keep getting a syntax error when I try to run it through a button. I thought it could have something to do with the single quotes around my text criteria but I tried double quotes as well. If anyone can help I'd be so grateful. I actually had it working once and then went and added the criteria about (Count_Stations .STATUS) = 'Active' and (Traffic.COUNT_ STATUS) = 'Updated' and then it wouldn't work. I tried removing these and still no luck. The syntax is copied right out of the SQL view in Access and it's working fine there.


Thanks Ramprat

DoCmd.OpenForm "Count_Stations_Form", acNormal, "", "", , acNormal
DoCmd.ApplyFilter "", "SELECT Count_Stations.PCID " & _
"FROM Counties INNER JOIN ((Count_Stations INNER JOIN AADT ON Count_Stations.PCID = AADT.PCID) " & _
"INNER JOIN Traffic ON Count_Stations.PCID = Traffic.PCID) ON Counties.CNTY_FIPS = Count_Stations.CNTY_FIPS " & _
"WHERE (((Count_Stations.STATUS) = 'Active') And ((Counties.DMA_NUM) = [Forms]![login_form]![DMA_textbox]) " & _
"And ((Abs(([Traffic]![COUNT] * [Traffic]![HOUR_FACTOR] * [Traffic]![DAY_FACTOR] * [Traffic]![MONTH_FACTOR] * [Traffic]![YEAR_FACTOR] - [AADT].[AADT]) / ([AADT].[AADT])) * 100) >= 20) And ((Traffic.COUNT_STATUS) = 'Updated'))"

[ChipR]

First, I'd remove the WHERE clause altogether and make sure it ran. Then I'd try removing all the unneccesary parentheses from the WHERE clause. I've been using two double quotes (""txt"") instead of single quotes, but either one should work as far as I know.

[Stewart Ross]

Hi. I have added code tags before and after your code for clarity.

One problem is the use of the form control references, which work OK in the query editor but fail when run from code (as the Jet database engine does not see them as valid field names). You should substitute the values of the controls in the place of the control names, as shown for one such example below. If the form control reference is a string value, use single quotes before and after the reference as shown in the second version :

"WHERE (((Count_Stations.STATUS) = 'Active') And ((Counties.DMA_NUM) = " & [Forms]![login_form]![DMA_textbox]) & _
'
' above version for numeric values, below for strings
'
"WHERE (((Count_Stations.STATUS) = 'Active') And ((Counties.DMA_NUM) = '" & [Forms]![login_form]![DMA_textbox]) & "'" & _

I also think that using an arithmetic expression involving a division in your Where clause is just asking for trouble - what will happen when your divisor is 0? The place for the computation is within the query, not in its where clause.

-Stewart

[ramprat]

Thanks Chip. I tried getting rid of everything from the "Where" on and still got the same error?!? So I know it isn't the quotes

Stewart thanks for adding the code tags (I'll be sure to do that in the future) and for your suggestion but since I've removed everything from the Where on and thus removed any references to form controls I've still got the problem so I don't know if that was the problem. What is really aggravating is that at one point around noon this actually worked until I added in the additional criteria about status = active and count_status = updated. After that I couldn't get it to work again even with removing the criteria.

Is it one of those things that is staring me in the face and I can't see it?

[Stewart Ross]

Talking about staring in the face - the apply filter command is meant to be a WHERE clause but without the word WHERE. It is not valid syntax to have the whole SELECT statement in there!!

When I first looked at your post I simply saw the SELECT and thought you were building an SQL statement in code. ApplyFilter does not use the full SELECT statement - see the help entry reproduced below.

ApplyFilter Method

The ApplyFilter method carries out the ApplyFilter action in Visual Basic.
expression.ApplyFilter(FilterName, WhereCondition)
expression Required. An expression that returns one of the objects in the Applies To list.
FilterName Optional Variant. A string expression that's the valid name of a filter or query in the current database. When using this method to apply a server filter, the FilterName argument must be blank.
WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

-Stewart

[ChipR]

Good catch Stewart :)

[ramprat]

Stewart,

Does that mean that since my data comes from multiple tables I'd be better off to create a query that combines the tables and selects the PCIDs that I want my form to be filtered to and simply use the DoCmd.ApplyFilt er "Myquerynam e" , "" command? If this does work, Is there a better way to do this that doesn't involve creating and saving a query?

Thanks for your help and patience with this.

[ramprat]

I tried creating a select query to select only the PCIDs that I need and used the query in the Do.Cmd.ApplyFil ter command but it didn't work because my form uses data from several tables so access kept asking for me to enter the parameter value for data in the other tables. It seems to me that if I create a temp table of the PCIDs that I want filtered in my form there should be some way to use that table as a filter for my form. Am I wrong? This doesn't seem like it should be that hard yet it is. If I can figure this out I'll be extremely happy.

Thanks for any help

[ChipR]

I haven't used the ApplyFilter, but couldn't you use that select query string as the form's recordsource?

[ramprat]

I'm thinking of doing that. I just wonder if it's possible to use one query as the recordsource when someone is opening the form to do one task and another when they are opening the form to do another task. Essentially it would be the same query except one would limit which of the complete list of records would be displayed.

[ChipR]

You can set the recordsource dynamically. I set many of mine in the Form_Open depending on OpenArgs. So different buttons can open the form with different OpenArgs and you can set the recordsource accordingly. You should also be able to do it from the function where you open the form.

docmd.openform "frm"
Forms![frm].recordsource = strSQL

[ramprat]

Thanks Chip. I dynamically set my recordsource using the me.recordsource = myquery. I put this in the Load Event of the form. It seems to work now except that some of my controls on the form are now locked or not editable when the form uses my specified recordsource. Some of the controls are editable though. The recordsource is simply a query that combines fields from about 4 tables and limits the records based on a calculation in the query. Any idea why some would not be editable now?
Thanks for all your help and Stewart's as well.

[ChipR]

I honestly have no clue why that would happen. The source data must correspond to fields in your query or you would get the #errror, so it doesn't make much sense. Are the control properties still showing enabled and not locked?

[ramprat]

ChipR
Yes the controls are still enabled and unlocked. I've noticed that I have 6 controls that now appear locked and their underlying data all comes from the same table (count_stations ). They are the only controls on the form that reference this table so there's a common thread. Although the record source for the form is a query the query gets this data from the various tables obviously. The other controls based on other tables all work fine. The Count_Stations table is in a way my main table. All of the other tables are linked to it by a connection between common fields. Could this have something to do with it?

The funny thing is that I set the recordsource of the form depending on inputs from another form and if we apply one record source then everything is fine, if we apply the second then I get the locked controls. When I created the form originally I used the form wizard and based it on a query which also uses the count_stations table among others. That is the record source that is working, this new record source based on a new query is the one that's pooching on me.

One day I hope things actually work first time

[ramprat]

ChipR with the RecordSource I've set below any control based on a field from the count_stations table appears to be "Locked" the rest of the controls based on the other tables are fine. Could it have something to do with the Joins below? I hope I've used the code tags correctly.

[code ]If Forms![login_form]![Task_Combo_Box] = "20/20 Checks" Then

'filters the records that the count station form will display if someone chooses 20/20 checks from the login form

Me.RecordSource = "SELECT counties.dma_nu m, counties.county _nam, Count_Stations. PCID, Count_Stations. CSTATIONID, Count_Stations. COUNTING_S, Count_Stations. STREET, " & _
"Count_Stations .CROSS_ST1 , Count_Stations. CROSS_ST2, Count_Stations. CNTY_FIPS, Count_Stations. STATUS, Markets.MARKET_ NAM, " & _
"Count_Station_ Attributes.ROAD _CLASS, Count_Station_A ttributes.ONE_W AY, Count_Station_A ttributes.Direc tion, " & _
"Count_Station_ Attributes.TOWN _NAME, Count_Station_A ttributes.DESIG NATION, Count_Station_A ttributes.LATIT UDE, " & _
"Count_Station_ Attributes.LONG ITUDE, AADT.AADT, Traffic.traffic id, Traffic.COUNT, Traffic.COUNT_T YPE, Traffic.COUNT_D ATE, " & _
"Traffic.COUNT_ SOURCE, Traffic.COUNT_D OCUMENT, Traffic.COUNT_S OURCE_ID, Traffic.COUNT_S OURCE_ID2, Traffic.COUNT_D ESCRIPTION, " & _
"Traffic.HOUR_F ACTOR, Traffic.HOUR_FA CTOR_SOURCE, Traffic.DAY_FAC TOR, Traffic.DAY_FAC TOR_SOURCE, Traffic.MONTH_F ACTOR, " & _
"Traffic.MONTH_ FACTOR_SOURCE, Traffic.PRE_05_ GROWTH_FACTOR, Traffic.YEAR_FA CTOR, Traffic.YEAR_FA CTOR_SOURCE, " & _
"Traffic.COUNT_ STATUS, Traffic.COMMENT , Traffic.Checked , Traffic.APPEAL_ COMPLETE, Traffic.Usernam e, Traffic.TIMESTA MP " & _
"FROM (Markets INNER JOIN (Counties INNER JOIN ((Count_Station s INNER JOIN AADT ON Count_Stations. PCID = AADT.PCID) " & _
"INNER JOIN Traffic ON Count_Stations. PCID = Traffic.PCID) ON Counties.CNTY_F IPS = Count_Stations. CNTY_FIPS) " & _
"ON Markets.DMA_NUM = Counties.DMA_NU M) INNER JOIN Count_Station_A ttributes ON (AADT.PCID = Count_Station_A ttributes.PCID) " & _
"AND (Count_Stations .PCID = Count_Station_A ttributes.PCID) " & _
"WHERE (((Count_Statio ns.STATUS)='Act ive') AND ((Traffic.COUNT _STATUS)='Updat ed') " & _
"AND ((Counties.DMA_ NUM)=[forms]![login_form]![dma_textbox]) AND ((Abs(([Traffic]![COUNT]*[Traffic]![HOUR_FACTOR] " & _
"*[Traffic]![DAY_FACTOR]*[Traffic]![MONTH_FACTOR]*[Traffic]![YEAR_FACTOR]-[AADT].[AADT])/([AADT].[AADT]))*100)>=20))"

Else


End If
Me.Requery [ /code]