Queries :: Locating All 3 Digit Numbers
Dec 19, 2014Is 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...
ADVERTISEMENT
Queries :: Converting Four Digit Numbers To A Date
Jul 30, 2014I 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?
Help With 4 Digit Numbers To Date Format?
Dec 31, 2006Hello,
I have extracted 4 digit numbers from a long strings of characters and numbers which I have identified as date. Some are inputted as
mmdd (example: 1129)
ddmm (example: 2310)
I have to calculate the number of days between the start and end dates. I tried formatting as date, but I can't get it to work.
Any suggestion?
Thanks!
How To Limit Account Numbers To 4 Digit Numeric Value
Mar 28, 2015On a form, is it possible to have #### appear in the form box when it loads and then the user would type a 4 digit number in place of ####.Also, for the table associated with that form, how do I limit account numbers to a 4 digit numeric value?
View 3 Replies View RelatedForms :: Edit / Entry Form That Uses Combo Box Lists To Select Different Segments Of 32 Digit Account Numbers
Aug 7, 2015I have a database I am using to record financial transactions. I have a transaction edit/entry form that uses combo box lists to select the different segments of my 32 digit account numbers. The issue I am running into is that when I enter a value value in the first box/field the form jumps to the very first record.I then can use the navigation buttons to get back to the last record, and all the subsequent boxes/fields work fine without jumping to a different record.
View 5 Replies View RelatedQueries :: How To Remove A Digit From String
Jul 29, 2014I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?
Queries :: Separate 6 Digit Data In Same Field
Jun 10, 2015In same field Im saving every time 6 digits data and i need to separate them. (It was not my fault. They created DB like this... )
For example my data is
123412(main-field) and i want to see them 1234(field1) 12(field2)
Queries :: Convert 19 Digit Number To Hex String
Mar 4, 2015I 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"
Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers
Aug 12, 2014I'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.
Locating A Record On Click
Oct 16, 2011I am design a limousine dispatch database and it would really be benificial to me if I could do the following:
I have a split form I use to make reservations and input all details. These details need to be changed often.
There is a report that shows our reservation #'s as well as trip details. I want to be able to click the res # on the report and have it being me to that res# on the reservation form so I can edit the details.
So far I go into design view right click the res# field go to properities and then build event. I do Open Form and then am lost from there (fairly new to access)....
Locating Multiple Entries In Query
Mar 3, 2006Hello, I have searched the forums and found plenty of information about eliminating duplicate values. What I would like to be able to do is run a query and have it show me only the records that have the field "serial number" that contain the same number. That is, I want to be able to query and see which serial numbers have been entered more than one time. The serial number is not the primary key, this database keeps track of product repairs and is used to log them in by the primary key serial number/date. Thanks in advance for any help. Tim
View 2 Replies View RelatedLocating Form In Specific Location On Screen! Please Help
Nov 25, 2005hi guys,
would someone please tell me if it is possible to locate the form in a specific location on screen, e.g. starting from top left corner or basically have the form open in centre of screen?!
I have created my database in Access and now trying to make the forms appear on screen as neet as possible. I have a main form which is on screen all the time and it should cover the whole screen. I have set the size of the form but everytime i open it it doesnt cover the screen as it does not start from the top left corner!
i also have a few pop up forms which i want them to appear in the middle of screen. how can i do this?!!!
i would appreciate any help or suggestions on alternative soloutions!
Thanks
Locating A Backend And Automatically Creating The Links For Link Tables
Aug 25, 2006Is there a way to just select like through a find dialog box, the location of a databases backend location and just automatically create all the links for the link tables...
The reason Im asking, is my boss wants the database to be on his laptop in the event that the network is down, and also so that he can work away from the company's location but I was showing him the implications of having it installed locally on his machine should his laptop go down....
So now I'm wondering if i could
1. do some automation that allows him to just choose the location of the databases backend that he would be working from, be it on the network or on his machine
and
2. using both backends to update or overwrite each other depending on which he was working from or did updates to..
Really would love all the help and input I can get on this... Im willing to work on developing a sample for this if nothing is exactly already out there so that it can be reposted cause i think this would be very useful to everyone's database applications.
Queries :: Searching For A Value Between Numbers
Nov 23, 2014In 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 RelatedQueries :: Order By Letters And Then Numbers
Dec 31, 2013I 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))
Queries :: Count Duplicate ID Numbers
Sep 15, 2013I 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.
Queries :: Identifying Whole Numbers Vs Integers
May 2, 2013I 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 RelatedQueries :: Formatting Numbers To Have A Leading Zero
Apr 24, 2014I 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).
Queries :: To Get Numbers With Specific Character
May 12, 2014I 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..
Queries :: Extract 3 Numbers From A String
Jul 11, 2014I 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
Queries :: Union All Reverting To ID Numbers
Aug 12, 2014I 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])
Queries :: Rounding Negative Numbers Up To 0
Mar 13, 2015I 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 RelatedQueries :: Missing Sequential Numbers
Feb 25, 2014I 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 RelatedDetailed Record Numbers By Group In QUERIES
Oct 24, 2007I 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??
Queries :: Formatting Whole Numbers And Decimals Differently
Nov 26, 2013I 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.
Queries :: Postcodes - Return Only TEXT Before Numbers
Jun 17, 2015I have a number of short postcodes ie
S43
GU10
ME8
etc etc
I want a run query to return only first characters before the numbers ie..
S
GU
ME