?
Solved

Fromating Fields

Posted on 2014-09-15
10
Medium Priority
?
122 Views
Last Modified: 2014-09-15
I including a Database with a query.  I need the query to do the following:
1.       Crte_DTTM-Format to be only Date
2.      Ver_NUM-If equal 1 the append AO
 If equal 2 the append GN
If equal 3 the append GO
If equal 4 the append CO
If equal 5 the append GNII
If equal 6 the append GNA
Thank you in Advance.
FormatDB.mdb
0
Comment
Question by:ca1358
[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
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 85
ID: 40323327
This looks more like a statement or work than a request for help.

Can you post your query that you have now, and perhaps explain what you've tried? The experts are here to help YOU do your job - not do it for you.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40323329
I have no real idea as to what you are asking.

However, the answer to part 2 seems to be:

CHOOSE(Ver_NUM,"AO","GN","GO","CO","GNII","GNA")
0
 
LVL 85
ID: 40323338
To help with your query:

You can format a DateTime field to show the Date portion only by using the Format function:

SELECT Field1, Format(YourDateField, "mm-dd-yyyy") AS FormattedDate FROM YourTable

You can also use nested IIF statements, or the Switch method, to show different values depending on the value in a field. For example:

SELECT Field1, IIF(Field2='AO', 'A', IIF(Field2='AB', 'B'), 'C') AS SomeField FROM SomeTable

To use Switch:

SELECT Field1, Switch(Field2="AO", "A", Field2="AB", "B", Field2="AC", "C") AS SomeField FROM SomeTable
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ca1358
ID: 40323347
I included the sample database with the queries that I am having problems with.  Name of Database is FormatDB.mdb

I have formated 200 fields, these two fields I am troubling with.  So just want some help Formating these two fields.
0
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 2000 total points
ID: 40323364
Here is what you need.

The Ver_Num would be:

Ver_Num: IIf([Table1]![Ver_Num]="1","AO",IIf([Table1]![Ver_Num]="2","GN",IIf([Table1]![Ver_Num]="3","GO",IIf([Table1]![Ver_Num]="4","CO",IIf([Table1]![Ver_Num]="5","GNII",IIf([Table1]![Ver_Num]="6","GNA","ERROR"))))))

see attachment.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40323374
For the date field you just needed to change the property to display the format as short date.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40323377
My answer is shorter - use the Choose function.
0
 

Author Closing Comment

by:ca1358
ID: 40323381
Thank you for your Help .
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40323382
As I re-read your request were you asking to update the field
Ver_Num to the values listed or did you
mean [Ver_Num] + Value as in
1AO
2CO
3GN
etc.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40323393
I don't recommend formatting Date fields in queries unless you are exporting the query as a csv or to excel.  It is best to format dates where they are displayed on reports or forms.

In a query if you need only the date part of a datetime field and you want the field to retain its property as a date so you can still sort it like a date or compare it to other dates, then use the DateValue() function.  The Format() function suggested by Scott actually turns a date into a string so it no longer sorts like a date or compares like a date although it is the function of choice when formatting is required due to its options.

As an example 1/1/2014 will sort BEFORE 1/2/2013 if the field is a string data type but AFTER if the field is a date data type.  Strings are processed character by character, left to right where as numeric fields (such as a date) are  aligned at the decimal point and sorted by order of magnitude so negative numbers sort descending first followed by positive numbers ascending.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…
Suggested Courses

766 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