Solved

LIKE and Column(1)

Posted on 2014-03-14
7
361 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 450 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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