Solved

LIKE and Column(1)

Posted on 2014-03-14
7
355 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
7 Comments
 

Author Comment

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

error
0
 
LVL 40

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access report groups with sums 5 24
Access Update Query 1 19
Dlookup MSACCESS 5 24
Access VBA, adding Progress Bar in code to allow execution. 7 27
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now