Access 2002 - Query question

[truthlover]

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!

[FishVal]

Hello.

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

Regards,
Fish

[truthlover]

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)

[FishVal]

[code=vb]
Split("John Doe", " ")(0) & " " & Left(Split("Joh n Doe", " ")(1), 1)
[/code]

for the first

and

[code=vb]
Left("John Doe", 7)
[/code]

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

[NeoPa]

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 :

Public Function AccountName(strName As String) As String
  AccountName = Split(strName, " ")(0) & Left(Split(strName, " ")(1), 1)
End Function

Your SQL would then refer to the function :

SELECT AccountName([FullName]) AS AccName,
       ...

[truthlover]

Hi Fish,

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

[FishVal]

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.

[truthlover]

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_SurveyWorkO rder.CADTech I'd be grateful.

Here's my SQL statement:

SELECT tbl_CrewSchedule.ScheduledDate, [B]tbl_SurveyWorkOrder.CADTech[/B], tbl_SurveyWorkOrder.ProjectID, tbl_SurveyWorkOrder.ShortName 
FROM tbl_Projects INNER JOIN ((tbl_SurveyWorkOrder INNER JOIN (tbl_Crew RIGHT JOIN tbl_CrewList ON 
tbl_Crew.CrewMember = tbl_CrewList.FieldCrew) ON tbl_SurveyWorkOrder.SurveyWorkOrderID = tbl_CrewList.SurveyWorkOrderID) 
INNER JOIN tbl_CrewSchedule ON tbl_SurveyWorkOrder.SurveyWorkOrderID = tbl_CrewSchedule.SurveyWorkOrderID) 
ON tbl_Projects.Project = tbl_SurveyWorkOrder.ProjectID WHERE (((tbl_CrewSchedule.ScheduledDate) Is Not Null 
And (tbl_CrewSchedule.ScheduledDate) Between " & lngFirstOfMonth & " And " & lngLastOfMonth & "))
ORDER BY tbl_CrewSchedule.ScheduledDate;

Thanks!!

You are right.
I think the problem is that Split() function returns array.

[NeoPa]

Try :

SELECT tbl_CrewSchedule.ScheduledDate,
       [U]Left(tbl_SurveyWorkOrder.CADTech,7) AS CADTech7[/U],
       tbl_SurveyWorkOrder.ProjectID,
       tbl_SurveyWorkOrder.ShortName
...