I would like to run a query which takes a code which contains a mixture of number and letter and returns all the number before the first letter. I was using the left function (=Left(([Codes],2)) but sometimes there is one number sometime two. The desired results are shown below;
7pol2try36 = 7
12cet9fre55 = 12
10yea3gtr77 = 10
Is this possible?
I need a field to return the first 4 letters of the last name of our clients. What expression will do this?
View 1 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 tried to use a bit of code so return the Last Year and WeekNo in my database, to select some records but�. It currently fails, I think this because it returns the values as text and will then not compare to a numeric field. Is there an easy way to change this code so it returns numbers.
qryCurrentWkYr
Code:
SELECT
Right(yw,2)
AS week,
[Code].....
I've been trying to create a query that will take a date and return the week number of the date.
My original date is formatted m/d/yyyy
I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year
I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)
but the I get a result of 53 in this query, when I expected/need it to be 1.
I'm trying to return a list of students in a particular team who have not attended a certain number of sessions at a gym, between two dates. For some reason i get the error. At most one record can be returned by this subquery. (Error 3354).
SELECT [Student ID], Count(*) AS ["Number of Times Attended"]
FROM Gym_Attendance
WHERE [Gym Date] BETWEEN [Forms]![SV_Attendance]![txtStart].Value AND [Forms]![SV_Attendance]![txtEnd].Value
AND [Student ID] Like
(SELECT [Student ID]
FROM Student_Sports
WHERE [Sporting Team] = [Forms]![SV_Attendance]![lstTeam].Value)
GROUP BY [Student ID]
HAVING Count(*) < [Forms]![SV_Attendance]![txtNo].Value;
The big issue is wanting to create the following records in a subform every time a new record in the subform is added. I can populate the Ticket field and know how to cast fields as strings to append them (so I can get the Cylinder field). The output is used to populate a table called Cylinders. My question is:
How do I create a field of letters (or numbers) corresponding to the row in the subform without continuing from the previous letter (or number) in the Cylinders table?
Ticket
Letter
Cylinder
1589
A
1589A
1589
B
1589B
1600
C
1600C
1600
D
1600D
1600
E
1600E
how to write out the query Im trying to create so I'll create an example:
Field: Drivers Names
I want to know how to create a query that will calculate the # of drivers names which starts with, A, B, C etc.
I want my query to be:
Drivers names first letter # of first letters
A 10
B 19
C 15
how I can create such a query
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
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 a need to strip of letters from a string but i needs to look for / as the length would change, below is an example of the data I am working with
record 1 example ) REP/1349/999/426066/XX/9
record 2 example ) REP/UDKBS01N/1/448174/XX/
what i need to extract is
1) = 1349
2) = UDKBS01N
I need to get the information between the first / and the second / is there a function in access to get this.
I have a query that holds data based on a field. If the field [Device In] is "TimeStation-1" in TblTime for example it holds "AV" in the field [House]. Trouble is some fields are blank and when this is so I want it to pull the last two letters from the [Notes] field. I have attached the database. The query is [QryDeductionsandSleep Ins].
View 4 Replies View RelatedHi guys and gals,
I have compiled a very simple database to track staff and training audit requirements for my company.
I am 57 thick as 2 short planks and need some help please.
I wish to ask a text box called "Score" to return a number 2 if a yes/No box is "NO" and a number "1" if the Yes/No box is "YES".
I have looked at the help sections of Access and thought maybe an "IIf" statment in the Validation Rule section of the "Score" box might do the trick.
However, i haven't got a clue as to what the coding should be written like.
Can anybody help me please.
Basically i want to say
"If YES/NO = NO then SCORE = 2 but If YES/NO = YES then SCORE = 1"
Told you i was thick
I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.
I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)
This query produces the error given in the title of this thread, whilst the following works:
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)
This related query also works and shows a load of -1s and 0s correctly
Code:
SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND
But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.
I have a datasheet form listing prospect information.
Each prospect is assigned a position, there can be more than one prospect with the same position.
I'm trying to get a text box to lookup the prospects position and search the remaining rows for matching positions and return the total number found.
This data does not need to be stored anywhere, just a reference value for users to look at.
I am wanting to make a text box on a form, to return a number (amount of records returned by a query)basically so if the query returns 5 records, the text box on the form will show"5",
Lets say the form is called "A", and the query is called "B" .How do I put this in the source control of the textbox ? < if this is right too ?
I'm using Dmax() to return a max number in a field which I then want to inc by 1 for a new record. Dmax is returning 999. I believe Dmax therefore thinks it's a text field. So where do I change this to a number field?
View 2 Replies View RelatedI am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
I am looking up a product based on a unique code in tblProducts...I need to return one of two values
If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields
for example
tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue
query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'
Hi All,
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
Thanks in advance:D
Cheers,
Karv
I am having an issue with a select query as I want one column to show the MAX and "00" value of the same Column. How do I go about having this task completed.
For example, the column has 00,01,02,03,04,05,*1,*2,*3,*4,*5 so I want a query that will return values of "00" and "05" on this example
00
05
Further example, this is what I have now
SELECT qryPR34_UNION.PR34001, qryPR34_UNION.PR34002, qryPR34_UNION.PR34003, qryPR34_UNION.PR34005,
qryPR34_UNION.PR34006, qryPR34_UNION.PR34007, qryPR34_UNION.PR34013, qryPR34_UNION.PR34014,
qryPR34_UNION.PR34098, qryPR34_UNION.PR34099, qryPR34_UNION.PR34100, qryPR34_UNION.PR34101
FROM qryPR34_UNION
WHERE (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0")
AND ((qryPR34_UNION.PR34006)<"0") AND ((qryPR34_UNION.PR34099)>0));
On the WHERE command, I need field PR34005 to give me the "00" value or the MAX value (on my example the result will have to give me records that have "00" and "05" value assigned on PR34005
I have a query which is a max sum of sales for several sales reps. (If that makes sense?)
Sales Rep, Total Sales
1, 100
2, 200
3, 500
4, 50
5, 150
I need to create a report which delivers the sales rep with the most sales and only that sales rep
e.g.
3,500
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C
123456 AAAA 1/8/13
123456 BBBBI 1/8/13
123456 CCCC 1/8/13
123456 DDDD 1/8/13
123456 EEEEEE 3/10/13
123456 FFFFFF 3/10/13
123456 GGGG 3/10/13
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
I have tried the max & last functions, yet no success.
My setup is simple, it consists of two tables:
Employee (one)
Position (many)
So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.
I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?
I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.
Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.
E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography
I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned
I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
View 1 Replies View Related