Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Basic Access Functions

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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