Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

LIKE and Column(1)

Posted on 2014-03-14
7
Medium Priority
?
370 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 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 49

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 49

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

916 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