Solved

LIKE and Column(1)

Posted on 2014-03-14
7
358 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 47

Accepted Solution

by:
Dale Fye (Access MVP) 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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