query format

[awake2424]

What is the correct way to enter Location: Combo1184.Column(1) in a query field? I am getting an undefined function error. Thanks

 

[apr pillai]

You cannot address the .Column(1) Property of the Combobox in a Query column or in criteria. But, you can reference the default bound column like:

 [Forms]![FormName]![COMBO1184].Value

OR

 [Forms]![FormName]![COMBO1184]

 

[awake2424]

I have a text box (Name) on a form in which the user selects a name, then I have a combobox set to Name.Column(1)... so when the user selects a specific name a coressponding address shows up, is the user selects a different name the address of that individual is displayed.

I need to use that code in a query, I am getting an enter parameter error when I try. Thanks.

 

[boblarson]

As it was said, you CAN'T use that syntax in a query. The way you can do this is to reference the TEXT BOX instead. But you wrote something confusing. You select from a textbox?

I have a text box (Name) on a form in which the user selects a name, then I have a combobox set to Name.Column(1)...

Are you sure that you don't mean you have a combobox to select a name and then the TEXT BOX uses the Column(1) part?

 

[awake2424]

You are correct, the combobox selects the name and the text box uses the Column(1) part. I am not a computer programmer just an everyday database user trying to make things more efficient for other users in the office. So how do I reference the text box in the query? Thank you for your help.

 

[bob fitz]

Hi awake2424,

If you have a control (textbox/combobox) that you have called "Name", then I think it would be advisable to change this because "Name" is a reserved word in Access and not changing it may cause you problems.

 

[awake2424]

Is there a limit in Access 2003 to the number of IIF statements that can be entered in a Field: of a query? Thanks.

 

[boblarson]

Is there a limit in Access 2003 to the number of IIF statements that can be entered in a Field: of a query? Thanks.

Yes, there is. I think you can get 7 nested IIfs. But I would ask why and see if there was a better way.

Also, your text box "Name" should be named txtName instead as NAME not only is an Access Reserved Word but it is one of the worst to use for the name of something as that can throw Access off quite badly since everything in the database has a .Name property.

I would change it.

Then you could use this as the criteria in the query:


[Forms]![YourFormNameHere]![txtName]

 

[awake2424]

When I use the code:

[Forms]![YourFormNameHere]![txtName]

it asks for a parameter to be entered. Is there anyway to avoid this?

Thanks.

 

[Anchoress]

Are you substituting your actual form name for the placeholder [yourformnamehere]?

 

[awake2424]

Yes I am entering my form name.

How does the query change names... so if John is selected the 1 is returned but if sara is selected then 2 is returned? Thanks.

 

[boblarson]

When I use the code:

[Forms]![YourFormNameHere]![txtName]

it asks for a parameter to be entered. Is there anyway to avoid this?

Yes, the part YourFormNameHere is what needs to be changed to your actual form name. And txtName needs to be changed to the name of the text box which is on your form, if it isn't named txtName.

 

[awake2424]

I guess I am not following.

On a form I have a ComboBox: txtName that works with a TextBox: =txtName.Column(1).... when the user selects the name John a 1 is displayed, but when a use selects Sara a 2 is displayed.

I am trying to get a code similar to that in a query, but am having issues. Thank you very much.

 

[boblarson]

I think you are confused. I think the best thing to do is to have you post your database and we dig in to it. I may not be able to get to it until Monday as I'm going to be on a mini-vacation starting in a couple of hours. But if I can't someone else might be able to help.

 

[JANR]

You cannot address the .Column(1) Property of the Combobox in a Query column or in criteria. But, you can reference the default bound column like:

 [Forms]![FormName]![COMBO1184].Value
 
OR
 
 [Forms]![FormName]![COMBO1184]

That's not entirely true, you can but you need to use Eval() function.

SELECT tblContact.ID, tblContact.MyName
FROM tblContact
WHERE (((tblContact.MyName)=Eval("forms!frmContact!cboContact.column(1)")));

But for queries if you can avoid using function calls then you should as this slows down execution of the query.

JR