Hey, I know this is probably a simple thing to do, but I couldn't find it anywhere on the web so hopefully someone here can help.
I have a column of UPC codes which are a bunch of #'s. I need to find the ones that have more than or less than 12 characters.
Thanks
Hello,
I am new to Access and was trying to figure out a simple Count. What I want to do is Count Column A if Column B has "".
I have looked around and am sure this is easy, can anyone point me in the right direction. I use this for another item but dont know what to add to make it work like I would want.
=Count(IIf([CLASSIFICATION]="Preliminary",0))
Hi,
I have one field, for this example, FieldA and another FieldB.
I would like the following:
If fieldA =1 then FieldB must = 1 or 3 or 4. If not then I would like a message box saying it needs to check. Otherwise I don't want anything to happen.
How can I do this? and where do I put any expressions?
thanks
Sue
Windows XP
MS Access 2007
VBA experience is very limited
So in my forms 'BeforeUpdate' property I want to validate whether a condition is met. The condition is whether 'any one of 4 fields contains something (text or number)'. I have written this code for validating a single condition and it alerts if the field is left blank. This code returns a warning message when the field is blank.
If Me.[txtObjective] & "" = "" Then
MsgBox "A 'Problem Objective' is required, please complete this field.", vbOKOnly
Me.[txtObjective].SetFocus
Cancel = True
[code]...
how to write the SetFocus part. And I dont know if this is correct anyway.
I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to show how the data is stored in Access table.
Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.
e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.
I'm an amateur (is there such a thing as having databases as a hobby?) with little training. I volunteered to organize a growing volunteer group I'm a part of by making a simple database for the volunteer office staff. One of the things I want it to do is make name tags for our weekly meetings. I don't want to print one for each name on our list because the list is over 400 names long with only around 35 regular attendees. I made two tables--one is the MakeNameTag table that lists regular attendees (it is a lookup field on the Names table).
The other is the Absences table. It has two fields; DateOfAbsence and MakeNameTag. If someone misses a meeting (does not pick up their name tag), their name is added to the Absences table. A query then filters the table for dates in the last 28 days. If their names shows up four times in the last 28 days I want for their name to be deleted from the MakeNameTag table. How to do that automatically? Our office volunteers have minimal PC skills, so the solution needs to be very user-friendly.
I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;
[Movement] - a unique ID for a collection of items to be picked.
[ToAssignRef] - the order ID
[Product] - the product!
[Picked] - the date/time the item was scanned
[Pick Actioned] - the date/time the [ToAssignRef] was completed
[Reason] - A code to indicate why an item could not be picked - AKA F3'd
An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].
I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?
Code:
SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked
FROM PickData
GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked
HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));
I am trying to create a report which basically includes the following:
Company, Wages, Contribution.
Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:
Company---Employee---Wages---Contribution
CompanyA---EmployeeA---$4000---$40
CompanyA---EmployeeB---$3800---$38
CompanyA---EmployeeC---$3800---$38
CompanyB---EmployeeA---$4200---$42
CompanyB---EmployeeB---$4200---$42
...and so on.
Each employee is required to contribute, in this example, 1% of gross wages to the general fund.
On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.
Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.
I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.
Is it possible to insert text from a textbox on a form (Data) when a combobox on (Data) meets a criteria?
Example: Test: IIf([Results]="Positive" text207)
So if the Results combobox is Positive then the text from Text207 is inserted.
like we do when we change our password, if characters are <4 or >10 then warning comes, I am making a password change button and want same thing.
I tried but could not locate any help for this. Can some body help?
Also, if put an If Text2.value = Null then
msgBox "Blank not allowed", vbcritical,"Error"
End If
While debugging I can see Null = Null but it does not give message box.
I tried everywhere in all text boxes of my db and found same thing.
I have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
View 1 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.
I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
Yes i'm having trouble with a Count problem, don't laugh at me.
I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.
Please help
I have one table with one column that I am trying to extract data from.What I would like to do with the Column is split it into a few counts. The Column has numbers in it with KB usage. What i'm trying to do is count the amount of users that use say 0. Then have a column next to it that count say 500 >= 2000 and so on. This is completely baffling me as to why I can't seem to do this. I've even redesigned the database to see if I could fix this problem, but no luck I can build a query for this to be done sepearetly, but can't seem to find a way to put it into one query.
SELECT Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID, [test Usage].[Nov-14], [test Usage].[Nov-14], Count([test Usage].StaffPhoneID) AS CountOfStaffPhoneID1
FROM [test Usage]
GROUP BY [test Usage].[Nov-14], [test Usage].[Nov-14]
HAVING ((([test Usage].[Nov-14])=0) AND (([test Usage].[Nov-14])>=500));
I could probably do this with 2 seperate querys, but I want to know if it can be done with just one.
I have a query which builds a username based on LastName, FirstName
That same query pulls out the Role of that person.
Currently the first column of the query has Role, and the second has the username expression.
One of my forms uses this in a drop down box that gives a Role, and the person attatched to that role.
On my login page i wanted the samething but without the Role.
Is it possible to just select the second column of the query?
I've tried setting the bound column to 2, but this doesnt work.
Hi
Can someone help me to change the following select?:
SELECT avdeling_nr,aar_nr, Sum(Arbtid) AS SumAvArbtid
FROM tblFerdig
GROUP BY avdeling_nr,aar_nr;
The table tblFerdig contains the columns mnd_nr and uke_nr. I want to be prompt to select aar_nr, mnd_nr or uke_nr.
something like this:
SELECT avdeling_nr,[input], Sum(Arbtid) AS SumAvArbtid
FROM tblFerdig
GROUP BY avdeling_nr,[input];
Can someone help me?
eroness
I am feeling really silly right now. This should be simple but .....
I have a problem i just simply can not get my head around. Hope some one can help.
There are two tables in this db.
There is a one to many relationship set up between them
I want a query that will show records in the parent table only when there are a specific number of related records in the child table.
Does anyone know if this can be done and if so how?
I'm stuck on this one part of my code where I am trying to count values in a column called 'ItemQty' in table 'dbo_Item'. I only want to count the values with the associated values in column 'OrderNumber'. I am getting the values of 'OrderNumber' from an array. Here is my code...(it doesn't work though. When I put a Msgbox to print out what the ItemQuantity value ends up to be, it only prints my code back to me.)
Code:
For Each Order In q
MsgBox "Order = '" & Order & "'"
ItemQuantity = ItemQuantity + ("count(ItemQty) Where OrderNumber LIKE '*" & Order & "'")
Next Order
I have a bunch of records in 1 table. Some records could have 1 duplicate field. What I am trying to do is create a query that produces the total or count in a new column. For example:
ID
FName
SName
Account#
If account appears more than once, I would like a new column to count the number of times in total that record appears. Then use this in a report afterwards.
Is this possible?
I have attached a file with Table, Current Result & Desired Result.
I have a table with Month, A, B to G with True or False check box inputs, what i need is on every month, in each category what is the total False;
In the past, i created a count query for each category like A to G and then finally link it. So i want to simplify it and i used below SQL but its giving me the result but not in a good format
SQL used
SELECT Tbl_DateMonth.[Month], Count(*) AS A, '' as DA_FIEPending, '' as B,'' as DA_PFMEAPending
FROM Tbl_DateMonth LEFT JOIN ([MasterTable_ EngineeringChanges] LEFT JOIN [MHEX Processors Update] ON
FROM Tbl_DateMonth LEFT JOIN ([Table1] LEFT JOIN [Table2] ON [Table1].UniqueID = [Table2].[Unique Id]) ON Tbl_DateMonth.[Effective Month] = [Table1].[Effective Month]
[Code] .....
Hi,
I am trying to create an ASP script that I can use to take the column names out of my access database, and then take out the data. I have a select statment like this:
Code: <%Set conn = Server.CreateObject("ADODB.Connection")conn.open "source","",""SQL_query = "SELECT * FROM table"Set RS = conn.Execute(SQL_query)%><%WHILE NOT RS.EOF%><%=RS("field")%><%RS.MoveNext WEND%>
I would just like to make it so the above script just takes out each column header from my database table, and instead of the <%=RS("field") it shows the current column name.
Thank you for your help,
~David
In forms, I frequenty use the following expression to get the results needed from a field's column:
=EmployeeID.column(1).
However, when I try to use the column function in a query it doesn't like it:
EmployeeName:[EmployeeID].Column(1)
What am I doing something wrong?
Along the same line, if I want to use =EmployeeID.column(1) for an unbound control in a form, why must you put the bound field on the form just to get the info? In this case, I want to do this to display the employee's name - not the primary key - without the scroll bar. Every time I do this, Access performance analyser tells me to use fewer controls, but you can't if you have to have the reference. The same thing seems to be required in queries, i.e. include the unaltered control to get an expression.
Help and advise, as always, will be appreciated!
Christine
Is there any way to use a select query where it will select the values off of my database but temporarily use a different column name for one of the columns???
I have 3 different item names stored in my main table. When I compare this to another table, I want to select one of the item names and just pass that as item_name. This way, I can have the same compare routine instead of writing a different compare routine for each item name. Any way? Thanks a lot for the help in advance!
I am using Access 2010 on a Windows 7 laptop. I need a query to provide a list of ID's that have more than one occurrence of IDandDate combined but haven't been successful getting past syntax errors.
Using this table structure as an example:
ID - defined as text field
Date - defined as date/time field
TestValue
This legacy table contains a record for each test. The table should be unique based on the ID and Date combination but was never restricted to that rule. I am converting to a new table but need to identify the duplicate entries so they can be addressed by business folks.
Access 2010 query.
I have the following SQL which returns rows of distinct numbers that are calculated from a field.
SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;
ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307
I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:
1307
1311
1401
etc...
I just want NumberOfCalls to show "3". Is there an easy way to do this?