compile error

[JoeyB]

I have tried several things, but can not figure out what I am doing wrong here. What might be the thing that I am doing wrong? I have looked on the internet but can not really find an answer to my question.

Thank you

Edit: I have seen the grammar mistake, but it still didnt help. (ORDER BY was misspelled)

[ItsMe]

Just before ORDER BY you have an ampersand and a quotation that does not belong.

.Column(0) & _

Variables belong outside of the quotes in strings. They are concatenated.

[JoeyB]

Just before ORDER BY you have an ampersand and a quotation that does not belong.

.Column(0) & _

Variables belong outside of the quotes in strings. They are concatenated.

Saved me again thank you. Solved!

[JoeyB]

I do have another question. The airports can be seperated by a tier system. Tier 1 is capital city and international flights and tier 5 is regional without interstate flights and everything in between. For each Tier I want the total sum for regional, major domestic and international. This is to compare them with other tiers or airports. I put them in a query with criteria like: year 2012-13 and ofcourse the tiers. Now I got a query (SumTierQ) with 5 rows and their numbers. This one I want to use in the same way as the form in this topic. But it is giving me a syntax error for the FROM clause. Might it be you can not use a query with INNER JOIN?

[ItsMe]

Try building your query using the query builder. Might be a problem creating the join because of calculations. When you post your code, try posting the actual text. You can use the advanced options and click the # in the toolbar to automatically create code tags that will enclose your code, preserving the format.

You are creating an SQL string in your VBA module to reassign the recordsource of your form. You can SELECT query objects as well as table objects using your string. If you build a query the way you need it, you can include the query name in your SQL string.
strTier = "SELECT * from qryName...

You can include a where clause or build onto your string how you see fit.

When you are building your saved query with the query builder you can test a where condition by adding a column that is the same as a control from your form. You can do this by referencing the field and not the form and or control. So, if your combo column 0 is Field1 you can add Field1 to your query and test a where clause by typing a value in the criteria of your query object.

With that, when you look at your saved query's SQL you will see the WHERE clause in print. Just substitute your control's name when you transfer the SQL to your VBA string.

[rpeare]

You can also DEBUG.PRINT StrTier to see what the SQL statement is actually saying.

I'd suggest another method... as ITSME suggested, build your query in the query builder except substitute discrete values (actual values) in any field you want a criteria (in your case I don't know that you can use .column(x) as a criteria so you'd need an actual value) then when it's showing you results you expect cut and paste that SQL code into your VBA window then see where the differences are between what works and what isn't (your code) while substituting in actual field references as you go.

[JoeyB]

Try building your query using the query builder. Might be a problem creating the join because of calculations. When you post your code, try posting the actual text. You can use the advanced options and click the # in the toolbar to automatically create code tags that will enclose your code, preserving the format.

You are creating an SQL string in your VBA module to reassign the recordsource of your form. You can SELECT query objects as well as table objects using your string. If you build a query the way you need it, you can include the query name in your SQL string.
strTier = "SELECT * from qryName...

You can include a where clause or build onto your string how you see fit.

When you are building your saved query with the query builder you can test a where condition by adding a column that is the same as a control from your form. You can do this by referencing the field and not the form and or control. So, if your combo column 0 is Field1 you can add Field1 to your query and test a where clause by typing a value in the criteria of your query object.

With that, when you look at your saved query's SQL you will see the WHERE clause in print. Just substitute your control's name when you transfer the SQL to your VBA string.

Sorry but where can I find the query builder? Or do you mean the one when you go in design mode? And I am not sure where to find advanced options?
And btw. this was the syntax error.

[ssanfu]

It looks like your error is caused by TierSumQ (in RED):

....<snip> ON AirportTierQ = AircraftMovement.TierID TierSumQ INNER JOIN PassengerT ON <snip>....

The TierSumQ (in RED) shouldn't be there. (Looks like a copy/paste error.) It is not in the SQL that is in the first post.

[ItsMe]

Thanks for bumping this Steve.

As for the query builder, I am refering to the button at the top of the screen in the Ribbon. The one that creates quries and allows you to save them as a query object with a name.

There are other interfaces available too. You can build SQL strings for comboxes from a form's design view, for instances.

[JoeyB]

Thanks for bumping this Steve.

As for the query builder, I am refering to the button at the top of the screen in the Ribbon. The one that creates quries and allows you to save them as a query object with a name.

There are other interfaces available too. You can build SQL strings for comboxes from a form's design view, for instances.

That is clear then, wasn't sure we were talking about the same thing. I always use Design View. I do have to say, you also have the option query wizard, but it appears that it has been disabled or it is not functioning properly. Would you know more about this?

[ItsMe]

I am not familiar with the "Option Query Wizard". When I mention "Builder" I am refering to a GUI that is offered by Access to help the developer construct an object. This is done in the spirit of Rapid Application Development (RAD). Wizards are, in my opinion, a different animal. The wizard will ask relevenat questions and then create something that "best fits".

The basic query object builder is started by clicking here.





You can use a similar GUI to create SQL strings for controls like combobxes by clicking the elipses for the rowsource property.

.



.

[JoeyB]

I am not familiar with the "Option Query Wizard". When I mention "Builder" I am refering to a GUI that is offered by Access to help the developer construct an object. This is done in the spirit of Rapid Application Development (RAD). Wizards are, in my opinion, a different animal. The wizard will ask relevenat questions and then create something that "best fits".

The basic query object builder is started by clicking here.

.

I fixed it. VBA code wasnt needed for this form, since I only used one combobox. works fine now, and what you illistrated, is exactly what I always used
thanks

[ItsMe]

Good to hear!

[JoeyB]

Although I have put a solved on this thread, I encountered another compile error, and I better not make another thread with the same title ^^

It is regarding the code underneath. The compile error says: method or data member not found.
Could someone tell me what I am doing wrong, because I cant see it The highlighted text appears yellow in VBA.

Private Sub cboAirports_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT TierT.Tier, AirportT.Airport, AircraftMovementT.AirMovementMDAInbound, AircraftMovementT.AirMovementMDAOutbound, AircraftMovementT.AirMovementRegionalInbound, AircraftMovementT.AirMovementRegionalOutbound, AircraftMovementT.AirMovementINTLInbound, AircraftMovementT.AirMovementINTLOutbound, AircraftMovementT.AirMovementTotalTotal, PassengerT.PassengerMDAOutbound, PassengerT.PassengerRegionalInbound, PassengerT.PassengerRegionalOutbound, PassengerT.PassengerInternationalInbound, PassengerT.PassengerInternationalOutbound, PassengerT.PassengerTotalTotal, PassengerT.PassengerMDAInbound, YearT.Year, TierT.TierID, AirportT.AirportID" & _
"FROM YearT INNER JOIN ((TierT INNER JOIN (TierJointAirport INNER JOIN (AirportT INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) ON TierJointAirport.AirportID = AirportT.AirportID) ON TierT.TierID = TierJointAirport.TierID) INNER JOIN PassengerT ON (PassengerT.PassengerNrID = AircraftMovementT.AircraftMovementNrID) AND (AirportT.AirportID = PassengerT.AirportID)) ON (YearT.YearID = PassengerT.YearID) AND (YearT.YearID = AircraftMovementT.YearID) " & _
"WHERE TierT.TierID = " & Me.cboTiers.Column(0) & " AND AirportT.AirportID = " & Me.cboAirports.Column(0) & _
" GROUP BY TierT.Tier, AirportT.Airport, AircraftMovementT.AirMovementMDAInbound, AircraftMovementT.AirMovementMDAOutbound, AircraftMovementT.AirMovementRegionalInbound, AircraftMovementT.AirMovementRegionalOutbound, AircraftMovementT.AirMovementINTLInbound, AircraftMovementT.AirMovementINTLOutbound, AircraftMovementT.AirMovementTotalTotal, PassengerT.PassengerMDAOutbound, PassengerT.PassengerRegionalInbound, PassengerT.PassengerRegionalOutbound, PassengerT.PassengerInternationalInbound, PassengerT.PassengerInternationalOutbound, PassengerT.PassengerTotalTotal, PassengerT.PassengerMDAInbound, YearT.Year, TierT.TierID, AirportT.AirportID" & _
"HAVING (((YearT.Year) = '2012-13'));"


Debug.Print strSQL
Me.RecourdSource = strSQL
Me.Requery

End Sub

Private Sub cboTiers_AfterUpdate()

If Not IsNull(Me.cboTiers) Then

Me.cboAirports.RowSource = " SELECT QueryTier.AirportID, QueryTier.Airport" & _
                           " FROM QueryTier" & _
                           " WHERE TierID = " & Me.cboTiers & _
                           " ORDER BY Airport"
                           
Me.cboAirports = Null
Me.cboAirports.Requery

End If

End Sub

Edit: Saw the mistake I made. Spelling mistake. Now it has turned into a runtime error 3075. And same as before edit, the highlighted text is highlighted in VBA

Edit[2]: already solved it, VBA can be so easy sometimes...

[ItsMe]

When typing in the IDE I use all lower case when I know a method, property, variable, etc. is upper and lower case. After I finish typing in lowercase, Access will automatically correct the uppercase characters if I spell the name right.