473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Access 2002 - Query question

truthlover
107 100+
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
8 1385
FishVal
2,653 Expert 2GB
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
107 100+
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
2,653 Expert 2GB
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
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 :
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
107 100+
Hi Fish,

Thanks! I havent gotten around to trying it, but i'll let you know how it goes.
Sep 2 '08 #6
FishVal
2,653 Expert 2GB
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
107 100+
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
32,556 Expert Mod 16PB
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

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

Similar topics

14
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...
0
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...
1
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...
52
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...
2
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...
9
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...
3
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...
8
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...
0
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...
12
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.