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!
8 1385
Hello.
Certainly it is possible via calculated query field.
Split() and Left() functions will help you to achieve this.
Regards,
Fish
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)
-
Split("John Doe", " ")(0) & " " & Left(Split("John Doe", " ")(1), 1)
-
for the first
and
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 32,556
Expert Mod 16PB
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,
-
...
Hi Fish,
Thanks! I havent gotten around to trying it, but i'll let you know how it goes.
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.
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: - SELECT tbl_CrewSchedule.ScheduledDate, tbl_SurveyWorkOrder.CADTech, 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 32,556
Expert Mod 16PB
Try : - SELECT tbl_CrewSchedule.ScheduledDate,
-
Left(tbl_SurveyWorkOrder.CADTech,7) AS CADTech7,
-
tbl_SurveyWorkOrder.ProjectID,
-
tbl_SurveyWorkOrder.ShortName
-
...
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB...
|
by: LesM |
last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002
SP3.
I have Progress table that is linked via ODBC into Access using OpenLink
Lite for Progress 9.0b. For over a year, using...
|
by: Odie |
last post by:
Hi
My employer sent me to school to learn acess 2002, I have started fast track
classes at a technical institute, although I'm learning alot, I seem to be
learning everything except what I need...
|
by: Neil |
last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is
upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server
at the same time. We're moving to SQL Server...
|
by: Frav |
last post by:
The Reps team have been experiencing that Access 2002 unexpectedly
quits
while working and also lots of Corruption Failures and "Record lock
can not
update" messages since the upgrade from...
|
by: Alan Mailer |
last post by:
Ok, my Access 2002 language writing skills are VERY rusty,. I would
know how to do what I need using SQL Server's "Coalesce' function, but
I don't have that available to me in the Access 2002...
|
by: rhobson2 |
last post by:
Hello,
I wrote a database applicaiton using Access XP (2002) and everything
has been working good for the client until they purchased a couple of
new computers with Access 2003.
The meetings...
|
by: mainframetech |
last post by:
We are having a problem with Access 2002 as opposed to Access 2000.
A multi-file query was set up under 2000 and tested good. Access is
connected through a link to an Oracle 9.1 database. The...
|
by: Sebastian |
last post by:
Hello
I develop my applications in Access 2002. My development system is
running Windows XP SP2 and I have Microsoft Office XP Developer.
Microsoft Office XP is at SP3.
I used Inno Setup (great...
|
by: tekctrl |
last post by:
Environment; Win2K PC with 1Gb of RAM and plenty of HD space running Access
2002
Issue; Access presents a blank data entry form in the Forms view when the
New Record icon is used. However, it...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |