UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query With Subquery With Parameter From Form, Access 2016    
 
   
airdata
post Oct 6 2019, 09:19 PM
Post#1



Posts: 43
Joined: 24-October 09



Hi all

I am trying to streamline a database query.

This query retrieves records based on type code, where this type is derived from a type group which is specified on a form. The data is aircraft related and as a sample, I am wanting to retrieve data of all Boeing 747's built which are comprised of multiple type codes such as B741, B742, B743, B744, B74L, B748. The group and individual codes are stored in a table: "tblAircraftTypeCodes".

The below query criteria works and retrieves the correct records.
In (select [tblAircraftTypeCodes].[TypeCode] from [tblAircraftTypeCodes] where ([tblAircraftTypecodes].[TypeFamily] = "B747"))

When I add the reference to the form, nothing is retrieved.
In (select [tblAircraftTypeCodes].[TypeCode] from [tblAircraftTypeCodes] where ([tblAircraftTypecodes].[TypeFamily] = """ & [Forms]![frmTypeCode]![txtCode] & """))

I think there is a syntax issue. Any help will be greatly appreciated....

Thanks

Mark
Go to the top of the page
 
tina t
post Oct 6 2019, 09:45 PM
Post#2



Posts: 6,155
Joined: 11-November 10
From: SoCal, USA


what fields are in table tblAircraftTypeCodes?

QUOTE
this type is derived from a type group which is specified on a form

the name of the form control is txtCode, correct? is it a textbox control? or is it a combobox control? if the latter, what is the RowSource of the combobox?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
arnelgp
post Oct 6 2019, 09:49 PM
Post#3



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


In (select [tblAircraftTypeCodes].[TypeCode] from [tblAircraftTypeCodes] where ([tblAircraftTypecodes].[TypeFamily] = [Forms]![frmTypeCode]![txtCode]))

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
airdata
post Oct 7 2019, 01:09 AM
Post#4



Posts: 43
Joined: 24-October 09



Thanks arnelgp - that worked. I think I was overcomplicating things and getting confused with SQL...
Cheers

Mark
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 10:14 AM