By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Access 2002 - Query question

truthlover
100+
P: 107
Is there a way to write a query that will display only the first word and first letter of the second word in a string? If so, how?

If not, is there a way to get it to display only a certain number of characters?

I'm a vba novice. It seems like there should be a way, but all my attempts to search the answers in the help files or this forum have been unsuccessful.

Thanks!
Sep 2 '08 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello.

Certainly it is possible via calculated query field.
Split() and Left() functions will help you to achieve this.

Regards,
Fish
Sep 2 '08 #2

truthlover
100+
P: 107
Hi Fish,

I just looked that up in Access and other tutorials, but I just cant make any sense of their explanation. Sorry, but I really dont know VBA.

How would I code the whole first word and the first character of the second word?
How would I code only displaying the first 7 characters?

Thanks so much! (and thanks for your patience)
Sep 2 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. Split("John Doe", " ")(0) & " " & Left(Split("John Doe", " ")(1), 1)
  2.  
for the first

and

Expand|Select|Wrap|Line Numbers
  1. Left("John Doe", 7)
  2.  
for the second

to build a query on it place the code instead of field name in query and replace string argument with correspondent text field of your table

Regards,
Fish
Sep 2 '08 #4

NeoPa
Expert Mod 15k+
P: 31,487
While the second (Left('John Doe',7)) fits neatly into a query (SQL) I don't believe that is true of the first.

I'm a very heavy user of the Split function in VBA and particularly referencing a single element of the array value returned. I find I can only access this via VBA code though (not in SQL).

That's no reason you shouldn't benefit from the concept though. Simply design a Public function (must be defined in a module object (not class, form or report related)) which gets passed the string you need to parse.

Something similar to the following :
Expand|Select|Wrap|Line Numbers
  1. Public Function AccountName(strName As String) As String
  2.   AccountName = Split(strName, " ")(0) & Left(Split(strName, " ")(1), 1)
  3. End Function
Your SQL would then refer to the function :
Expand|Select|Wrap|Line Numbers
  1. SELECT AccountName([FullName]) AS AccName,
  2.        ...
Sep 2 '08 #5

truthlover
100+
P: 107
Hi Fish,

Thanks! I havent gotten around to trying it, but i'll let you know how it goes.
Sep 2 '08 #6

FishVal
Expert 2.5K+
P: 2,653
While the second (Left('John Doe',7)) fits neatly into a query (SQL) I don't believe that is true of the first.
....
You are right.
I think the problem is that Split() function returns array.
Sep 3 '08 #7

truthlover
100+
P: 107
Ok, I've tried to make this work, but something still isnt working. My lack of VBA and SQL knowledge leaves me unable to figure out why.

Using the first 7 characters is sufficient, so if someone can tell me how/where to put in the statement that would just pick up the first 7 characters of tbl_SurveyWorkOrder.CADTech I'd be grateful.

Here's my SQL statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_CrewSchedule.ScheduledDate, tbl_SurveyWorkOrder.CADTech, tbl_SurveyWorkOrder.ProjectID, tbl_SurveyWorkOrder.ShortName 
  2. FROM tbl_Projects INNER JOIN ((tbl_SurveyWorkOrder INNER JOIN (tbl_Crew RIGHT JOIN tbl_CrewList ON 
  3. tbl_Crew.CrewMember = tbl_CrewList.FieldCrew) ON tbl_SurveyWorkOrder.SurveyWorkOrderID = tbl_CrewList.SurveyWorkOrderID) 
  4. INNER JOIN tbl_CrewSchedule ON tbl_SurveyWorkOrder.SurveyWorkOrderID = tbl_CrewSchedule.SurveyWorkOrderID) 
  5. ON tbl_Projects.Project = tbl_SurveyWorkOrder.ProjectID WHERE (((tbl_CrewSchedule.ScheduledDate) Is Not Null 
  6. And (tbl_CrewSchedule.ScheduledDate) Between " & lngFirstOfMonth & " And " & lngLastOfMonth & "))
  7. ORDER BY tbl_CrewSchedule.ScheduledDate;
  8.  
Thanks!!


You are right.
I think the problem is that Split() function returns array.
Sep 4 '08 #8

NeoPa
Expert Mod 15k+
P: 31,487
Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_CrewSchedule.ScheduledDate,
  2.        Left(tbl_SurveyWorkOrder.CADTech,7) AS CADTech7,
  3.        tbl_SurveyWorkOrder.ProjectID,
  4.        tbl_SurveyWorkOrder.ShortName
  5. ...
Sep 4 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.