Solved

Basic Access Functions

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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 view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 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

19 Experts available now in Live!

Get 1:1 Help Now