Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Basic Access Functions

Posted on 2012-03-27
4
Medium Priority
?
368 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 2000 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 52

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

963 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