Solved

Basic Access Functions

Posted on 2012-03-27
4
359 Views
Last Modified: 2012-08-13
Hello,
Can you please help me with these functions as part of Access Queries.

1. All the data in a field called Sender Name are in the following format:
Smith, John A:(GenCo-Nuc)
The names vary but the part after the colon is the same
I need to get rid of the colon and what follows the colon.
How do I use a function to turn the above into Smith, John A.

I tried the function Replace but when I leave the field cell, it changes the format. Is there another function?

Replace([Sender Name], ":GenCo-Nuc","")

Becomes this:
Replace([Sender Name],([GenCo]-[Nuc]),"")

This also doesn’t work: FirstName: Left([Name],InStr([Name]," "))
Somehow the colon and the parenthesis mess it up.

2. Another field says, “I Have Reviewed My Quals Today: Daily Qual Check”. Can you please let me know what If function to identify the phrase I Have Reviewed My Quals Today and turn that into a Y otherwise N.

3. Again, create a new field filled with the first part of the following, the date: 3/27/2012 of
3/27/2012 2:22:15 PM

Thanks,
Dennis
0
Comment
Question by:u002dag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37774763
In general, multi-topic posts like this should be posted as three seperate questions.

But try this for 1:

Replace([Sender Name], ":(GenCo-Nuc)","")


2:

iif(Instr([YourField],1, "I Have Reviewed My Quals Today:") >0, "Y","N")


3.

Format(YourField,"mm/dd/yyyy")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37774784
for number 3, i suggest you use

DateValue([DateFieldName])

this will keep it as a Date/Time data type
unlike using Format([datefieldName],"mm/dd/yyyy") which convert the Date Type data into  Text
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37775110
Yep, for 3. DateValue is the function to use.

/gustav
0
 
LVL 28

Expert Comment

by:burrcm
ID: 37775954
No 1. A bit of lateral thinking. A million rows or less? Copy and paste it into Excel (2010), do a text to column with the : as the delimiter and paste it back. (Yes it will take a few minutes).

Chris B
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses

615 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