Convert Number To Text In Query

Dec 1, 2007

I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".

We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.

FiscalMonth and FiscalYear are both Long Integer number fields in the table.

Query field concatenated:
MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]

criteria line:
Between [Month-Year START DATE] And [Month-Year END DATE]

However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?

I assume that a Dash sign between the month and year are fine.
Thanks

View Replies


ADVERTISEMENT

How To Convert Number Date To Text In Access Query

Feb 12, 2015

how to convert number date to text date.

Ex. Date field: 02/12/2015

convert into:

Month field
February

Day Field
12

Year
2015

View 3 Replies View Related

Convert Text To Number

Aug 2, 2005

I have a table with the following fields:
Tool Text
Tool_ID Text
Description Text

The Tool_ID field holds a number if the tool provides a tool id. However, if the tool doesn't provide a tool id, the text "No tool ID given" is automatically placed into the field. Therefore, it needs to remain a text field for this reason and others that are too long to go into.
Anyway, I created a query that pulls only one specific tool which always provides a tool ID. Therefore, the results of my query will always have a number in the text field "Tool_ID".

Question: Is there a way to convert the text field to a number field within the query?

Thanks in advance!
~ Greg

View 4 Replies View Related

Convert Text Field To Number Field In Query

Nov 18, 2005

As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.

I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.

I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.

Is there any way to get round this?

Many thanks

View 2 Replies View Related

Convert A Text Field To A Number Field In A Query

Feb 28, 2007

Hi,

I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.

Thanks,

Jeff

View 3 Replies View Related

Convert Text To Number In Multivalue Table Field Without Data Loss

Oct 21, 2012

I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.

My Question is there a way to convert it without loosing all my data, as there are over 5000 records?

My Table with the multivalue field is called "Contacts", the field is called "Groups".

I tried update and append queries but I must be doing something wrong....

View 2 Replies View Related

Queries :: Convert Phone Number In Text Format And Remove Dashes Or Parenthesis

Aug 7, 2014

Is there a way to convert a phone number in text format into a number and remove any dashs or parenthesis. What function can I use ?

old format (951) 244-3011
new format 9512443011

View 2 Replies View Related

Convert Text Field To Number Field But Keep Leading Zeros

Oct 3, 2006

I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.

What formula do I use?

View 6 Replies View Related

Queries :: Can Query Convert A Field Value Into Number Of Records?

May 11, 2013

I build a database to manage some events. To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.

I create a table named EVENTS.

Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date

Then I create a query which returns the number of days the event will take place on.

EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]

I use an append query to insert this new record into the table EVENTS_MAIN.

I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.

Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.

Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?

View 9 Replies View Related

NEWBIE: How Do I Convert A Query To Text?

Mar 15, 2006

Hi,

I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.

PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC

What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.

So if say the cartridge code '12345' returns a list of printers such as;

printer 1
printer 2
printer 3

I want to take this data and turn it into a line like this;

printer 1, printer 2, printer 3

I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.

table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |


AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3

I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?

Thank you

View 4 Replies View Related

Convert Positive Number To Negative Number

Nov 22, 2011

This has to be a simple way to convert a positive number to a negative number. For example, convert 5 to -5. The field is quanitity and I wanted to convert to negative number. I tried to use negative = "-" & [quantity] but it doesn't work.

View 2 Replies View Related

Convert Number

May 27, 2005

i have data stored in my paid field as negative ex: 000000460p which is $40.00. how can i convert this into $40.00?
I know i have to make a query but how can i make access pick up all the negatives that end in P and transform them into a negativ e number?
thanks

View 12 Replies View Related

Convert String To Number

May 31, 2005

I have a field on my Report called Revenue. It is used in a calculation and there is a calculated field called Profit. Revenue-Cost=Profit. That all works.

The only thing that is wrong is that the Revenue field displays its numbers like this:

0.7
12.43
4.3
0

etc.

i.e. not formatted for currency. But I don't get the currency option under Format. It is based on a Query and this Query is based on a Linked Table.

Any suggestions on how to get it formatted as currency while still having the calculated field working?

View 3 Replies View Related

Convert Number To Hour

Jan 2, 2006

Hello Everybody...

I have a Question.
How do I convert a number (26) to 26:00 Hours.

The thing is.
I have a database that calculates the hours of overtime.

for example.

I work from 7:00 to 19:00.
Then I get 12:00 hours of total time.

The above is no problem.

But no I want to deduct the number 2 from 12:00 hours.

I hope to hear from u guys soon

View 2 Replies View Related

Convert Date To Number

Aug 9, 2007

I would like to convert a date field to a number in a query. What I am trying to do is take 2 date fields and see how many days they are apart. For example if one date is 3/1/07 and the other is 3/1/06 I would like 365 days to be returned. Thanks for any help!

View 2 Replies View Related

Convert Number To Date

Aug 9, 2007

I am importing data from a text file that contains dates in this format: 08012007

I am trying to convert the number to a date 08/1/2007 so that I can run date related queries.

View 4 Replies View Related

How To Convert Number To Date

Feb 28, 2008

I have a number, 20080101, that I need to convert to 01/01/2008.

How do I do this? I am not familiar enough with access to know.

Thanks.

View 6 Replies View Related

Convert Month Name To Number

Apr 9, 2008

Hi All - I would like the user to be able to import a month name (April for example) and then have access convert that name to a number (4) so that I can search for records that were opened before the month entered. I have been using:

MonthName(Month([CompletionDate])) = [Enter Month]

but

MonthName(Month([CompletionDate])) <= [Enter Month]

does not work.

Thanks!

View 1 Replies View Related

Convert Number To Month Name

Feb 4, 2008

Hi
I am struggling to get a number to change to a month name.
When the user chooses a month from a drop-down:

Value List
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"

I use the month number to run the query, ie: 1, 2, 3 etc.,
But I want the Month Name to use in the subsequent report.

I have no other data in the query other than a month number to use so I thought about an invisible text box that get's the month number and I could reference it from the report, but I just can't get the text box to get the month name from the drop-down.

=Month([Month4Query].[Column](2))

Maybe there is a better, easier way?

Martin

View 3 Replies View Related

Forms :: VBA - Whole Number Convert To Hh:mm

Aug 14, 2013

I have a textbox that I put in minutes as whole number. What i want is to put in AfterUpdate Event to convert that whole number to hh:mm....

View 4 Replies View Related

Currency Convert From Digits To Number

Nov 7, 2007

dear frnd,

i have a access program for printing cheque. i want to use a text box which automaticaly convert number ito digits from user input text box.

View 1 Replies View Related

How Can I Convert A Week Number To A Month?

Sep 29, 2005

Help!!
I have 2 fields in a table one for a year and one for a week number that data is being collected against.
I need to change these to a month and year for the report. Is there a way to do this??

View 1 Replies View Related

Convert Minutes From Number To Time

Mar 30, 2006

Is there any way to take a field that is a whole number and convert that number to appear like a time?

i.e.

120 shows as 2:00
106 shows as 1:46
25 shows as 0:25

View 2 Replies View Related

Queries :: Convert Number To Date?

Nov 11, 2013

i need to convert a number string to a date For example, 0820 i would need to convert it to august 2020 ( the date will always be in this century)

View 3 Replies View Related

Queries :: Convert String To Number

Dec 17, 2013

how I can convert the first column to show the number? I used the expression below, but it does not convert anything with a letter or a 0

Expr1: Val([SPPC TEST]![JULY_ACCRUAL_ADJ_X])

View 7 Replies View Related

Queries :: Convert Date To Number

Jun 18, 2015

Trying to convert a date in my query to a number. What function will convert 6/17/2015 to 42172? Tried datevalue dateserial

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved