Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

LIKE and Column(1)

Posted on 2014-03-14
7
Medium Priority
?
365 Views
Last Modified: 2014-03-15
Experts, can I use column(1) like I am using it below?  

DoCmd.OpenForm "Project Datasheet subform_3", acFormDS, , "[End User] Like '" & Left(Me.txtEndUser, 4) & "*' Or [UltimateEndUser].column(1) Like '" & Left(Me.txtEndUser, 4) & "*' Or [UltimateEndUser] Like '" & Left(Me.txtEndUser, 4) & "*'", , acDialog

[UltimateEndUser] is a number field with row source of:
SELECT tblEndUser.EndUserID, tblEndUser.[End User] FROM tblEndUser ORDER BY tblEndUser.[End User];

I didnt want to have to drag in the tblEndUser into the record source of the Project Datasheet subform_3 and thought I could use column(1) instead.  maybe I am using it wrong or there is a better way.  If column(1) simply cant be used like I am then I will drag the table into the record source.  

thank you
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:pdvsa
ID: 39930315
this is the error I get

error
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39930360
What do you mean by column(1)? Do you want to extract the first character from UltimateEndUser?
0
 

Author Comment

by:pdvsa
ID: 39930662
Sharath, thanks for the comment.  What I mean by column(1) is extracting the name and not the ID. You can see from the row source qry that column(0) is the idm and the column(1) is the name of the EndUser.  Let me know if that makes sense.

Thanks
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39930715
if [UltimateEndUser] is a number field then why is the rowsource 2 fields? is this a rowsource for a dropdown box?

You'll probably need to join the other table so you can filter by the required field
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1800 total points
ID: 39931192
Thanks to Microsoft's attempt to make life easier for users by creating links within the data tables themselves, you are falling into a trap.  [UltimateEndUser] is a single field, do o't confuse it with a listbox or combo that you see when you look at the table in design view.Example of lookup fields
In order to use the "columns" associated with the table that is linked to the [UltimateEndUser] field, you will need to create a query, and physically join the table you are trying to query above with the table that is being used as the 'lookup table' (probably tbl_People or tbl_Users or something like that).  Once you have created this join, and added the field that normally shows up as Column(1) as a separate field in the query (you don't actually have to include it in the query results), you can then refer directly to that field, not the Column(1).

Personally, I would strongly recommend that you remove the "lookup" feature in your table and simply display the value of the [UltimateEndUser] field (select TextBox rather than ListBox or Combo Box in the Display Control property on the Lookup tab in your table design.
0
 

Author Closing Comment

by:pdvsa
ID: 39931511
Thank you Dale.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39931625
glad I could help.

<begin rant>
This "feature" bugs the h**l out of me, especially when I'm trying to fix someone else's database and don't realize they have utilized this feature.  You open the table and look at it, and it displays a text value, so you go looking for that text value and cannot find it, you don't even see the numeric value unless you go looking for it in table design and realize that the lookup feature has been used.

In my opinion, it is far better to simply create a query to view the data in the related field than to use this "feature" and have it confuse the h**l out of you.

</end rant>
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question