How can a reformat a number from 23 to 23.00 in a query. The format functions reformats it correctly but converts the number to text. I need to format the number as 23.00 and maintain the value as a number, not as a text. How can I do this ?
View RepliesHello, Could someone advise me how to fix this little problem i have when importing a spreadsheet. My app outputs two columns into a spreadsheet with the following US date formats.The spreadsheet it generates is called dates.xls, and i need to re-arrange all the dates in the spreadsheet to a British date format dd/mm/yyyy and re-save it as dates.xls from a click of a button on my form.I know i can use text to columns in excel to fix this problem, but i want access to do it for me, preferbly in VBA, as in the future i may be working with multiple columns. Thankyou for any suggestions.20070612 2007011220070529 2007031920070530 2007040620070530 2007040620070530 2007040620070530 2007040620070530 2007040620070518 2007040620070525 2007041520070521 2007041920070608 20070430
View 2 Replies View RelatedI am attempting to use some external data to populate fields in my DB. I would like to reformat the ProductID in my DB to match a ProductName coming into my DB. We have many products that have 2 pieces. If the product does have two pieces, the external database has two ProductNames that look like this:
0000967
2000967
I would like my database to be able to pull information for each of these 2 part products (they will be displayed as one product in our DB, never to be seperated). I have a form that gives the exact measurements of the first piece by using the ProductName and matching my ProductID (0000967). I would now like to write a little VBA to populate some fields on the forms that are pulled from 2000967.
something like:
Forms!Product!txtField2 = DLookup("[FieldName]", "TableName", "[ProductName] = Forms!Product!ProductID")
The issue is that I need to only get the trailing 6 digits of my ProductID and add a 2 at the beginning. Is there a quick Format syntax I could use to accomplish this?
Getting close, but missing a step or two. Have a series of text files that need to be cleaned up and reformatted. The issue is two fold. Need to remove hard return wrapping so that each line is complete and then format to a table structure. I built code that imports fine if no wrapping. I tried this link and seemed to work, except in my implementation it is putting in delimiters, so that if a row of data has a comma, it treats that as a separate field rather than a single field. I thought that fixed width would mean that it ignores potential delimiter characters.
[URL]
The other problem is with the logic so that sometimes in is concatenating a new line to the old line. There may be exceptions due to typos and older files that used a tab rather than space, but seems in general like the indication of a wrap termination is that a new record will begin with either a number period and space or a letter (a, b, c, d only) a period and a space. The next part of building the table is tricky because the question needs to be repeated for each response as shown below in phase 2.
Example from plain text file (xxx.txt):
1. This is line one sentence
a. response a
b. response b
c. response c
d. response d
2. This is line two sentence
and it wraps
a. response a
b. response b
c. response c is
also wrapping
d. response d
----------------------------------
Phase 1 result should be:
-----------------------------------
1. This is line one sentence
a. response a
b. response b
c. response c
d. response d
2. This is line two sentence and it wraps
a. response a
b. response b
c. response c is also wrapping
d. response d
----------------------------------
Phase 2 result should be an access table with several cols:
-----------------------------------
Code:
QNO Question Response Answer
1 This is line one sentence a response a
1 This is line one sentence b response b
1 This is line one sentence c response c
1 This is line one sentence d response d
2 This is line two sentence and it wraps a response a
2 This is line two sentence and it wraps b response b
2 This is line two sentence and it wraps c response c is also wrapping
2 This is line two sentence and it wraps d response d
Here is the code that I'm trying to use for unwrapping.
Code:
Private Sub btnUnwrapText_Click()
'Create a new text file that has removed the wrapped text
'assumes that wrapping only occurs on second line. If
'wrapping exceeds two lines, code won't work. Haven't had
[Code].....
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 RelatedI do same thing each month and would like to make it easier/faster: is there a way to run many queries, one after another?
I cannot use macro, cause Run SQL is restricted to 255 characters and my queries SQL codes are longer than that.
Is there a way to run them in a row? Like run query1, wait till its finished, then run 2..3..4.
Hi,
Im trying to combine 2 fields below into one column.
ID - 001 (This is formatted in table using 000)
Sometext - "BLAH"
In the query I have written this ...
Newfield: [Sometext] & [ID]
But when I preview the query the data displays as "BLAH1" and not "BLAH001"
what should i do to correct this formatting issue?
many thanks!
I was about to post a question yesterday, but I found a solution myself and would like to share it. When a new column is computed in the query, there is no way to prescribe the data format of the computed column (=field =variable). It is done automatically by access and it can go wrong. I tried to use Format() function but it changes the look but does not change the type of the column. So what you have to do is to wrap your expression in a data convertion function. I used CDBL() as a wrapper for my expression.
I consider it as a bug of Access because in my situation I had a swich() function that took only numeric fields and produced only number but the data type of the column was set to TEXT. I noticed it in the pivot table later on , when I could only use Count() as aggregate function.
Good luck! and take care of data conversion by yourself, do not let it go automatically!
P.S. If anyone knows a better way to prevent such conversion errors, be my guest!!!
By
Maxim Ivashkov
http://www.4suc6.com
I have three tables the tblStudentInfo (holding the persons main data), the tblEducation (holding the students education records) and the refInstution (holding all university's and college information).I am pulling PersonID, name, etc from the tblStudentInfo, education records from tblEducation.
the problem is that in the query it displays the institution ID instead of the Institution name. How do i get the query/rpt to show the name instead of the ID?
On the form Institution this is pulled from the refInstitution table and the ID InstName etc is a combo box 0; 1.0; etc so the display on the form shows the name of the university instead of the ID number.
How do I use the Max() function to get only one number. And also what do I put in the parentheses?
View 14 Replies View RelatedI have a query the result of which provides me a date field in the format 12-Oct-2010.I want that this field data to be converted to corresponding numbers such as 40463.
View 5 Replies View Relatedi have a query with an inputbox for numbers [Enter Number]..if i want the query to return multiple numbers how would i do this? so the user could have 3 5 and 8 to search for, currently the [enter number] allows them to search for one at a time.
View 1 Replies View RelatedI have a linked table to Access 2010 which contains tracking numbers for shipments.When I link the spreadsheet to the Table, the FedEx tracking numbers are changed to for example - 9.813842152e+014 instead of 981384215196229. Is there anyway to change these back to its original format? I was thinking of creating a query where I could use Conversion Function, but it kept giving me error. Please note that both the UPS Tracking and FedEx tracking are in the same field.
View 8 Replies View RelatedI'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.
I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).
Person Referral
1 A
1 B
1 C
2 D
2 E
3 F
3 G
3 H
What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.
I'm on Access 2010 if it makes any difference.
AWB numbers consist of 8 digits. The rightmost digit is a Mod 7 check digit. For example: 1234567 returns a check digit 5, so the AWB number is 12345675. How do I design a query to validate the accuracy of an AWB number?
View 3 Replies View RelatedI have a normalised table containing the following fields:
<Unique ID> <Fund Name> <Date> <Return>
The data are time series denoting the monthly performance of investment funds. Funds can have any number of observations (e.g. March 1997 to June 2005). In addition, some funds can have performance gaps.
I would like to extract those funds that have a continuous performance history of 120 consecutive months between a specific start and end date. As an exmaple, the start date is July 1990 and end date June 2010:
- Fund A: performance history July 1995 to June 2005 (= 120 consecutive observations) INCLUDE
- Fund B: performance history July 1995 to June 2007 (> 120 consecutive observations) INCLUDE
- Fund C: performance history July 1995 to June 2000 (< 120 consecutive observations) EXCLUDE
- Fund D: performance history July 1995 to and June 2000 August 2000 to June 2007 (> 120 observations but gaps) EXCLUDE
- Fund E: performance history July 1985 to June 1995 (= 120 consecutive observations but only 60 after start date) EXCLUDE
how to build a query around this? Perhaps this needs implementation in VBA. Ideally, I would like to be able to select the start and end date dynamically and then run the query accordingly.
I just can't seem to get this one to work right. I've got the following query. I need to count the number of Null dates or show zero if there are no Null Dates.
Code:
SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, Count(qryNoticeResponseNew.[fldResponseSeen]) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;
Which is just counting the number of dates so far. It got me to thinking I need to do something like this.
Code:
SELECT DISTINCTROW qryNoticeResponseNew.fldNoticeID, IIf(IsNull(qryNoticeResponseNew.[fldResponseSeen]),1,0) AS fldCount
FROM qryNoticeResponseNew
GROUP BY qryNoticeResponseNew.fldNoticeID;
Which pops a "cannot have aggregate function in expression" error.
I have this formula for counting age groups
TOTAL 29 - 31: Count(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29<=31;1))
But id does not work, if I use between 29 and 31 also do not work
I need to get count (number) of all students that have 29,30 and 31 year of age.
I have a query with records,
I want a field named RECORD NUMBER beside it I want a consecutive number increment EG
RecordNumber - RecordDecription - RecordTitle
------------------------------------------
1 - TITLE001 - TITLE001
2 - TITLE002 - TITLE002
3 - TITLE003 - TITLE003
is there a formula i can use in my query to create this?
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 Relatedhow 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])
I have a table with:
A dDataID (sequential auto-number)
B dDateTimeEntered (date/time field)
C dFactoryOrder (text)
D dSpecificationID (number)
E dStartWeek (text)
F dIncreasingDecreasing (text, values are limited to "Increasing", "Decreasing", or "NA")
I need a query to look up the last 9 values where C, D, and E match variables from a form, then tell me how many "Increasing" values are contained within that set. I want to store the final value in a combo box on the form. I tried writing the code and this is what I got - design view attached as a pic and SQL below. It looks like the "Top 9" is sorting on the Expr1 field, which isn't what I want, but I'm not sure how to force it to use dDataID or dDateTimeEntered to figure out which records to use.
SQL:
SELECT TOP 9 Sum(IIf([tblData]![dIncreasingDecreasing]="Increasing",1,0)) AS Expr1
FROM tblData
WHERE (((tblData.dFactoryOrder)=[Forms]![frmDataEntryDisplay]![dFactoryOrder]) AND ((tblData.dSpecificationID)=[Forms]![frmDataEntryDisplay]![dSpecificationID]) AND ((tblData.dStartWeek)=[Forms]![frmDataEntryDisplay]![dStartWeek]));
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 RelatedI'm converting minutes to display as HH:MM but have an issue when the number is negative. How to write this so I don't end up with a negative in front of the minutes?
Format([Variance]60,"00") & ":" & Format(([Variance] Mod 60),"00")
i.e. -193 gives me -03:-13, whereas I would like -03:13
In table1 students lectures data are stored as:
Code:
ID studentname lecturesubject lecturedate
1 steve politics 3/2/2013
2 jack politics 3/2/2013
3 steve math 5/2/2013
4 steve politics 7/2/2013
5 jack politics 8/2/2013
6 steve math 8/2/2013
7 jack politics 9/2/2013
8 steve politics 10/2/2013
9 steve math 11/2/2013
10 steve physics 13/2/2013
11 jack politics 15/2/2013
We need a query in which for each student is allowed to preset a limited number of lectures on a specific subject. For example each student can provide only two lectures on a subject and any more lectures on the same subject presented by him will not be counted. In the above example records 7 8 9 and 11 will not be shown in the query.
Consider the table
Date Shift-1 Shift-2 Shift-3
1-1-15 OP-1 op-2 op-3
2-1-15 op-4 op-3 op-5
3-1-15 op-2 op-1 op-4
4-1-15 op-3 op-6 op-1
5-1-15 op-5 op-1 op-3
How can calculate the duties of operators (Op-1 to Op-6)?
Possible result is
Op-1 = 4, Op-2 = 2, Op-3 = 4, Op-4 = 2, Op-5 = 2, Op-6 = 1