I just want to get like if i have criteria like ;
Excellent
Good
Fair
Poor
Then what i need that in table if i select excellent then in query it should display 5, same if select good then 4, then fair 3 and so on..
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
View 14 Replies View RelatedI have a query where I use the Month function to get the month number of funded loans. So for example if a loan funded 06/01/07, my field would display 6. I have this for the whole year of 2006 giving me values of 1-12. I have put in the criteria <=[What Month?] for my month field so that the query will prompt me for the month value I want and give me that plus any month previous. The problem is that when I put in 12 for the criteria, it only returns 1, 10, 11 ,12. It is grouping/sorting the values based on the first character. So any value that starts with 1 is automatically less than any value starting with 2.
How do I fix this?
I need to automatically generate a 5-character value for my Business Key. Without any user interaction.
2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).
The Sequential Number must reset on each new day.
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
I am looking to round numbers to specific values 2, 4 and 6.
I was thinking of a code in a query something like:
if <3 but >0 then 2
if <5 but >3 then 4
if >5 then 6
can anybody convert this into queery code, not SQL tho because I'm rubbish at that. I just want to type it into the criteria on my field box on the query.
I also just tried to do it with:
IIf(>0 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0))))
but this only seems to return results of 4 and doesn't show all entries, like entries with no value entered. I also think i'm missing out the numbers 3 and 5 because it's less than and greater than but not the number itself:
if 5 or < or > (to include the 5)
thanks
Ok I've now got
IIf(<1,0,IIf(>1 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0)))))
which I think is doing roughly what I need but i have stumbled on a new problem.
These results are based on the average result of another query but i need to add a section before the results are averaged that says
if(=0 or no entry then all results are 0 for that unit else avg but iif(<1 etc. (the original statement above)
Thanks for your help
I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.
View 14 Replies View Relatedi would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.now data in my table are like below
Quote:
No option1 1 100 2 145/Mechanical/0800 3 120/electrical/1620 4 131/mechanical/0200/dw-001
Now I like to make a query can extract text between first and second character "/" like below: No option1 discipline
Quote:
1 100 null 2 145/Mechanical/0800 Mechanical 3 120/electrical/1620 electrical 4 131/mechanical/0200/dw-001 mechanical
I have a field called "SC" with a value 13-251. I need to remove the "-" ending up with 13251. I tried using, as a starting point, Mid(String, Start, Length) with no success. I found this in another topic, and thought this might be close to what I was looking for. I sure did not know how to use it. When I selected the run command I got the following response:
Query must have at least one destination.
I tried a few different things like adding an extra field and different pointers but I doubt if I'm even standing in the batters box.
I have a weight field that I've been populating with the weight and the unit of measure. I want to seperate those into 2 different fields. I've created a new field called UnitofMeasure and now I need to go back and remove all the instances of 'oz' and 'lbs' from the first field.
Is this possible with SQL?
Im linking to sql across ms access and I have all records uppercase.I need to set StrConv with first character uppercase and every other lowercase which is not problem.
View 5 Replies View RelatedI'm creating a string with an IIf statement and placing a comma between values. How do I delete the last comma at the end of the resultant string?
View 2 Replies View RelatedI have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.
Example:
Field 1: THE
Field 2: 1234
Field 3: BOAT
Field 4: 0001
End Result: THE 1234BOAT0001
Need to add 7 spaces to equal 22 characters.
Fields 1, 2, and 3 can vary in number of characters.
In the Table I have in the trailer database has, among many, 2 fields called 'Maximum Length' and 'Minimum Length'. Say, the data in these fields could be 5 and 4 respectively. I would like to create a search box where I can type in any length such as 4.4 and the database would return the trailer that can fit this length. (Sot the searched value is between the Min. and Max. length) What would I need to type into the query box and the search form to type in the length?
View 6 Replies View RelatedIs there any code to be able to be used within a query to locate the 1,2 or 3 digit numbers?
such as 1 ,11, 111 - 2, 34, 834 etc...
I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".
The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to
MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))
I have the following code and I am trying to show a COUNT of duplicate ID numbers, so for example
ID ID_COUNT
2000 2
2000 2
2001 1
2002 1
2003 1
Code:
SELECT [DaisyServiceData-Import].*, Companies.CompanyName, Company_vs_Services.[Tariff Code], IIf([ClashForce]=True,"",IIf([ClashOveride]=False And [ClashClear]=False,IIf([DaisyServiceData-ClashExc].[DaisyServiceData-Import].[ID] Is Null,"",1),"")) AS ClashExc, IIf([Sell Price]="","",IIf([SellRateForce]=True,"",IIf([CompanyName] Is Null,"",IIf([SellRateOveride]=False And [SellRateClear]=False,IIf([Total Cost]>[Sell Price]+0.001,1,""))))) AS SellRateExc, IIf([Tariff Code]='1',[T1Rate],
[Code] ...
A GROUP BY query will not work for me as the recordset needs to remain updatable.
I currently have a calculated field that will result in either a whole number or a decimal. From there, I need to set up a field that identifies if the previous field is either a whole number or a decimal.I have tried several different formulas, but always seem to get an error or a query that does not return any results.
View 3 Replies View RelatedI have an INSERT query like so:
Code:
INSERT INTO table1 (column1, column2, column3, column4, column5)
SELECT (field1, field2, field3, field4, field1 & "" && field2)
FROM mastertable
According to the format desired by a client, column5 from table1 needs to have field1 and field2 concatenated and represented as two-digit numbers if they're a one digit (has a leading zero).
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01
I have created a database for my hobby of being an (American) football official. I have one table of officials and another for matches to track the matches that I have worked. There are 3-7 positions that officials can work in any one match.
I want a query that will tell me which officials I have worked with and in how many matches (in total regardless of position). I have created a union all query to give me a list of officials names each time they appear. Rather than giving me the result as the names it comes back as the ID numbers from the Officials table.
The SQL query is:
SELECT Ref FROM Matches WHERE Ref Is Not Null
UNION ALL
SELECT Umpire FROM Matches WHERE Umpire Is Not Null
UNION ALL
SELECT LineJudge FROM Matches WHERE LineJudge Is Not Null
UNION ALL
SELECT HeadLine FROM Matches WHERE HeadLine Is Not Null
UNION ALL
SELECT BackJudge FROM Matches WHERE BackJudge Is Not Null
UNION ALL
SELECT SideJudge FROM Matches WHERE SideJudge Is Not Null
UNION ALL SELECT FieldJudge FROM Matches WHERE FieldJudge Is Not Null;
The field used in each position is a full name field ([Surname] & ", " & [Name])
I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...
View 10 Replies View RelatedI have a list of what should be sequentaia numbers, but I am finding instances were a number seems to be missing..Is there a way to query the whole dataset and find all missing instances without having to trawl he list?
View 4 Replies View RelatedI have a table that looks like this:
caseID
date
value
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc
caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
Any ideas??
I have a query to create in access based off a query already created in SQL
The SQL query converts a 4 digit number into a date using this code:
CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN LEFT(OPDT, 2) ELSE RIGHT(OPDT, 2) END AS SMALLDATETIME)
OPDT is a digit number in text format. The function converts 9606 into 1996-06-01
What is the access method in order to this?
I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc. Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)
891
833.5
######
I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891. How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.