Queries :: Count Boolean By Row?
Feb 11, 2015I 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 Replies
ADVERTISEMENT
Queries :: Select Query Return (text) Instead Of (Boolean Value)
Mar 26, 2015how 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 ?
Queries :: Make Boolean (Yes / No) Field With SELECT INTO Sql Statement
Nov 28, 2013I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
Queries :: Convert Boolean Fields To Integers / Blank For Null Values
May 22, 2015I 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.
Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count
Aug 16, 2015I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
Boolean Declaration
Nov 9, 2005Hi,
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)
?
Please Look At This Boolean Vs String
Dec 2, 2005I'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.
Boolean Searches? Possible?
Jun 21, 2005I 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,
Boolean Value In Query
Nov 2, 2004I 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
Boolean Type Search
Oct 31, 2007I 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
Combining Two Boolean Values?
Feb 11, 2012I 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.
How To Change To Boolean Data Type??
Apr 17, 2007my 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.
Excluding Boolean Values From A Query
Mar 26, 2006I 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 1 Replies View RelatedForms :: Boolean Checkbox Updates
Aug 4, 2013I 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 ?
Modules & VBA :: How To Check If Boolean Variable Has Been Set
Nov 25, 2014I 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.
Modules & VBA :: ApplyFilter With Boolean And String Data?
Nov 14, 2013I 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) & "")
Modules & VBA :: Public Boolean Variable Not Retaining Value
Oct 9, 2014I have a backup subroutine which automatically triggers when the front-end is closed down (it just takes the back-end, makes a copy and compacts it).It's driven off a hidden form which I use to track who is connected to the BE at any given time. This form is opened as part of the AutoExec when the FE is opened and writes some basic user info to a table. When the form is closed, it updates the table and fires the backup process before quitting Access.
Part of that user tracking process checks to see if the same user is already connected - either elsewhere (i.e. on a different machine) or on the same machine (i.e. opening a second instance of the same FE) - which is undesirable (and, frankly, unlikely, but not impossible) A brief prompt appears to explain that they can only be connected once, at which point the application is quit (to enforce the rule) This also works fine.
I would add a public boolean variable, set it to True by default, then switch it to False if the same user is already logged on before quitting Access.Trouble is, the variable doesn't seem to be holding its value? Here is the Load event for the tracking form :
Code:
Private Sub Form_Load()
On Error GoTo ErrorHandler
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Call InterfaceInitialise
blnPerformBackup = True
[code]...
For some reason, blnPerformBackup is False every time, even though I set it to True at the very start of the Load event (and it is a Public variable, defined in a separate module where I store all my Public constants and variables)
why it is not retaining its value from the Load event? I've checked and it does get set to True - and when I debug, it remains True - but at runtime it reverts back to False by the time it reaches the decision whether to backup or not?
Reports :: Sorting By Boolean Fields - Print All Different Combination
Feb 20, 2015I have 3 fields Yes/No.
A B C
To be in order it is necessary that: A , B and C = Yes.
I would want to print all different combinations :
A = no et B et C = Yes
Then
A: Missing
Nom, prenom et adresse.
.....
.....
A = Yes, B=No and C = No
Then
B and C missing :
Nom, prenom et adresse.
......
.......
Etc.....
General :: Database Status Flags (Text / Boolean Or Number)
Mar 11, 2015Is there a place within an Access database besides a table where you can store a flag (text or boolean or number) that persists after the database is closed and can be checked when the database is opened (using VBA)?
View 5 Replies View RelatedSetting Some Text As Visible Or Invisible In A Tabular Form Wrt Boolean Field
May 3, 2006Hi there,
I know there have been a good number of questions about visibility in forms already but I couldn't find a solution to my problem (or maybe I just didn't get it).
Basically, I have a tabular form (more than one record displayed at once) and one the field is of the Yes/No type. For each record, I'd like to have a text box that displays 'pending', set as visible if the field value is 'Yes' and set as invisible if the field value is 'No'.
The table is as follows:
id : auto-number
Flag: Yes/No [Yes]
If I use the following code on the Flag button:
Private Sub Flag_AfterUpdate()
Me.pending2.Visible = Me.Flag.Value
End Sub
all the 'pending' text boxes appear and disappear together (instead of just the relevant one).
I thought of using another text box, with the same data source ('Flag') but which would set itself to visible or invisible wrt to its own value but I couldn't find a way to do it.
Any suggestions ?
Thanks in advance !
and many thanks already for the forum and the contributions - it's been extremely helpful, esp. for a beginner.
Reports :: Change Text Formatting Of Control In Report Based On Boolean
Jul 10, 2015I would like to change the text formatting (color, italics, bold etc) of the contents of a control based on a boolean value in the underlying datasource of the report.
For instance, I have a report that generates a "Proforma Invoice" i would like to ability italicize the prices of certain items based based on a boolean value (EstimatedPrice) in the underlying datasource.
Queries :: Count Only Certain Value
Jun 17, 2014tbl_myTable:
Columns/Fields:
- pkID (primary auto),
- clientName (text),
- q1 (text) - can only be "ABC" or "DEF" (from form's radio button group),
- q2 (text) - can only be "ABC" or "DEF" (from form's radio button group),
- q3 (text) - can only be "ABC" or "DEF" (from form's radio button group)
Example:
PK= 1
clientName = John
q1 = ABC
q2 = ABC
q3 = DEF
Desired outcome of Query:
1 - John
2 - (count of ABC for John only)
- Irrespective of which column it is (ie. q1,q2,etc)...
- COUNT(sum?) only all the "ABC" for John.
Queries :: How To Count A Field And Sum
Jun 27, 2015I working on simple MS Access program which have 2 tables. As you can see in snap shot. What I am trying to achieve is. I would like to run a query which shows data between certain dates(It is done) and also to show me which Technician has done how many jobs(i have 5 technicians) and how much money a technician received by a payment method like Technician ID 1 did Cash Transactions 4 worth $300 similarly Credit Card transactions 2 worth 120$ and so on for other technicians.
The main theme is to get weekly summary report of technicians how many jobs they done and how many transactions each technician made by cash, by credit card along with their amount.
Queries :: Count On One Column
Jan 5, 2015I 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));
Queries :: Sum Overall Total Of Count
Aug 27, 2014I have a query that is counting perfectly like I want, but I need to sum the overall total of that count. How do I sum a count in SQL?
View 3 Replies View Related