By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

run time error 3075 Syntax error

P: 60
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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Count_Stations_Form", acNormal, "", "", , acNormal
  2. DoCmd.ApplyFilter "", "SELECT Count_Stations.PCID " & _
  3. "FROM Counties INNER JOIN ((Count_Stations INNER JOIN AADT ON Count_Stations.PCID = AADT.PCID) " & _
  4. "INNER JOIN Traffic ON Count_Stations.PCID = Traffic.PCID) ON Counties.CNTY_FIPS = Count_Stations.CNTY_FIPS " & _
  5. "WHERE (((Count_Stations.STATUS) = 'Active') And ((Counties.DMA_NUM) = [Forms]![login_form]![DMA_textbox]) " & _
  6. "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'))"
Dec 2 '08 #1
Share this Question
Share on Google+
19 Replies


Expert 100+
P: 1,287
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.
Dec 2 '08 #2

Expert Mod 2.5K+
P: 2,545
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 :

Expand|Select|Wrap|Line Numbers
  1. "WHERE (((Count_Stations.STATUS) = 'Active') And ((Counties.DMA_NUM) = " & [Forms]![login_form]![DMA_textbox]) & _
  2. '
  3. ' above version for numeric values, below for strings
  4. '
  5. "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
Dec 2 '08 #3

P: 60
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?
Dec 2 '08 #4

Expert Mod 2.5K+
P: 2,545
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
Dec 2 '08 #5

Expert 100+
P: 1,287
Good catch Stewart :)
Dec 2 '08 #6

P: 60
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.ApplyFilter "Myqueryname" , "" 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.
Dec 3 '08 #7

P: 60
I tried creating a select query to select only the PCIDs that I need and used the query in the Do.Cmd.ApplyFilter 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
Dec 3 '08 #8

Expert 100+
P: 1,287
I haven't used the ApplyFilter, but couldn't you use that select query string as the form's recordsource?
Dec 3 '08 #9

P: 60
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.
Dec 3 '08 #10

Expert 100+
P: 1,287
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
Dec 3 '08 #11

P: 60
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.
Dec 3 '08 #12

Expert 100+
P: 1,287
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?
Dec 3 '08 #13

P: 60
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
Dec 3 '08 #14

P: 60
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_num, 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_Attributes.ONE_WAY, Count_Station_Attributes.Direction, " & _
"Count_Station_Attributes.TOWN_NAME, Count_Station_Attributes.DESIGNATION, Count_Station_Attributes.LATITUDE, " & _
"Count_Station_Attributes.LONGITUDE, AADT.AADT, Traffic.trafficid, Traffic.COUNT, Traffic.COUNT_TYPE, Traffic.COUNT_DATE, " & _
"Traffic.COUNT_SOURCE, Traffic.COUNT_DOCUMENT, Traffic.COUNT_SOURCE_ID, Traffic.COUNT_SOURCE_ID2, Traffic.COUNT_DESCRIPTION, " & _
"Traffic.HOUR_FACTOR, Traffic.HOUR_FACTOR_SOURCE, Traffic.DAY_FACTOR, Traffic.DAY_FACTOR_SOURCE, Traffic.MONTH_FACTOR, " & _
"Traffic.MONTH_FACTOR_SOURCE, Traffic.PRE_05_GROWTH_FACTOR, Traffic.YEAR_FACTOR, Traffic.YEAR_FACTOR_SOURCE, " & _
"Traffic.COUNT_STATUS, Traffic.COMMENT, Traffic.Checked, Traffic.APPEAL_COMPLETE, Traffic.Username, Traffic.TIMESTAMP " & _
"FROM (Markets INNER JOIN (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) " & _
"ON Markets.DMA_NUM = Counties.DMA_NUM) INNER JOIN Count_Station_Attributes ON (AADT.PCID = Count_Station_Attributes.PCID) " & _
"AND (Count_Stations.PCID = Count_Station_Attributes.PCID) " & _
"WHERE (((Count_Stations.STATUS)='Active') AND ((Traffic.COUNT_STATUS)='Updated') " & _
"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]
Dec 3 '08 #15

Expert 100+
P: 1,287
Are the controls locked such that you can't set the cursor in them or just that you can't change the data? Do your tables have relationships set up with referential integrity? It doesn't seem like that would cause this kind of behavior but I don't have any other ideas yet.
Dec 3 '08 #16

P: 60
I can set the cursor in the control I just can't update the values. I do have these tables set up with referential integrity but that should only affect joined fields. Dop you think it may have something to do with the order of my joins in the code?
Dec 3 '08 #17

Expert 100+
P: 1,287
Just for fun, what's your other recordsource that works fine look like?
Dec 3 '08 #18

Expert 100+
P: 374
@ramprat
Hello ramprat,

The one thing that you have to keep in mind with regards to basing a form on multiple joins is, that if for any reason, the values that you're wanting displayed combines and/or calculate any value, those fields on your form will not be updated. Because in the eyes of Access, they are calculated fields.

That may be why some fields work because there is a one to one relationship between which record is being edited, and the value that you want to put into it.

The table count_stations, you may be either using a SUM function or a Group BY statement, Those are all possibilities as to why those items would be non-changeable.

I hope that helps,

Joe P.
Dec 4 '08 #19

P: 60
ChipR, Joe and Stewart,

Thanks for all your help. The reason that the controls based on the count_stations table were "locked" and the rest weren't was because the count_stations table is on the one side of a one to many relationship and queries based on two or more tables (in this case my recordsource is really a query) only allow edits to records on the many side of the relationship and not the one side. The way around this is to only include fields from the "one" side in the select part of the query. In the where clause you can use fields from the "many" side.

I got this information from an old Access manual that shipped with Office 97.

Thanks again guys
Dec 4 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.