I have designed an invoice for a project that shows the amount of money needed to be paid, some of this has already been paid and some hasnt, how am i able to make it so that the values that have been paid and therefore have been checked are not include in the query. please help.
View RepliesI basically have two yes/no fields in an access table. I want field one to have the value yes if field two has value no, and the other way around.
I had a look at the Boolean operators, but im not sure where to go from there.
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
Is there a way that in a query from several tables you can choose to exclude duplicates of a specific field?
In the query I'm trying to create I am going through and counting the number of customers in a certain time frame. I don't want to count a customer each time they buy something though, I only want to count them once. The dates of their purchases are held in a seperate table for purchases and the customer information is held in a customer table. The information I need is in both of these tables and each customer has an ID number which is how the two tables are joined.
When I tried using the DISTINCT code it still brought in all the purchases because the customer bought things on different dates. The only way I have ever used this, however, is to write SELECT DISTINCT at the beginning of the SQL instead of just SELECT. If there's another way to use it to do what I need I will try it as long as you tell me what it is.
How can I set up the query to bring in all the information I need but not duplicate the ID numbers?
Thanks in advance for any help I get!!!
Hi all, I know this is a real easy one, but I am not the smartest when it comes to access. Can you help me out.
I am running a crosstab query to count and sum records in my database. I have a fied called "Amount". In my form the user is not always required to enter an amount. When I run the query, I would like the results to exclude the records that have a null value or $0.00 in the "Amount" field.
How do I format the query to exclude those records?
Thanks in advance.
I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...
View 3 Replies View RelatedThe attached Access XP file demonstrates my problem. I've included a form to make testing easier.
Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.
A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.
I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.
In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.
I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.
I am creating table in ms access using query. How to specify data type for a boolean fields ie YES/No or TRUE/FALSE?
with regards
shruthi
how to do to return a text for each row (as field value) when a table field contains "1" as value ?
for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.
during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?
Could someone please help me? I am trying to run a query in which I would like to exclude a particular person's name from the query. For example, I am entering productivity information for each employee and manager each day, however, I only want the employee information to appear in the reports each day. Is there anyway of excluding a name from a query?
Thanks:)
I have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:
CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]
In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?
In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).
How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date
View 1 Replies View RelatedI have a table with RandomID, EmpID, CompanyID. Some Companies will have great than 25 employees. I need to setup a query where I can randomly select all employees from all companies whos employee count is less than 25.
I have tried the following:
SELECT top 10 percent Count(*), empID
FROM tlbCompany
Group By empID
HAVING Count(*)<25
ORDER BY rnd(RandomID);
This counts the total number of employees, so if a company has 10 employees, it will only display one of the them.
Can this query be done?
And, what am I doing wrong?
Thanks to all that help....
Enviva..
Trying to build a CRM system for the office but am getting stuck with the below...
Each company (tblCompany) in the database has at least 1 enquiry (tblEnquiries) linked to it and normally at least 1 (sometimes 0) people (tblPeople).
I have a form which loads company specific information e.g. notes/quotes/orders/people/enquiries. All data is loaded based on the company unique id (c_id).
The attached image shows Company 1 (c_id = 1). It has 6 enquiries. However the subform only displays 5 of these. It does not display any which do not have a person (or p_id) linked to it - this is consistent throughout the database. I have included the table relationships and the enquiry as well in case they are needed!
Searching the net seems to suggest 2 possibilities:
1 - the relationship join type needs to be set to include all from tblEnquiries and only those from tblPeople where joined fields are equal, however changing the join properties does not appear to have an effect.
2 - table field types do not match (all _ID fields are set to number, unique numbers only).
[edit]: am using Access 2010!
I have re-designed the query to pull through tblEnquiries and tblPeople data based on the c_id field on the open form, which is now showing all records, including those not assigned to a person.
However, in the form I can select one from the query datasheet and open to see additional info. Those without a blank p_id number do not open, I receive the error:
"Run-time error '2113': The value you entered isn't valid for this field.
All _id fields are set to numeric so am not sure how to correct this?
Table1 gives Case Nu.
Table2 gives Case Nu. and EventType.
Table3 gives EventType and Cleared (yes/no field)
I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.
Any ideas? I know there must be an obvious solution, but my mind is stuck right now!
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
Hi,
In ADP project I want declare ckeckbox data:
dim strDec as String
strDec = 1 ' if True
strDec = 0 ' if False
How can I show all records:
strDec = ? ' Show all recrords (true and false)
?
I'm working on a software that was developed by someone who left the company. The problem is that the software works for all locations execpt one that is in austria, after debuging I found out that there is a statement in the code that uses boolean true or false, the only way it worked is when I put an if statement with false or true as string, ex: if doc = "true" then
buttom line has anyone faced this problem, I searched microsoft website and they said that some vb engines don't convert boolean to string. Please Please if someone has a clue try to help. Thanks.
I have a search tool, but right now it is 100% case sensitive in the sense that if i search for "test" and my entry is "Test" it will not return any results.
is there any way to setup so I could say search for "Test" and it would find "test" properly? Or even to the extreme where I searched for "Tes*" and it came back with the "test" entry.
Thanks,
I need to create a user interface that will allow a boolean type search of a field. This would mean item1 AND item2 AND NOT item 3, item1 or item2, item 1 NOT item2, etc.
I've set up a combo box setup with the following script, but I can't figure out how to adjust it to allow the multiples.
Please advise if I should adjust this or try a differenmt interface format for my users.
__________________________________________________ ________
Option Compare Database 'Use database order for string comparisons
Option Explicit
Private Function AfterCombo(WhichLine As Integer)
Dim CBox As Control, TBox As Control, AndBox As Control, TBoxA As Control
Set CBox = Me("Combo" & WhichLine)
Set TBox = Me("Value" & WhichLine)
Set AndBox = Me("And" & WhichLine)
Set TBoxA = Me("Value" & WhichLine & "A")
TBox = Null
TBoxA = Null
Select Case CBox
Case "All", "Blank", "Not Blank"
TBox.Visible = False
AndBox.Visible = False
TBoxA.Visible = False
Case "Like", "Equal", "Less Than", "Greater Than", "Not Like", "Not Equal", "Not Less Than", "Not Greater Than", "In List", "Not In List"
TBox.Visible = True
AndBox.Visible = False
TBoxA.Visible = False
Case "Between", "Not Between"
TBox.Visible = True
AndBox.Visible = True
TBoxA.Visible = True
End Select
End Function
Private Sub Cancel_Click()
DoCmd.Close
End Sub
Private Function FormatList(ByVal List As String, FieldType As Integer)
Dim NewList As String, CommaPos As Integer, Word As String
NewList = ""
Do While Len(List) > 0
CommaPos = InStr(List, ",")
If CommaPos = 0 Then
Word = Trim(List)
List = ""
Else
Word = Trim(Left(List, CommaPos - 1))
List = Trim(Mid(List, CommaPos + 1))
End If
If Word > "" Then
Select Case FieldType
Case DB_TEXT, DB_MEMO
If InStr(Word, """") > 0 Then
MsgBox "Don't type double-quotes in the list"
End
End If
Word = """" & Word & """"
Case DB_DATE
If InStr(Word, "#") > 0 Then
MsgBox "Don't type '#' in your dates"
End
End If
If Not IsDate(Word) Then
MsgBox "Your list contains non-date characters"
End
End If
Word = "#" & Word & "#"
Case Else
If Not IsNumeric(Word) Then
MsgBox "Your list contains non-numeric characters"
End
End If
End Select
NewList = NewList & "," & Word
End If
Loop
NewList = Mid(NewList, 2)
If NewList = "" Then
MsgBox "Your list needs a valid value"
End
End If
FormatList = NewList
End Function
Private Function MakeNull(C As Control)
If Len(Trim(C)) < 1 Then C = Null
End Function
Private Function MakeSQL(WhichLine As Integer, FieldName As String, FieldType As Integer) As Variant
Dim CBox As Variant, TBox As Variant, TBoxA As Variant
Dim Condition As Variant, Delim1 As String, Delim2 As String
CBox = Me("Combo" & WhichLine)
TBox = Me("Value" & WhichLine)
TBoxA = Me("Value" & WhichLine & "A")
Select Case CBox
Case "Like", "Equal", "Less Than", "Greater Than", "In", "Not Like", "Not Equal", "Not Less Than", "Not Greater Than", "Not In"
If IsNull(TBox) Then
MsgBox "You have left a parameter blank for field [" & FieldName & "]"
End
End If
Case "Between", "Not Between"
If IsNull(TBox) Or IsNull(TBoxA) Then
MsgBox "You have left a parameter blank for field [" & FieldName & "]"
End
End If
End Select
Select Case FieldType
Case DB_TEXT, DB_MEMO
Delim1 = """"
Delim2 = """"
If Not IsNull(TBox) Then TBox = QFix(TBox)
If Not IsNull(TBoxA) Then TBoxA = QFix(TBoxA)
Case DB_DATE
Delim1 = "#"
Delim2 = "#"
Case Else
Delim1 = ""
Delim2 = ""
End Select
Select Case CBox
Case "All"
Condition = Null
Case "Blank"
Condition = " Is Null"
Case "Not Blank"
Condition = " Is Not Null"
Case "Like"
Condition = " Like """ & TBox & """"
Case "Equal"
Condition = "=" & Delim1 & TBox & Delim2
Case "Less Than"
Condition = "<" & Delim1 & TBox & Delim2
Case "Greater Than"
Condition = ">" & Delim1 & TBox & Delim2
Case "Not Like"
Condition = " Not Like """ & TBox & """"
Case "Not Equal"
Condition = "<>" & Delim1 & TBox & Delim2
Case "Not Less Than"
Condition = ">=" & Delim1 & TBox & Delim2
Case "Not Greater Than"
Condition = "<=" & Delim1 & TBox & Delim2
Case "In List"
Condition = " In(" & FormatList(TBox, FieldType) & ")"
Case "Not In List"
Condition = " Not In(" & FormatList(TBox, FieldType) & ")"
Case "Between"
Condition = " Between " & Delim1 & TBox & Delim2 & " And " & Delim1 & TBoxA & Delim2
Case "Not Between"
Condition = " Not Between " & Delim1 & TBox & Delim2 & " And " & Delim1 & TBoxA & Delim2
End Select
MakeSQL = " And [" + FieldName + "]" + Condition
End Function
Private Sub OK_Click()
Dim Where As String
Const ObType = "Form"
Where = Where & MakeSQL(1, "Lyrics", 10)
Where = Where & MakeSQL(2, "TrackTitle", 10)
On Error GoTo OKCApplyError
If Where <> "" Then
Where = Mid(Where, 6)
DoCmd.OpenForm "MasterFormQuery", , , Where
Else
DoCmd.OpenForm "MasterFormQuery"
End If
OKCExit:
Exit Sub
OKCApplyError:
MsgBox "Error " & Err & " opening " & ObType & Chr$(13) & Chr$(10) & Error
Resume OKCExit
End Sub
Private Function QFix(ByVal X)
Dim P As Integer
If IsNull(X) Then
QFix = Null
Exit Function
End If
P = InStr(X, """")
Do While P > 0
X = Left$(X, P) & """" & Mid$(X, P + 1)
P = InStr(P + 2, X, """")
Loop
QFix = X
End Function
Private Sub exitselectform_Click()
On Error GoTo Err_exitselectform_Click
DoCmd.Close
Exit_exitselectform_Click:
Exit Sub
Err_exitselectform_Click:
MsgBox Err.Description
Resume Exit_exitselectform_Click
End Sub
I have a table with seven Boolean fields: I need to count all the "trues" and return the row if the sum of the trues is greater than 3.how to do this.
View 2 Replies View Relatedmy teacher asked me to do Access homework to change data type from Text to Boolean. Data in "Owned car" field show as Y or N
17178
so I click on Design View and change datatype of this field from "Text" to "Yes/No" , than when I saved it show a message
17179
When I back to Datasheet view, all data in "Owned car" field change to be "No".
How can I change data type to be Boolean type without deleting my original data??? I want them to show the same as Y or N.
I use a form for changing data. So when the user selects a record from a listbox I fill all fields of the form with the content relating to the selected line in the box. Nearly everything runs as wished.
All updates take place immediately but the Boolean field does not update or better it updates only after I move the cursor over it. Then it is correct. To make it clear, I do not need to press a button or click, I only move the cursor over the boolean checkbox.
I access VBA after clicking the line in the listbox and use the dlookup command to get the right values. Do I need something like a requery for a boolean field ?
I have an Access 2010 db which has a load of global variables of type Boolean. The values are different for each machine the database is stored on, so I have a linked table to a separate Access mdb. On startup the database opens the linked table and assigns values to each variable.
However I need to differentiate between a valuable value of False and the variable having not been set. As boolean variables default to False, this is a problem. One option is to use data type Variant, but I know this is not efficient. I am considering using data type Byte instead of Boolean, with 1 as False and 2 as True.
I have a form used to gather data around some supplier details, and we have a review check box with Boolean data. I'm trying to establish a filter button to filter un-checked boxes by specific suppliers from a drop-down list.
Although I've been able to run filters on suppliers and the review checks separately, together I get a "Type Mismatch" error. I thought it might be because of the Boolean data type, so I tried converting that to String but get the same issue.
So far what I've created is this:
Code:
Dim Chk1 As String
Chk1 = CStr([RevChk])
DoCmd.ApplyFilter , (" Chk1 = " & Chr(34) & 0 & Chr(34) & "") And (" [Supplier Name] = " & Chr(34) & txtSupplSearch & Chr(34) & "")