Syntax Error in Code

[riggsd]

I've looked at this so long that I can't figure out where the errors are. It's probably something simple though.

Would you folks please take a look at this and tell me where I went wrong please? I took a SQL Query and am trying to convert it to use in VBA instead.

stDocName = "frm_PPLforFlightRange"
DoCmd.OpenForm stDocName, , , "SELECT tbl_RDFVersions.CSCIName, tbl_RDFVersions.ADOID, " & _
"tbl_RDFVersions.RDFVersion, tbl_RDFVersions.RDFName, " & _
"tbl_PPLInstances.Instance, tbl_PPLInstances.InstanceDescription, " & _
"tbl_PPLInstances.PPLType, tbl_PPLInstances.FinalPartNumber, " & _
"tbl_PPLInstances.LIFFile, tbl_PPLInstances.PDDFile, " & _
"tbl_PPLInstances.RDFFile, tbl_PPLInstances.FirstFlight, " & _
"tbl_PPLInstances.LastFlight, tbl_PPLDetails.SQAMaturityLevel, " & _
"tbl_RDFVersions.tbl_Subsystem_ID, tbl_RDFVersions.BPFM, " & _
"FROM tbl_Flights INNER JOIN ((tbl_RDFVersions INNER JOIN tbl_PPLInstances " & _
"ON tbl_PPLInstances.RDFUID = tbl_PPLInstances.tbl_RDF_ID) INNER JOIN " & _
"tbl_PPLDetails ON tbl_PPLInstances.InstanceUID = tbl_PPLDetails.PPL_ID_Lookup) " & _
"ON (tbl_Flights.FlightUID = tbl_PPLInstances.LastFlight) AND " & _
"(tbl_Flights.FlightUID = tbl_PPLInstances.FirstFlight) " & _
"WHERE (((tbl_PPLInstances.FirstFlight.Column(3)) " & _
"BETWEEN '" & cmbFirstFlight.Column(2) & "' " & _
"AND '" & cmbLastFlight.Column(2) & "' " & _
") AND ((tbl_PPLInstances.LastFlight.Column(3) >= '" & cmbMidFlight.Column(2) & "' " & _
")) ORDER BY tbl_RDFVersions.CSCIName, tbl_RDFVersions.ADOID, " & _
"tbl_RDFVersions.RDFVersion, tbl_PPLInstances.Instance;"

 

[Mile-O]

It will probably help if you can say any more about the error.

On a quick glance, though, I have to say...

...you can't reference a table in this way:

"WHERE (((tbl_PPLInstances.FirstFlight.Column(3))


I'd also question the reason for needing to put such a long SQL statement into the VBA editor as the query is not dynamic. ie. the build stays the same, and creating a query in such a way increases the size of your database each time the query is evaluated and therefore requires a greater deal of compacting.

 

[riggsd]

It will probably help if you can say any more about the error.

It just says syntax error and when I hit debug it highlights the entire code, error code 3075 I think.

On a quick glance, though, I have to say...

...you can't reference a table in this way:

"WHERE (((tbl_PPLInstances.FirstFlight.Column(3))

Well how would I reference it and still get the column I need?

What about this:

"WHERE ((('" & tbl_PPLInstances.FirstFlight.Column(3) & "' " ) " & _

I'd also question the reason for needing to put such a long SQL statement into the VBA editor as the query is not dynamic. ie. the build stays the same, and creating a query in such a way increases the size of your database each time the query is evaluated and therefore requires a greater deal of compacting.

The query is dynamic. It pulls three values from three text boxes on a form that change each time the form is run based on three combo boxes. The reason I'm trying to put it into VBA is because I'm trying to get the data from the column 3 of the flight columns and I can't do that in a macro so I'm trying to do it in VBA.

How does this increase the size of the database if the results are never saved to the database?.

 

[pbaldy]

Looks like Mile-O-Phile is offline. I agree with both comments.

tbl_PPLInstances.FirstFlight.Column(3)

You are referencing a table (tbl_PPLInstances) and a specific field within that table (FirstFlight). There is no further breakdown of that field into columns. This would be the way to reference a combo or list box with multiple columns. This is certainly causing a syntax error.

Your query is NOT dynamic. A dynamic query is one in which the actual structure of the query changes based on user selections. In other words, perhaps different fields are used in the where clause. Depending on selections, the query may read

WHERE City = 'Whatever'

and the next time it's run it's changed to

WHERE Customer = 'Whoever'

In your case, that query will be exactly the same every time it's run. The fact that the values contained in the form fields will change is irrelevant; the query references the form fields.

Queries run from VBA are slower than saved queries, since they have to be compiled each time they're run. I would convert yours into a saved query (or leave it as one).

 

[riggsd]

Looks like Mile-O-Phile is offline. I agree with both comments.

tbl_PPLInstances.FirstFlight.Column(3)

You are referencing a table (tbl_PPLInstances) and a specific field within that table (FirstFlight). There is no further breakdown of that field into columns. This would be the way to reference a combo or list box with multiple columns. This is certainly causing a syntax error.

How is referencing a combo box in a table that has three columns different than referencing a combo box on a form with three columns?

The field in the table is a lookup combo box with three columns.

 

[pbaldy]

The field in the table is a lookup combo box with three columns.

No, it's not. If it's a lookup field, it holds the key value from another table, which is where your 3rd column probably is. Here's some info on lookup fields (not recommended, and you're experiencing one of the reasons why):

http://www.mvps.org/access/lookupfields.htm

 

[riggsd]

Thanks guys. I'm learning this by the seat of my pants and am just trying to understand how it works. Plus, I've been given a six month job and told to do it in two.

We have over 2000 records in just this one table and will be tripling it over the next few months, so I was trying to not only decrease the size of the table but also get rid of having to put the sort id and the flight in the same cell. Looks like I'll have to go back to the old ineffecient way of doing this.

We plan on converting to using an Oracle database but with the load that the coding team has, that willl be one to two years away.