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 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.
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.
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
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
my 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 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.
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 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?
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 ?
I'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?
Is 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)?
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.
I 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.
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.
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
The 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.
