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 Replies


ADVERTISEMENT

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

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

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 :: Convert Date To General Number?

Mar 20, 2015

Is it possible to easily convert 02/02/2015 15:30:00 to 201502021530 in a query?

View 4 Replies View Related

Queries :: Convert 19 Digit Number To Hex String

Mar 4, 2015

I need to run a query that converts a 19 digit number stored in a Short Text field to a hex string

Example of the string is "3310854670615838865" the result should look like "2DF284C801000091"

View 1 Replies View Related

Queries :: Convert String To Positive / Negative Number

Feb 24, 2015

I have an expression that converts a string to positive/negative number. It seems to be working fine, however, it doesn't convert "00000000001}" to -10. It shows as 10.

This is the formula that I am using in my query:

PJUNAccrual: IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",-1,1)*(Val([PJ_ACCRUAL_ADJ_X])/IIf(IsNumeric([PJ_ACCRUAL_ADJ_X]),100,10)+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "A" And "I",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-64)/100,0))+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-73)/100,0)))*100

View 1 Replies View Related

Queries :: Query To Count Number Of Different Records?

May 28, 2014

If I have a table called "Calls" with an autonumber and another field with names for stores like this:

1 Hobby Mart
2 Peter's Store
3 Hobby lobby
4 Hobby Mart
5 Peter's Store

How can I build a query to count the number of different store names?

For example, in this case I need the query to return the number 3.

View 7 Replies View Related

Queries :: Query To Display Number Of Records Per Value

Aug 9, 2013

I have a date field in a table. I wish to display in a query:Column A representing the month. Column B representing the number of records in the table corresponding to that month.

View 5 Replies View Related

Tables :: Convert Positive Number To Negative Based On Another Field In A Table

Jun 1, 2013

I am trying to create a table for income and expense [catergory] and would like the amount being entered into the [amount] field to have a negative or positive value on entering based on the category chosen.

so in my table I have

[catergory] which is chosen from a look up table ( which is either an INCOME or EXPENSE )
[amount] which is entered in the next field ( which has to return a positive or negative value based on the catergory choosen upon entering the data)

I would like to go one step further and indicate this negative value in red is possible in my FORM.

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 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 5 Replies View Related

Queries :: Crosstab Query - Counting Number Of Records For Each FY

Sep 19, 2013

I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.

View 7 Replies View Related

Queries :: How To Query Number Of Records Not Common To Two Tables

Aug 9, 2013

I have two tables, identical in terms of structure (number & names of fields etc.) but different in terms of content (records not necessarily the same, some may be common to both, some may only be present in one or other table)

I want to set up a query to count the number of records which are present in one table (Table A) but not in the other (Table B) using a common identifier field, and put the numerical result in a text box on a form (i.e. use the query as the control source for the text box)

I've managed to come up with the following which provides the list of ID's :

Quote:

SELECT tblA.CommentId
FROM tblA LEFT JOIN tblB ON tblA.Id = tblB.Id
WHERE tblB.Id Is Null;

Which will come in useful elsewhere but how do I adjust this SQL to only return the number of records? I think I have to use COUNT(*) somehow but I'm not familiar with it and not sure of the syntax?

View 2 Replies View Related

Queries :: Changing A Text Field Into A Number Field Using A Query

Jul 31, 2014

I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?

View 4 Replies View Related

Queries :: Calculating Total Number Of Records From Union Query

Jun 16, 2014

I am using the following UNION QUERY to total up equipment tested for a report.

SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]

[code]...

I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?

View 2 Replies View Related

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

Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query

Jul 15, 2014

I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?

View 9 Replies View Related

Queries :: Auto Number Field In Query?

Nov 22, 2014

I don't know why is it so complicated to add an auto number field in a query. I would like to add a increment number (auto number) on each line and then an auto number on each product.

View 7 Replies View Related

Queries :: Grouping Number Field Into Intervals In A Query

Jul 2, 2014

I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:

0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet

This is the query I'm coming up with so far

JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600

I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.

View 5 Replies View Related

Queries :: How To Convert Datetime Field To Date Field

Jun 8, 2013

I am trying to deduct a datetime field from a date field to decide how many days are in between. It doesn't let me. So do I need to convert the datetime field to a date field first? How would I do that?Or is there a function I can use to calculate the date difference?

View 2 Replies View Related

Queries :: Extract Number Out Of Field And Update Extracted Number To Another Field

Feb 6, 2015

791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.

View 1 Replies View Related







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