Solved

Basic Access Functions

Posted on 2012-03-27
4
347 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
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 119

Expert Comment

by:Rey Obrero
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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
My experience with Windows 10 over a one year period and suggestions for smooth operation
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

929 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now